COMBINAR CORRESPONDENCIA Y GUARDAR DOCUMENTOS INDEPENDIENTES

Hola a todos!

El post de hoy va a ser distinto a los que acostumbro a subir a la web, sobre todo porque está programado en WORD.

Hace ya tiempo que escribí un post relacionado con Combinar Correspondencia, en concreto para intentar dar solución a un problema muy común cuando se realiza esta funcionalidad con WORD, es decir, el obtener de manera individual todos los documentos generados.

Si no estáis familiarizados con Combinar Correspondencia, en este enlace podéis leer en profundidad de qué se trata. Si bien, en resumen, combinamos los campos de un archivo de Excel con una plantilla en WORD y generamos tantos documentos como datos tengamos en Excel. Podemos enviar por correo electrónico cada documento a su destinatario o generar los documentos todos a la vez, de uno en uno o por tramos.

Pero entonces, ¿cuál es el problema?, pues que si tenemos que generar 500 cartas (individuales), sería bastante tedioso el tener que generar y guardar cada documento de uno en uno manualmente.

Ante esta necesidad, en un primer avance lo he realizado con Excel directamente, y el proceso funciona correctamente, pero tiene sus limitaciones (fundamentalmente los formatos). Para solucionarlo me he decidido a hacerlo en Word. Me documenté con algunos procesos que se proponen en Internet, pero no me acababan de convencer, eran demasiado manuales y poco intuitivos. Así que he creado un proceso bastante más automatizado para facilitar la experiencia del usuario.

El primer paso que debemos realizar es obtener el archivo de WORD con los documentos ya generados, todos en el mismo archivo. Importante, el proceso está programado para documentos que ocupen un folio (una hoja de Word).

Pues bien, vamos a hacerlo paso a paso:

  • Generamos los documentos con Combinar correspondencia. Vamos a partir del siguiente listado en Excel (100 líneas)

COMBINAR CORRESPONDENCIA Y GUARDAR DOCUMENTOS INDEPENDIENTES

  • Combinamos correspondencia con el siguiente texto:

COMBINAR CORRESPONDENCIA Y GUARDAR DOCUMENTOS INDEPENDIENTES1

Una vez que hemos combinado y generado todos los documentos obtendremos un archivo de 100 hojas (una por empleado de la base de Excel).

A continuación, ese documento lo vamos a guardar, pero lo haremos o bien como .Doc o como .Docm (para macros), pero no .Docx. El motivo es que vamos a incluir una macro en el documento y con extensión .Docx no permite guardar con macros. Para este ejemplo lo denominaré como “DOCUMENTOS GENERADOS”

Hasta este punto ya tenemos el archivo de Word que queremos trocear en 100 archivos. Ahora debemos crear una carpeta o saber en cuál vamos a importar todos estos archivos, en mi ejemplo la crearé y la denominaré: “DESTINO”.

COMBINAR CORRESPONDENCIA Y GUARDAR DOCUMENTOS INDEPENDIENTES3

Y ya tenemos todo lo necesario para abrir DOCUMENTOS GENERADOS, crear un módulo estándar y pegar nuestra macro:

