Combinación de correspondencia (Mail Merge) en PowerPoint usando VBA y Excel.

finalstage

Como puedes ver en la imagen superior hemos hecho una combinación de correspondencia con datos de empleados (id, nombre, apellido y una foto) almacenados en Excel en una presentación en PowerPoint (permite crear tantas diapositivas como sean necesarias)

Como explico en otro artículo PowerPoint no tiene “Combinación de Correspondencia” o Mail Merge. Esta es una solución que utiliza Visual Basic for Applications (VBA) y Excel como fuente de datos para crear automáticamente la lista en diapositivas de PowerPoint.

Si quieres usar una base de datos SQL Server en vez de Excel como fuente de datos mira este otro artículo.

En este artículo veremos como PowerPoint puede obtener los datos almacenados en Excel.

Primero necesitamos una hoja de Excel con los datos, para este ejemplo usaremos 4 columnas de esta manera:

Columna A: Id. Es un número: 1, 2, 3...
Columna B: Nombre: Como "Luis", "José", etc.
Columna C: Apellido: Como "Smith", "Luna", etc.
Columna D: Imagen. Hay dos opciones escribir la ruta al archivo de imagen o poner una imagen incrustada en Excel.

Combinar texto como Id, nombre, apellido, es la parte fácil. Combinar la foto es un poco más difícil pero puede hacerse:

A) La forma más fácil es usar un enlace al archivo de imagen. Pueden verlo en la imagen siguiente, ponemos la ruta de la ubicación del archivo en la columna “Picture”.

excelwithlinks

B) Insertar la imagen directamente en Excel. Un poco más complicado.

excelwithimages

Estas fotos tienen la propiedad “Mover y dimensionar imágenes con las celdas” o “Move and size with cells” en inglés. Miren la siguiente imagen, la parte resaltada en amarillo:

moveandsize

Esta configuración hace que las imágenes se queden en la misma celda cuando mueves la celda (por ejemplo cuando la re-ordenas) y para redimensionar la imagen cuando el ancho o altura de la celda cambie.

Comencemos.

Para el caso A) necesitas hacer los siguientes pasos:

1. Abre PowerPoint y agrega una referencia a Excel. En el menú Herramientas\Referencias marca la librería llamada: “Microsoft Excel 15.0 Object Library”.

reference

2. En código VBA tienes que abrir el archivo en Excel. En este caso el archivo en Excel se llama Employees.xlsx y está ubicado en “C:\”

Dim XL As Excel.Workbook
Set XL = Excel.Application.Workbooks.Open("c:\Employees.xlsx")

3. Necesitamos hacer un bucle para leer todas las filas de datos. Hay dos opciones:

Si conocemos cuantas filas de datos tiene el Excel, podemos usar un bucle For…Next. Así:

Dim x As Long
'We know there are exactly 50 rows and they start in row number 2
For x = 2 to 51
    'Here goes the code that applies to every row.
Next x

Si NO sabemos cuantas filas de datos tiene el Excel, mejor usamos un bucle While…Loop. Así:

Dim x As Long
Dim Id as Long
'We don't know how many rows to iterate so we ask if the Id column has a number greater than zero.
x = 2 'Data starts in row number 2
Id = XL.Sheets(1).Cells(x, 1) 'Read the first Id for the first row
Do While x > 0
   'Here goes the code that applies to every row.
 
   'This code needs to be at the end of the loop
   'Reads the next row
   x = x + 1 'Increase the row counter
   Id = XL.Sheets(1).Cells(x, 1)
Loop

4. Dentro del bucle necesitamos leer las columnas A, B, C y D que corresponden a el Id, Nombre, Apellidos y la Foto

Id = XL.Sheets(1).Cells(x, 1)
FirstName = XL.Sheets(1).Cells(x, 2)
LastName = XL.Sheets(1).Cells(x, 3)
PictureFileName = XL.Sheets(1).Cells(x,4)

Un listado completo está incluído al final de este artículo que utiliza el método de “Imágenes dentro de Excel”.

Para usar la ruta al archivo de imagen, dentro de una forma (shape) tienes que usar lo siguiente:

Dim sh as Shape
Set sh = ActivePresentation.Slides(1).Shapes.AddPicture(PictureFileName, msoFalse, msoTrue, 190, 50 + ((i - 1) * 150))

Imágenes dentro de Excel.

Si la foto está en el caso B) entonces está dentro de Excel y necesitamos extraerla. Hay dos formas la fácil y la larga.

Forma Fácil.

Usando el método CopyPicture así:

XL.Sheets(1).Cells(2,4).CopyPicture

Forma larga.

Primero necesitamos ubicar la imagen que corresponde al número de fila dentro del bucle. Para la primera fila necesitamos encontrar su imagen, para la segunda, etc.

Necesitamos iterar todas las formas (Shapes) de la colección así:

For Each Sh In XL.Sheets(1).Shapes
 
Next

Dentro del bucle previo necesitamos preguntar si la forma (shape) es la que queremos.

Ejemplo 1. Usando la propiedad Shape.TopLeftCell.Row

Shape.TopLeftCell.Row = x

Listado.

Dim Sh as Shape
With Worksheets("Sheet1")
For Each Sh In .Shapes
   If Sh.TopLeftCell.Row = x Then
      If Sh.Type = msoPicture Then

      End If
   End If
Next
End With

Ejemplo 2. Intersectando los 2 rangos. Application.Intersect(range1, range2)

Application.Intersect(Sh.TopLeftCell, XL.Sheets(1).Range(Cells(x, 4), Cells(x, 4)))

Listado.

Dim Sh as Shape
With Worksheets("Sheet1")
For Each Sh In .Shapes
If Not Application.Intersect(Sh.TopLeftCell, XL.Sheets(1).Range(Cells(x, 4), Cells(x, 4))) Is Nothing Then
   If Sh.Type = msoPicture Then

   End If
Next
End With

Ejemplo 3. Usando la propiedad Address del objeto Range.

Sh.TopLeftCell.Address = XL.Sheets(1).Range(Cells(x, 4), Cells(x, 4)).Address

“x” representa la fila y “4” representa la columna.

Listo, 3 maneras de encontrar la foto correcta para la fila. Todas ellas iteran la colección completa de formas (shapes) por cada fila del Excel. Si conoces una mejor manera de hacer esto puedes dejar un comentario por favor.

5. Necesitamos copiar la imagen de Excel a PowerPoint. Vamos a utilizar el PortaPapeles o ClipBoard. Para la versión de Excel 2010 y 2013 utiliza el método CopyPicture del objeto Shape.

Shape.CopyPicture

Para versiones previas necesitas llamar funciones API o usar el objeto DataObject, necesitarás referenciar la librería “Microsoft Forms 2.0 Object Library” aquí hay un enlace para más información http://www.cpearson.com/excel/Clipboard.aspx

6. Pega del PortaPapeles (ClipBoard) a la forma (shape) en PowerPoint. Utiliza el método Paste de la colección Shapes. Esto devuelve un objeto ShapeRange.

Dim Sr as ShapeRange
Set Sr = ActivePresentation.Slides(1).Shapes.Paste
'We can move the picture to the desired location
Sr(1).Left = 10
Sr(1).Top = 10

Listado Completo (versión con la imagen incrustada en Excel):

Sub MailMergeWithExcel()
   'Don't forget to reference "Microsoft Excel 15.0 Object Library"
   Dim XL As Excel.Workbook
   Set XL = Excel.Application.Workbooks.Open("c:\Users\Daniel\Desktop\Employees.xlsx")
 
   Dim Id As Long
   Dim FirstName As String
   Dim LastName As String
   Dim x As Long
   Dim Sr As ShapeRange
 
   'first row is the header: id, first name, last name, picture
   'second row is where data starts
   x = 2
 
   Id = XL.Sheets(1).Cells(x, 1)
   Do While Id > 0
      'Get employee info from excel
      FirstName = XL.Sheets(1).Cells(x, 2)
      LastName = XL.Sheets(1).Cells(x, 3)
 
      'Add employee info from excel
      Set sh = ActivePresentation.Slides(1).Shapes.AddTextbox(msoTextOrientationHorizontal, 30, 10 + ((x - 1) * 100), 145, 100)
      sh.TextFrame.TextRange.Font.Size = 14
      sh.TextFrame.TextRange.Text = "First Name : " & Trim(FirstName) & vbNewLine & _
                                    "Last Name: " & Trim(LastName) & vbNewLine & _
                                    "Id : " & Right("00000" & Trim(CStr(Id)), 5)
 
      'Copy the picture in Excel to the clipboard
      XL.Sheets(1).Cells(x, 4).CopyPicture
 
      'Paste the picture into a shape
      Set Sr = ActivePresentation.Slides(1).Shapes.Paste
      Sr(1).Left = 200
      Sr(1).Top = (x - 1) * 100
 
      'Next row
      x = x + 1
      Id = XL.Sheets(1).Cells(x, 1)
   Loop
   'Clean up
   XL.Close
   Set XL = Nothing
End Sub

Notas:

Este es un simple ejemplo de como combinar correspondencia desde Excel a PowerPoint y evitar tipear todo de nuevo adicionalmente explora 2 formas de combinar la imagen desde Excel.

Combina esto con el código del artículo anterior para tener una solución completa.

PowerPoint no tiene una opción “Combinar Correspondencia” (mail merge) todavía, cuando este artículo se público la última versión era 2013.

Un pensamiento en “Combinación de correspondencia (Mail Merge) en PowerPoint usando VBA y Excel.”

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s