Anuncios

jueves, 24 de diciembre de 2020

¿Como realizar combinación de correspondencia entre dos archivos de Excel?

Aprende a crear una combinación de correspondencia entre dos archivos de excel el cual es una herramienta fundamental cuando deseamos crear un conjunto de documentos, como una carta modelo que se debe enviar a muchos clientes. Cada carta o etiqueta contiene el mismo tipo de información, aunque su contenido es único. Por ejemplo, en las cartas que envíe a sus clientes, cada carta se puede personalizar con el nombre de la persona a la que vaya dirigida. La información única de cada carta o etiqueta procede de las entradas contenidas en un origen de datos.

combinación de correspondencia entre dos archivos de Excel

¿COMO CREAR UNA COMBINACIÓN DE CORRESPONDENCIA ENTRE DOS ARCHIVOS DE EXCEL?


Con esta macro nos va permitir realizar lo siguiente:


  • Realizar combinación de correspondencia entre dos archivos de excel
  • Tener presente que se debe tener una hoja con la carta o estructura modelo y el segundo archivo con la base de datos para realizar la combinación de correspondencia.


Revisa el siguiente video para que aprendas a utilizar la macro para hacer la combinación de correspondencia en excel con dos archivos y en la parte inferior del articulo se encuentra el enlace para que la puedas descargar la macro



Importante que para utilizar la macro debemos de tener habilitados el menú de programador o desarrollador. En este CORTO VIDEO te explico como puedes HABILITARLA


Para poder realizar la combinación de correspondencia debemos de tener con el nombre de correspondencia nuestra carta modelo y el otro archivo donde vamos a tener la base de datos se va llamar BD, como se muestra en la imagen.


combinación de correspondencia entre dos archivos de Excel


En la siguiente imagen se encuentra la información que tenemos en el otro archivo y el nombre de la hoja BD para realizar la combinación de correspondencia.

combinación de correspondencia entre dos archivos de Excel


Es muy importante que en la hoja correspondencia vamos a colocar de manera manual o escribir las variables donde queremos que aparezca la información entre signo < > ejemplo como semuestra en la imagen tenemos Señor(a): y aquí queremos que nos aparezca el nombre entonces colocamos <NOMBRE> se debe tener presente que se debe colocar tal cual como lo tenemos el titulo en la hoja BD

combinación de correspondencia entre dos archivos de Excel

Quedaría nuestra carta de la siguiente manera con todas las variables que voy a utilizar en la hoja BD

combinación de correspondencia entre dos archivos de Excel


Después de que tengamos habilitado las macros debemos de dirigirnos a la pestaña de desarrollador o programador  y posteriormente grabar macro.


Grabar macro en excel


En la ventana de grabar macro podemos colocar el nombre que queramos a la macro y algo que es muy importante que vamos a digitar un comando para ejecutar la macro y para eso podemos colocar la r para que en el momento de presionar ctrl + r se ejecute la macro y presionamos aceptar.


ventana de macros


Posteriormente nos debemos dirigir a la pestaña programador y luego en el grupo de código presionamos en detener grabación.

detener grabacion con macros


luego nos vamos nuevamente para la pestaña programador y nos dirigimos en el grupo código y presionamos clic en macros

pestaña de desarrollador en  macros


En el momento que realizamos el paso anterior debemos presionar clic en el botón de modificar, para aquí acceder al código de la macro.

modificar macros en excel



En la siguiente ventana debemos de pegar la macro que la puedes descargar en la parte inferior del articulo.

Ventana de visual basic


En el momento de presionar con el teclado los comandos ctrl + r automaticamente nos va ejecutar la macro y nos abre la siguiente ventana, escribe el número de criterios para el ejemplo va ser el número 6 y presionamos aceptar.

combinación de correspondencia entre dos archivos de Excel


Luego se ejecuta la macro donde me crea las combinaciones de correspondencias como se muestra en la imagen.

combinación de correspondencia entre dos archivos de Excel


Adicionalmente me crea una nueva hoja por cada fila que vamos a utilizar en la correspondencia en total de acuerdo a la imagen anteriores la base de datos era con 10 nombres o 10 filas y para eso excel crea las combinaciones con los diferentes datos y crea las 10 horas con el nombre de correspondencia seguido del número consecutivo.


combinación de correspondencia entre dos archivos de Excel


combinación de correspondencia entre dos archivos de Excel



La macro consisten en lo siguiente


Abrimos el archivo de la base de datos BD