Option Explicit
Sub COMBINAR()
'DECLARAMOS VARIABLES
Dim mArchivo As Variant, fdocument As Document
Dim miExcel As String, target As Variant, obSQL As String
Dim cnn As Object, dataread As Object, filas As Long
Dim i As Long, iname As Variant, midoc As Document
Dim mi_archivo As String, campo As String, miCarpeta As Variant
Dim texto As Variant, target_a As Variant
'Capturamos documento actual
Set fdocument = ThisDocument
'Abrimos ventana de diálogo para seleccionar el archivo Excel
'que hemos combinado
Set mArchivo = Application.FileDialog(msoFileDialogOpen)
If mArchivo.Show = 0 Then Exit Sub
miExcel = mArchivo.SelectedItems(1)
'Abrimos ventana de diálogo para seleccionar la carpeta en la que
'guardaremos todos los archivos independientes
Set miCarpeta = Application.FileDialog(msoFileDialogFolderPicker)
If miCarpeta.Show = 0 Then Exit Sub
miCarpeta = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
'Generamos documento nuevo
Set target = Documents.Add
'Con instrucción SQL seleccionamos el nombre de la hoja
'y el campo con el que queremos identificar cada documento de word
obSQL = "SELECT [BBDD$].[NOMBRE COMPLETO] FROM [BBDD$]"
'Iniciamos conexión ADO para pasar una tabla con los items
'del campo seleccionado
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Connectionstring = "DATA SOURCE=" & miExcel
.Properties("Extended Properties") = "Excel 8.0"
.Open
End With
Set dataread = New ADODB.Recordset
With dataread
.Source = obSQL
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
End With
'Grabamos los datos en el documento Word generado (target)
dataread.MoveFirst
filas = dataread.RecordCount
Selection.InsertAfter dataread.Fields(0)
Do Until dataread.EOF
dataread.MoveNext
If dataread.EOF = "Verdadero" Then Exit Do
campo = Replace(dataread.Fields(0), "-", " ")
Selection.InsertAfter Chr(11) & campo
Loop
'Formateamos los datos grabados como una tabla
Selection.ConvertToTable Separator:=wdSeparateByDefaultListSeparator, _
numcolumns:=1, Numrows:=filas, AutoFitBehavior:=wdAutoFitFixed
Set midoc = ActiveDocument
'Recorremos la tabla
For i = 1 To midoc.Tables(1).Rows.Count
Set iname = midoc.Tables(1).Cell(i, 1).Range
'Eliminamos de cada dato el retorno de carro, Chr(13).
'Eliminamos el último caracter de la cadena de texto
mi_archivo = Mid(Replace(miCarpeta & iname.Text, Chr(13), ""), 1, Len(Replace(miCarpeta & iname.Text, Chr(13), "")) - 1)
'Recorremos todas las hojas del documento word actual
'Creamos un nuevo documento y pasamos el contenido de cada combinación.
Set texto = fdocument.Sections(i).Range
texto.End = texto.End - 1
Set target_a = Documents.Add
target_a.Range.FormattedText = texto
'Nombramos cada documento con el nombre
'de la hoja que contiene los datos extraidos de Excel
target_a.SaveAs FileName:=mi_archivo
'Cerramos cada documento
target_a.Close
Next i
'cerramos el documento con los datos de Excel
ActiveDocument.Close SaveChanges:=False
End Sub

Después de pegar la macro, debemos activar las referencias para trabajar con ADO: Microsoft ActiveX Data Object 2.8 Library (en caso de no tener esta librería podéis probar con otra de la lista).

Este paso debéis hacerlo, de lo contrario se mostrará un error y no podréis continuar.

COMBINAR CORRESPONDENCIA Y GUARDAR DOCUMENTOS INDEPENDIENTES4

Otro paso que debemos realizar manualmente es determinar el campo del archivo Excel a incluir en el documento de Word.

Debemos especificarlo en la siguiente línea de código:

obSQL = "SELECT [BBDD$].[NOMBRE COMPLETO] FROM [BBDD$]"

Donde la hoja de nuestro archivo Excel se denomina BBDD, debemos especificarlo en la sentencia SQL y siempre con el dólar al final. El campo que vamos a incluir será NOMBRE COMPLETO y también lo especificamos en el SQL. Este paso es importante, si el nombre de la hoja o del campo no es correcto, se mostrará un error y no podremos continuar. Si se muestra el error, debéis reiniciar la macro para volver a ejecutarla y eliminar el documento en blanco que se creó antes de llegar al error.

Pero sigamos, suponiendo que ya tenemos la macro totalmente preparada, cuando la ejecutemos se mostrará un cuadro de diálogo para seleccionar el archivo Excel:

COMBINAR CORRESPONDENCIA Y GUARDAR DOCUMENTOS INDEPENDIENTES6

A continuación se volverá a abrir otro cuadro de diálogo para seleccionar la carpeta en la que dejaremos todos los 100 documentos creados:

COMBINAR CORRESPONDENCIA Y GUARDAR DOCUMENTOS INDEPENDIENTES7

Después dejaremos que se vayan generando los documentos hasta que finalice la macro, el resultado será este:

COMBINAR CORRESPONDENCIA Y GUARDAR DOCUMENTOS INDEPENDIENTES5

Como podéis observar hemos generado 100 documentos y los hemos identificado con su nombre (podría ser con otro campo cualquiera que sea un identificados único).

Y eso es todo!. Espero que el post sea lo bastante claro y no os queden dudas, pero en cualquier caso, os dejaré los dos archivos para que realicéis pruebas.

Descarga el archivo de ejemplo pulsando en: DOCUMENTOS GENERADOS

Descarga el archivo de ejemplo pulsando en: BASE EXCEL PARA COMBINAR

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

Anuncios

COMBINAR CORRESPONDENCIA EN EXCEL Y GUARDAR EN PDF

Hola a todos!, espero que todo os vaya bien 🙂

