Libros con VBA: el objeto Workbook
Según la jerarquía de objetos de VBA dentro de un libro puedes encontrar objetos como hojas, rangos de celdas, gráficos, formas, etc.
Con VBA puedes hacer muchas cosas con un objeto Workbook, por ejemplo abrir un libro específico, guardarlo, cerrarlo, crear uno nuevo, etc., además de cambiar sus propiedades.
En este tutorial te mostraré cómo realizar alguna de estas acciones habituales mediante libros con VBA.
¡Comenzamos!
Cómo hacer referencia a un libro con VBA
Existen varias formas de hacer referencia a libros con VBA. El método que elijas dependerá de lo que quieras hacer con él. Te muestro algunos ejemplos para facilitarte la elección.
Mediante el nombre del libro
Si conoces el nombre exacto del libro al que quieres hacer referencia, puedes utilizar el nombre en el código.
Imagina que tienes dos libros abiertos (Ejemplo.xls y Test.xlsm). y desde el segundo, quieres activar el primero. Puedes usar este procedimiento:
Sub ActivateWorkbook()
Workbooks("Ejemplo.xlsx").Activate
End Sub
Debes tener en cuenta que para hacer referencia al archivo tienes que incluir la extensión del libro si ya ha sido guardado. Si todavía no se ha guardado, puedes usar el nombre del archivo sin la extensión. Si sabes que el libro está abierto pero no recuerdas su nombre, puedes echar un vistazo al Explorador de proyectos de VBA.
Si lo que quieres es activar un libro y además seleccionar una celda específica en cierta hoja de trabajo, necesitas escribir la dirección completa de la celda (incluyendo el libro y el nombre de la hoja):
Workbooks("Ejemplo.xlsx").Worksheets("Hoja1").Activate
Range("A1").Select
End SubEste código primero activa el libro y la hoja y luego selecciona la celda A1.
En muchas ocasiones verás líneas de código en las que se hace referencia a una hoja y/o celda sin hacer referencia al libro. Esto sucede cuando la celda o el rango al que se hace referencia se encuentra en el mismo libro que tiene el código y además se trata del libro activo.
Sin embargo, en algunos casos es necesario especificar el libro con VBA para asegurarte de que el código funcione (lo trataré más adelante al hablar de Thisworkbook).
Desde su número de índice
También es posible hacer referencia a un libro en función de su número de índice.
Por ejemplo, si tienes tres libros abiertos, el siguiente código mostrará los nombres de los tres, en un cuadro de mensaje (de uno en uno).
Sub NombreLibro()
MsgBox Workbooks(1).Name
MsgBox Workbooks(2).Name
MsgBox Workbooks(3).Name
End Sub
El código anterior utiliza la función integrada MsgBox para mostrar un cuadro de mensaje con el texto o valor que se especifique (en este caso es el nombre del libro).
Uno de los problemas que surgen al usar los números de índice de los libros es que nunca se sabe cuál es el primero, cuál el segundo y cuál el tercero. Para asegurarte, antes de ejecutar el código anterior tendrías que usar un bucle que recorra todos los libros abiertos y conocer de antemano su número de índice.
Excel asigna el número 1 al libro abierto en primer lugar, el número 2 al siguiente y así sucesivamente.
A pesar de este inconveniente, los números de índice pueden ser útiles. Imagina que quieres recorrer todos los libros abiertos y cerrarlos todos. En este caso no es necesario que estén en un orden en concreto, lo que quieres es cerrarlos todos.
El siguiente código hace esto mismo: recorre todos los libros abiertos y los cierra excepto el libro que tiene el código.
Sub CerrarLibros()
Dim WbCuenta As Integer
WbCuenta = Workbooks.Count
For i = WbCuenta To 1 Step -1
If Workbooks(i).Name <> ThisWorkbook.Name Then
Workbooks(i).Close
End If
Next i
End Sub
Este código cuenta el número de libros abiertos en ese momento y los recorre usando el bucle For Each-Next.
Utiliza la condición If para comprobar si el libro es el mismo en el que se está ejecutando el código.
Si no coincide, cierra el libro y pasa al siguiente.
Observa que el bucle se ejecuta de forma inversa (desde WbCuenta hasta 1). Esto se hace así porque en cada vuelta el número de libros va disminuyendo.
Más adelante en el tutorial abordaré esto.
Con ActiveWorkbook
ActiveWorkbook, como indica su nombre, se refiere al libro activo. El siguiente código muestra el nombre del libro activo:
Sub NombreLibroActivo()
MsgBox ActiveWorkbook.Name
End Sub
Al ejecutar un código como el anterior siempre debes tener presente cuál es el libro activo para no obtener resultados erróneos.
Si introduces el siguiente código en un libro y lo ejecutas, primero mostrará el nombre del libro que tiene el código y luego el nombre de Ejemplo.xlsx (que se activa en el código).
Sub NombreLibroActivo()
MsgBox ActiveWorkbook.Name
Workbooks("Ejemplo.xlsx").Activate
MsgBox ActiveWorkbook.Name
End Sub
Con Thisworkbook
ThisWorkbook se refiere al libro de Excel donde se está ejecutando el código.
Cada libro tiene un objeto ThisWorkbook, el cual hace referencia a sí mismo.
En el objeto ThisWorkbook del Explorador de proyectos puedes almacenar procedimientos Sub (los habituales) y procedimientos de eventos. Un procedimiento de evento es un código que se activa cuando se realiza alguna acción (por ejemplo hacer doble clic en una celda, guardar el libro o activar una hoja).
Cualquier procedimiento de evento que guardes en este ‘ThisWorkbook’ podrás usarlo en todo el libro de trabajo (a diferencia de los procedimientos a nivel de hoja).
Por ejemplo, si haces doble clic en el objeto ThisWorkbook y pegas el siguiente código, se mostrará la dirección de la celda cuando hagas doble clic en cualquier celda en todo el libro.
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
MsgBox Target.Address
End Sub
Aunque la función principal de ThisWorkbook es almacenar procedimientos de evento, también puedes usarlo para hacer referencia al libro donde se está ejecutando el código.
El siguiente procedimiento devuelve el nombre del libro que contiene el código.
Sub NombreThisWorkbook()
MsgBox ThisWorkbook.Name
End Sub
¿Qué diferencia existe entre usar ThisWorkbook y ActiveWorkbook? Sencillamente en que ThisWorkbook hace referencia siempre al mismo libro, mientras que ActiveWorkbook puede variar en función del libro activo en un momento determinado.
Cómo crear nuevos libros con VBA
El siguiente procedimiento crea un libro nuevo:
Sub CreaLibro()
Workbooks.Add
End Sub
Como mencioné anteriormente, cuando se añade un nuevo libro de Excel, éste se convierte en el libro activo.
El siguiente código añadirá un nuevo libro y luego mostrará el nombre de ese libro en un cuadro de mensaje:
Sub AbrirLibro()
Workbooks.Open ("C:\Users\sergio\Desktop\Ejemplo.xlsx")
End Sub
En caso de que el archivo se encuentre en la carpeta predeterminada, que es la carpeta donde VBA guarda los archivos por defecto (suele ser Mis Documentos), puedes especificar solo el nombre el libro, sin la ruta completa.
Sub AbrirLibro()
Workbooks.Open ("Ejemplo.xlsx")
End Sub
Si el libro que intentas abrir no existe se mostrará un error.
Para evitarlo, puedes añadir algunas líneas al código para comprobar primero si el archivo existe o no, y, en caso afirmativo, intentar abrirlo.
El siguiente código comprobará la ubicación del archivo. Si no existe mostrará un mensaje personalizado, no el mensaje de error:
Sub AbrirLibro()
If Dir("C:\Users\sergio\Desktop\Ejemplo.xlsx") <> "" Then
Workbooks.Open ("C:\Users\sergio\Desktop\Ejemplo.xlsx")
Else
MsgBox "El archivo no existe."
End If
End Sub
Una buena alternativa al código anterior es hacer que se muestre el cuadro de diálogo Abrir para que el usuario pueda buscar el archivo en el equipo. Esto te evitará muchos errores:
Sub AbrirLibro()
On Error Resume Next
Dim Ruta As String
Ruta = Application.GetOpenFilename
Workbooks.Open (Ruta)
End Sub
El código anterior muestra el cuadro de diálogo Abrir. Cuando seleccionas el archivo que deseas abrir, asigna la ruta del archivo a la variable Ruta. Workbooks.Open utiliza entonces la ruta del archivo para abrirlo.
En caso de que el usuario no abra un archivo y haga clic en el botón cancelar, la variable Ruta se convierte en False. Para evitar este error he utilizado la instrucción On Error Resume Next.
Guardar un libro con VBA
La forma más sencilla de guardar un libro con VBA es con el siguiente código:
Sub GuardarLibro()
ActiveWorkbook.Save
End Sub
Este procedimiento funciona únicamente para los libros que ya han sido guardados previamente. Además, si el libro que contiene la macro anterior no tiene la extensión .xlsm (o .xls), perderá la macro cuando se abra de nuevo.
Si estás guardando un libro con VBA por primera vez, se mostrará un mensaje como el siguiente:
Cuando se guarda un libro por primera vez es mejor utilizar la opción ‘Guardar como’.
El siguiente código guarda el libro activo con la extensión .xlsm en la ubicación predeterminada (Mis Documentos en mi caso).
Sub GuardaLibro()
ActiveWorkbook.SaveAs Filename:="Ejemplo.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub
Si quieres que el archivo se guarde en una ubicación específica, debes mencionarlo en el valor de Filename. El siguiente código guarda el libro en mi escritorio.
Sub GuardaLibro()
ActiveWorkbook.SaveAs Filename:="C:\Users\sergio\Desktop\Ejemplo.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub
Si quieres que el usuario tenga la opción de seleccionar la ubicación para guardar el archivo, puedes mostrar el cuadro de diálogo Guardar como.
El siguiente procedimiento muestra el cuadro Guardar como y permite al usuario seleccionar la ubicación donde guardar el archivo.
Sub GuardaLibro()
Dim Ruta As String
Ruta = Application.GetSaveAsFilename
ActiveWorkbook.SaveAs Filename:=Ruta & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub
Si lo deseas puedes sustituir la línea FileFormat:=xlOpenXMLWorkbookMacroEnabled por FileFormat:=52, pues este número es el valor fijo de la variable xlOpenXMLWorkbookMacroEnabled.
Guardar todos los libros abiertos
Si tienes más de un libro abierto y quieres guardarlos todos en un abrir y cerrar de ojos, puedes utilizar el siguiente procedimiento:
Sub GuardaLibros()
Dim wb As Workbook
For Each wb In Workbooks
wb.Save
Next wb
End Sub
Este código guarda todos los libros de Excel incluyendo los que todavía no se han guardado por primera vez. Estos últimos se guardarán en la carpeta predeterminada del sistema. Si únicamente quieres guardar los libros que ya han sido previamente guardados puedes utilizar el siguiente código:
Sub GuardaLibros()
Dim wb As Workbook
For Each wb In Workbooks
If wb.Path <> "" Then
wb.Save
End If
Next wb
End Sub
Guardar y cerrar todos los libros
Si quieres guardar y cerrar todos los libros excepto el activo, puedes usar el siguiente código:
Sub GuardaCierraLibros()
Dim wb As Workbook
For Each wb In Workbooks
If wb.Name <> ThisWorkbook.Name Then
wb.Close SaveChanges:=True
End If
Next wb
End Sub
Este código cierra todos los libros excepto el que contiene el código. En caso de que haya cambios en estos libros, los cambios se guardarán. Si existe algún libro que no se ha guardado nunca se mostrará el cuadro de diálogo Guardar como.
Guardar una copia del libro con VBA (con la fecha de guardado)
Cuando ejecutas una macro, las herramientas Deshacer y Rehacer pierden las acciones que almacenan sin poder recuperarlas (a no ser que cierres el libro sin guardar). Por ello, mientras estoy trabajando con VBA suelo crear diferentes versiones de mis libros con VBA. En caso de que la macro no funcione y no pueda recuperar el libro, al menos tengo una copia guardada y no pierdo todo el trabajo.
Al principio comencé a usar el siguiente código que me servía para guardar una copia del libro cada vez que ejecutaba el procedimiento:
Sub CreaCopia()
ThisWorkbook.SaveCopyAs Filename:="C:\Users\sergio\Desktop\Copia seguridad.xlsm"
End Sub
Este código funcionaba muy bien pero me di cuenta de que si ejecutaba la macro cuando el libro contenía errores, la copia sobrescribía el archivo anterior. Ya no tenía acceso la copia ‘buena’, sino al archivo con errores.
Ante esta situación cambié la forma de guardar la copia del libro. Comencé a utilizar el siguiente procedimiento que guarda un archivo diferente cada vez que se ejecuta. Para ello se modifica el nombre del archivo agregando la fecha y la hora de guardado (por tanto no puede haber dos archivos con el mismo nombre).
Esto me ayudó mucho a rastrear los errores que se producían en las macros, pudiendo volver a la última versión ‘buena’.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ThisWorkbook.SaveCopyAs Filename:="C:\Users\sergio\Desktop\Copia seguridad" & _
Format(Now(), "dd-mm-yy-hh-mm-ss-AMPM") & ".xlsm"
End Sub
Con este código tengo la total seguridad de que nunca se sobrescribirán los archivos y en caso de error, puedo recuperar la versión que necesito.
Crear un libro nuevo por cada hoja
A veces me encuentro en la situación de tener un libro de Excel con varias hojas que quiero convertir en un libro diferente.
Esto puede ser interesante cuando trabajas con informes mensuales o anuales en un solo libro y quieres dividirlo en periodos convirtiendo cada hoja en un libro independiente para enviarlos fácilmente por correo electrónico.
El siguiente procedimiento crea un libro diferente de cada hoja de trabajo, asignándole que el de la hoja y lo guarda en la carpeta especificada.
Sub CreaLibrosHoja()
Dim ws As Worksheet
Dim wb As Workbook
For Each ws In ThisWorkbook.Worksheets
Set wb = Workbooks.Add
ws.Copy Before:=wb.Sheets(1)
Application.DisplayAlerts = False
wb.Sheets(2).Delete
Application.DisplayAlerts = True
wb.SaveAs "C:\Users\sergio\Desktop\Carpeta\" & ws.Name & ".xlsx"
wb.Close
Next ws
End Sub
En este código he usado dos variables: ws para la hoja y wb para el libro.
El código recorre cada hoja (con el bucle For Each-Next) y crea un nuevo libro para ella. También utiliza el método Copy para crear una copia de la hoja en el nuevo libro.
Observa que he utilizado la instrucción Set para asignar la variable wb a cualquier libro nuevo que se cree.
Puedes utilizar esta técnica para asignar un objeto del libro a una variable. Esto lo abordaré en un siguiente tutorial.
Resumen
Como acabas de comprobar, el objeto Workbook tiene infinitas posibilidades de uso. Al tratarse de un objeto de ‘alto nivel’ en la jerarquía de VBA he decidido dedicar el siguiente tutorial a algunas tareas habituales para el uso de libros con VBA.