Workbooks.Open Filename:="C:\Users\Juan David\Desktop\Ejemplo\BD.xlsx"


Sheets("BD").Select
    Sheets("BD").Copy After:=Workbooks("combinacion correspondencia.xlsm").Sheets(1)


Abrimos la ventana de combinacino de correspondencia

Windows("combinacion correspondencia.xlsm").Activate


Le asignamos a la variable NumCriterios para asignar el numero de criterios de combinaciones de correspondencia

NumCriterios = InputBox("Escribe el número de criterios")  'Caja de texto para escribir el numero de criterios
 

''''''''''''''''''PARA 1 CRITERIOS '''''''''''''''''''''''''''''''''''''''''''''''''''''''
If NumCriterios = 1 Then

Selecionamos y copiamos al primera hoja de correspondencia

Sheets("Correspondencia").Select   
Sheets("Correspondencia").Copy Before:=Sheets(2)

Para contar el numero de datos

CantDatos = Sheets("BD").Cells(Rows.Count, "A").End(xlUp).Row


Variable1 = Sheets("BD").Range("A1")

Reemplazamos las variables por los datos de la base de datos

Dato1 = Sheets("BD").Range("A2")
   Cells.Replace What:="<" & Variable1 & ">", Replacement:=Dato1, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False  
        
ContFilaAnt = 2
NumPagina = 3

Realizamos Ciclo para realizar las combinaciones

For datos = 2 To CantDatos  
NombreHoja = ActiveSheet.Name


Copia hoja y enumera el consecutivo
Sheets(NombreHoja).Copy Before:=Sheets(NumPagina)   
Variable1 = Sheets("BD").Range("A" & ContFilaAnt)

Busca y reemplaza los valores de la base de datos

Dato1 = Sheets("BD").Range("A" & NumPagina)
   Cells.Replace What:=Variable1, Replacement:=Dato1, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False 


incremento de contadores

ContFilaAnt = ContFilaAnt + 1 
NumPagina = NumPagina + 1
Next
Else
 
''''''''''''''''''PARA 2 CRITERIOS '''''''''''''''''''''''''''''''''''''''''''''''''''''''
If NumCriterios = 2 Then
Sheets("Correspondencia").Select
Sheets("Correspondencia").Copy Before:=Sheets(2)

Contar el numero de datos

CantDatos = Sheets("BD").Cells(Rows.Count, "A").End(xlUp).Row  
Variable1 = Sheets("BD").Range("A1")
Variable2 = Sheets("BD").Range("B1")
Dato1 = Sheets("BD").Range("A2")
Dato2 = Sheets("BD").Range("B2")
   Cells.Replace What:="<" & Variable1 & ">", Replacement:=Dato1, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="<" & Variable2 & ">", Replacement:=Dato2, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

ContFilaAnt = 2
NumPagina = 3
For datos = 2 To CantDatos
NombreHoja = ActiveSheet.Name
Sheets(NombreHoja).Copy Before:=Sheets(NumPagina)
Variable1 = Sheets("BD").Range("A" & ContFilaAnt)
Variable2 = Sheets("BD").Range("B" & ContFilaAnt)
Dato1 = Sheets("BD").Range("A" & NumPagina)
Dato2 = Sheets("BD").Range("B" & NumPagina)
   Cells.Replace What:=Variable1, Replacement:=Dato1, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=Variable2, Replacement:=Dato2, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
ContFilaAnt = ContFilaAnt + 1
NumPagina = NumPagina + 1
Next
Else
 

''''''''''''''''''PARA 3 CRITERIOS '''''''''''''''''''''''''''''''''''''''''''''''''''''''


La macro se repite los ciclos para cada criterio con la diferencia que se agrega una variable mas para que pueda realizar los cambios, la macro esta realizada hasta 8 criterios o variables



En el siguiente ENLACE puedes DESCARGAR el archivo para realizar las pruebas que se menciona en el ejemplo.


En el siguiente ENLACE puedes DESCARGAR la macro para combinación de correspondencia entre dos hojas de excel.



Seguramente quieres sabes más acerca de Excel, no te olvides darte una vuelta por las siguientes secciones disponibles para ti de manera gratuita: 


Canal de YouTube
Comunidad Telegram
Instagram: Aprende y Enseña Excel
Facebook: Aprende Excel Fácil
Twitter: Aprende y Enseña Excel


Hasta una próxima y te deseo el mayor de los EXITOS.


Aprende y Enseña Excel.

No hay comentarios:

Publicar un comentario