Llevo varias semanas que no publico nuevo material, el motivo no es otro que la falta de tiempo. Lo cierto es que entre la temporada veraniega, la reincorporación al trabajo y temas pendientes, se hace complicado sacar un momento para escribir nuevas entradas.

Estos días sin embargo, tuve tiempo para preparar un post bastante interesante. Como suele ser habitual, el tema surgió a raíz de una consulta de una lector. Me preguntaba acerca de la posibilidad de realizar combinar correspondencia en Excel y poder pasar cada uno de los documentos a PDF de forma individual.

Normalmente, el proceso de combinar correspondencia se inicia desde Microsoft Word y se accede a Excel para buscar la base de datos en la que tenemos la información necesaria para enviar la correspondencia. Prácticamente se pueden realizar todo tipo de envíos masivos y enviar la información a través de correo electrónico. También se pueden imprimir los documentos (es posible que con paciencia se puedan imprimir con algún programa a PDF de uno en uno, especificándolo en el cuadro de diálogo de la impresora).
Para más información sobre combinar correspondencia os dejo el siguiente enlace: COMBINAR CORRESPONDENCIA

Pues bien, en casi la totalidad de los casos podremos solucionar nuestras necesidades con Word. Pero si queremos pasar cada uno de los archivos que queremos enviar a PDF y guardarlos en un directorio, Excel es una buena solución. Para hacerlo, debemos construir nuestra propia aplicación de combinar correspondencia.

Después de este pequeño comentario, ya estamos listos para comenzar. Como siempre, vamos a ver la base de datos que hemos confeccionado para enviarles una comunicación, pestaña “DATOS”:

combinar-correspondencia-en-excel-y-guardar-en-pdf

El siguiente paso ahora es crear la plantilla que vamos a utilizar para incorporar los datos a enviar. La plantilla la vamos a crear directamente en una hoja Excel, lo haremos teniendo en cuenta los formatos que podemos darle al texto en cada línea de la hoja, y además crearemos una serie de marcadores que luego vamos a utilizar para trasladar los datos de cada persona. Esta es la plantilla:

combinar-correspondencia-en-excel-y-guardar-en-pdf1

Como podéis observar, aquellos campos que hacen referencia a los datos que iremos incorporando en cada carta los marcaremos entre corchetes “<>”, luego en la macro haremos referencia a ellos para reemplazarlos.

Es importante que vayáis configurando en cada línea lo datos de la forma en la que saldrán finalmente, aunque esto lo podéis hacer realizando varias pruebas para depurar el diseño.

La plantilla “GENERAR” no contiene datos, será la hoja en la que se vuelque una copia de “PLANTILLA” y en la que iremos colocando cada registro de la hoja “DATOS”. Lo que sí es importante es que en la hoja “GENERAR”, las líneas y las columnas tengan en mismo ancho que la hoja “PLANTILLA”, o por lo menos tener en cuenta que el PDF final tendrá el mismo formato que la hoja “GENERAR”:

combinar-correspondencia-en-excel-y-guardar-en-pdf2

Ahora que ya tenemos la carta incorporada en nuestro archivo y las hojas creadas, ya podemos ir a la programación, debemos incluir esta macro:

Sub COMBINAR_CORRESPONDENCIA()
Dim i As Double
Dim ruta As String
Application.ScreenUpdating = False
'Activamos nuestro libro
ThisWorkbook.Activate
Sheets(2).Name = "GENERAR"
'seleccionamos hoja "GENERAR"
Sheets("GENERAR").Select
'Contamos el número de casos
Fin = Application.CountA(Sheets("DATOS").Range("A:A"))
'Elegimos la carpeta donde queremos guardar los archivos
On Error Resume Next
With CreateObject("shell.application")
ruta = .browseforfolder(0, Titulo, 0).Items.Item.Path
End With: On Error GoTo 0
'Si no elegimos la carpeta de destino, la macro se para
If ruta = Empty Then
MsgBox "DEBES SELECCIONAR UNA CARPETA DE DESTINO, PULSA DE NUEVO EL BOTÓN GENERAR", vbExclamation
Exit Sub
End If
'Iniciamos un for
For i = 2 To Fin
'Creamos variables para cada uno de los datos a incorporar en la hoja "GENERAR"
Nombre = Sheets("DATOS").Cells(i, 1)
Apellidos = Sheets("DATOS").Cells(i, 2)
Lugar = Sheets("DATOS").Cells(i, 3)
Fecha = Format(Sheets("DATOS").Cells(i, 4), "[$-C0A]d ""de"" mmmm ""de"" yyyy;@")
ExcelSignum = Sheets("DATOS").Cells(i, 5)
Email = Sheets("DATOS").Cells(i, 6)
Firma = Sheets("DATOS").Cells(i, 7)
'Llamamos a la macro Actualiza
Call ACTUALIZA
'Damos nombre a la hoja activa, que es GENERAR
ActiveSheet.Name = Sheets("DATOS").Cells(i, 1) & " " & Sheets("DATOS").Cells(i, 2)
With ActiveSheet
'Reemplazamos los datos en los marcadores que hemos creado en Plantilla
Cells.Replace What:="<NOMBRE>", Replacement:=Nombre, LookAt:=xlPart, SearchOrder:=xlByRows
Cells.Replace What:="<APELLIDO>", Replacement:=Apellidos, LookAt:=xlPart, SearchOrder:=xlByRows
Cells.Replace What:="<LUGAR>", Replacement:=Lugar, LookAt:=xlPart, SearchOrder:=xlByRows
Cells.Replace What:="<FECHA>", Replacement:=Fecha, LookAt:=xlPart, SearchOrder:=xlByRows
Cells.Replace What:="<EXCEL SIGNUM>", Replacement:=ExcelSignum, LookAt:=xlPart, SearchOrder:=xlByRows
Cells.Replace What:="<EMAIL>", Replacement:=Email, LookAt:=xlPart, SearchOrder:=xlByRows
Cells.Replace What:="<FIRMA>", Replacement:=Firma, LookAt:=xlPart, SearchOrder:=xlByRows
'Si queréis dar formato de hipervínculo a las celdas A6 y A10
'Solo tenéis que descomentar la parte indicada entre puntos:
'-----------------------------------------------------------
.Range("A6,A10").Select
With Selection
.Font.Color = RGB(0, 0, 255)
.Font.Underline = xlUnderlineStyleSingle
End With
'-----------------------------------------------------------
End With
'Publicamos en PDF, sin propiedades en el documento y sin abrir cada vez que se genere el PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ruta & "\" & ActiveSheet.Name, Quality:=xlQualityStandard, _
IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
'Volvemos a renombrar la hoja2 como "GENERAR"
Sheets(2).Name = "GENERAR"
Next
End Sub

Con esta macro debemos adjuntar esta otra, a la que hacemos referencia en la macro principal:

Sub ACTUALIZA()
Dim Shape As Excel.Shape
'Limpiamos contenidos en hoja "GENERAR"
Sheets("GENERAR").Select
Columns("A:A").ClearContents
'Eliminamos imagenes en la hoja Generar
For Each Shape In Sheets("GENERAR").Shapes
Shape.Delete
Next
'Copiamos la plantilla base desde la hoja "PLANTILLA" a "GENERAR"
'Seleccionamos el rango de FILAS hasta donde tenemos texto o un rango superior
Sheets("PLANTILLA").Select
Rows("1:50").Select
Selection.Copy
Sheets("GENERAR").Select
Rows("1:50").Select
ActiveSheet.Paste
End Sub

Cuando la macro se ejecute, directamente nos va a preguntar por una ubicación (directorio) en la que queremos guardar cada uno de los PDF generados. Si no elegimos, saldremos la macro y se parará el proceso. Si la elegimos, la macro pasará los datos a la hoja “GENERAR” mediante el comando reemplazar y sustituirá cada uno de registros marcados a través de un bucle for-next.

La otra macro, lo que hace es limpiar de todo contenido la hoja “GENERAR” incluso objetos, como firmas escaneadas, imágenes, logos, etc y traslada el texto de la hoja “PLANTILLA”.

Finalmente, guardamos la hoja “GENERAR” denominando a cada PDF con el nombre de las personas, el resultado es este:

combinar-correspondencia-en-excel-y-guardar-en-pdf3

y la comunicación esta:

combinar-correspondencia-en-excel-y-guardar-en-pdf4

De esta forma tendremos todos los archivos en PDF y guardados en el mismo directorio.

En la macro, os he dejado parte del código comentado, ese código sirve para dar formato a los hipervínculos que se mostrarán en el PDF si queremos que se destaquen en azul y subrayados. Es solo una medida puramente estética, el hipervínculo funciona perfectamente.

Y esto es todo, espero que os sea de utilidad.  🙂

Importante: la macro ha sido probada en Excel 2010, 2013 y 2016. En 2007 debería funcionar correctamente, dado que permite pasar documentos a PDF. En versiones anteriores no funcionará.

Descarga el archivo de ejemplo pulsando en: COMBINAR CORRESPONDENCIA EN EXCEL Y GUARDAR PDF