En este tutorial, cubriré cómo trabajar con libros de trabajo en Excel usando VBA.

En Excel, un ‘Libro de trabajo’ es un objeto que forma parte de la colección’ Libros de trabajo’. Dentro de un libro de trabajo, tiene diferentes objetos, como hojas de trabajo, hojas de gráficos, celdas y rangos, objetos de gráficos, formas, etc.

Con VBA, puede hacer muchas cosas con un objeto de libro de trabajo, como abrir un libro de trabajo específico, guardar y cerrar libros de trabajo, crear nuevos libros de trabajo, cambiar las propiedades del libro de trabajo, etc.

Así que empecemos.

Si está interesado en aprender VBA de la manera más fácil, consulte mi Capacitación en línea de Excel VBA.

hace Referencia a un Libro utilizando VBA

Hay diferentes formas para referirse a un Libro objeto en VBA. El método que elijas dependerá de lo que quieras hacer. En esta sección, cubriré las diferentes formas de consultar un libro de trabajo junto con algunos códigos de ejemplo.

Usando nombres de libro de trabajo

Si tiene el nombre exacto del libro de trabajo al que desea hacer referencia, puede usar el nombre en el código.

Comencemos con un ejemplo sencillo.

Si tiene dos libros de trabajo abiertos y desea activar el libro de trabajo con el nombre-Ejemplos.xlsx, puede usar el siguiente código:

Sub ActivateWorkbook()Workbooks("Examples.xlsx").ActivateEnd Sub

Tenga en cuenta que debe usar el nombre del archivo junto con la extensión si el archivo se ha guardado. Si no se ha guardado, puede usar el nombre sin la extensión de archivo.

Si no está seguro de qué nombre usar, obtenga ayuda del Explorador de proyectos.

Si desea activar un libro de trabajo y seleccionar una celda específica en una hoja de trabajo en ese libro de trabajo, debe proporcionar la dirección completa de la celda (incluidos el Libro de trabajo y el nombre de la hoja de trabajo).

Sub ActivateWorkbook()Workbooks("Examples.xlsx").Worksheets("Sheet1").ActivateRange("A1").SelectEnd Sub

El código anterior activa primero Sheet1 en los ejemplos.libro de trabajo xlsx y luego selecciona la celda A1 en la hoja.

A menudo verá un código donde se hace una referencia a una hoja de trabajo o una celda/rango sin hacer referencia al libro de trabajo. Esto sucede cuando se refiere a la hoja de trabajo/rangos en el mismo libro de trabajo que tiene el código y también es el libro de trabajo activo. Sin embargo, en algunos casos, es necesario especificar el libro de trabajo para asegurarse de que el código funcione (más información sobre esto en la sección ThisWorkbook).

Utilizando números de índice

También puede consultar los libros de trabajo en función de su número de índice.

Por ejemplo, si tiene tres libros abiertos, el siguiente código le mostrará los nombres de los tres libros en un cuadro de mensaje (uno a la vez).

Sub WorkbookName()MsgBox Workbooks(1).NameMsgBox Workbooks(2).NameMsgBox Workbooks(3).NameEnd Sub

El código anterior utiliza MsgBox, que es una función que muestra un cuadro de mensaje con el texto/valor especificado (que es el nombre del libro de trabajo en este caso).

Uno de los problemas que a menudo tengo con el uso de números de índice con libros de trabajo es que nunca se sabe cuál es el primer libro de trabajo y cuál es el segundo, y así sucesivamente. Para estar seguro, tendría que ejecutar el código como se muestra arriba o algo similar para recorrer los libros de trabajo abiertos y conocer su número de índice.

Excel trata el libro de trabajo abierto primero para tener el número de índice como 1, y el siguiente como 2, y así sucesivamente.

A pesar de este inconveniente, el uso de números de índice puede ser útil. Por ejemplo, si desea recorrer todos los libros abiertos y guardarlos todos, puede usar los números de índice. En este caso, ya que desea que esto le suceda a todos los libros de trabajo, no le preocupan sus números de índice individuales.

El siguiente código recorrería todos los libros de trabajo abiertos y cerraría todos, excepto el libro de trabajo que tiene este código VBA.

Sub CloseWorkbooks()Dim WbCount As IntegerWbCount = Workbooks.CountFor i = WbCount To 1 Step -1If Workbooks(i).Name <> ThisWorkbook.Name ThenWorkbooks(i).CloseEnd IfNext iEnd Sub

El código anterior cuenta el número de libros abiertos y luego recorre todos los libros utilizando el bucle For Each.

Utiliza la condición IF para comprobar si el nombre del libro de trabajo es el mismo que el del libro de trabajo donde se ejecuta el código.

Si no coincide, cierra el libro de trabajo y pasa al siguiente.

Tenga en cuenta que hemos ejecutado el bucle de WbCount a 1 con un Paso de -1. Esto se hace como con cada bucle, el número de libros de trabajo abiertos está disminuyendo.

ThisWorkbook está cubierto en detalle en la sección posterior.

Usando ActiveWorkbook

ActiveWorkbook, como su nombre indica, se refiere al libro de trabajo que está activo.

El siguiente código le mostrará el nombre del libro de trabajo activo.

Sub ActiveWorkbookName()MsgBox ActiveWorkbook.NameEnd Sub

Cuando usa VBA para activar otro libro de trabajo, la parte ActiveWorkbook en el VBA después de eso comenzaría a referirse al libro de trabajo activado.

Aquí hay un ejemplo de esto.

Si tiene un libro de trabajo activo e inserta el siguiente código en él y lo ejecuta, primero mostrará el nombre del libro de trabajo que tiene el código y luego el nombre de los ejemplos.xlsx (que se activa por el código).

Sub ActiveWorkbookName()MsgBox ActiveWorkbook.NameWorkbooks("Examples.xlsx").ActivateMsgBox ActiveWorkbook.NameEnd Sub

Tenga en cuenta que cuando crea un nuevo libro de trabajo con VBA, ese libro de trabajo recién creado se convierte automáticamente en el libro de trabajo activo.

Usando ThisWorkbook

ThisWorkbook se refiere al libro de trabajo donde se está ejecutando el código.

Cada libro de trabajo tendría un objeto ThisWorkbook como parte de él (visible en el Explorador de proyectos).

‘ThisWorkbook’ puede almacenar macros regulares (similares a las que agregamos módulos), así como procedimientos de eventos. Un procedimiento de evento es algo que se activa en función de un evento, como hacer doble clic en una celda, guardar un libro de trabajo o activar una hoja de trabajo.

Cualquier procedimiento de evento que guarde en este ‘ThisWorkbook’ estaría disponible en todo el libro de trabajo, en comparación con los eventos a nivel de hoja que están restringidos solo a las hojas específicas.

Por ejemplo, si hace doble clic en el objeto ThisWorkbook en el Explorador de proyectos y copia y pega el siguiente código en él, mostrará la dirección de la celda cada vez que haga doble clic en cualquiera de las celdas de todo el libro de trabajo.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)MsgBox Target.AddressEnd Sub

Mientras que la función principal de este libro de trabajo es almacenar el procedimiento de eventos, también puede usarlo para referirse al libro de trabajo donde se está ejecutando el código.

El siguiente código devolvería el nombre del libro de trabajo en el que se está ejecutando el código.

Sub ThisWorkbookName()MsgBox ThisWorkbook.NameEnd Sub

El beneficio de usar ThisWorkbook (sobre ActiveWorkbook) es que se referiría al mismo libro de trabajo (el que tiene el código en él) en todos los casos. Por lo tanto, si usa un código VBA para agregar un nuevo libro de trabajo, el libro de trabajo activo cambiaría, pero este libro de trabajo aún se referiría al que tiene el código.

Crear un nuevo objeto de libro de trabajo

El siguiente código creará un nuevo libro de trabajo.

Sub CreateNewWorkbook()Workbooks.AddEnd Sub

Cuando agrega un nuevo libro de trabajo, se convierte en el libro de trabajo activo.

El siguiente código agregará un nuevo libro de trabajo y luego le mostrará el nombre de ese libro de trabajo (que sería el nombre de tipo de libro 1 predeterminado).

Sub CreateNewWorkbook()Workbooks.AddMsgBox ActiveWorkbook.NameEnd Sub

Abra un libro de trabajo usando VBA

Puede usar VBA para abrir un libro de trabajo específico cuando conoce la ruta de archivo del libro de trabajo.

El siguiente código abrirá el libro de trabajo – Ejemplos.xlsx que está en la carpeta Documentos de mi sistema.

Sub OpenWorkbook()Workbooks.Open ("C:\Users\sumit\Documents\Examples.xlsx")End Sub

En caso de que el archivo exista en la carpeta predeterminada, que es la carpeta donde VBA guarda los archivos nuevos de forma predeterminada, solo puede especificar el nombre del libro de trabajo, sin la ruta completa.

Sub OpenWorkbook()Workbooks.Open ("Examples.xlsx")End Sub

En caso de que el libro de trabajo que está intentando abrir no exista, verá un error.

Para evitar este error, puede agregar algunas líneas a su código para verificar primero si el archivo existe o no y si existe, intente abrirlo.

El siguiente código de verificación de la ubicación del archivo y si no existe, se mostrará un mensaje personalizado (no el mensaje de error):

Sub OpenWorkbook()If Dir("C:\Users\sumit\Documents\Examples.xlsx") <> "" ThenWorkbooks.Open ("C:\Users\sumit\Documents\Examples.xlsx")ElseMsgBox "The file doesn't exist"End IfEnd Sub

también puede utilizar el cuadro de diálogo Abrir para seleccionar el archivo que desea abrir.

Sub OpenWorkbook()On Error Resume NextDim FilePath As StringFilePath = Application.GetOpenFilenameWorkbooks.Open (FilePath)End Sub

El código anterior abre el cuadro de diálogo Abrir. Cuando selecciona un archivo que desea abrir, asigna la ruta de archivo a la variable Ruta de archivo. Libro.A continuación, Open utiliza la ruta de acceso del archivo para abrirlo.

En caso de que el usuario no abra un archivo y haga clic en el botón Cancelar, la ruta de archivo se convierte en False. Para evitar que se produzca un error en este caso, hemos utilizado la instrucción «En caso de error, Reanudar siguiente».

Guardar un libro de trabajo

Para guardar el libro de trabajo activo, use el siguiente código:

Sub SaveWorkbook()ActiveWorkbook.SaveEnd Sub

Este código funciona para los libros de trabajo que ya se guardaron anteriormente. Además, dado que el libro de trabajo contiene la macro anterior, si no se ha guardado como a .xlsm (or .xls), perderá la macro cuando la abra a continuación.

Si está guardando el libro de trabajo por primera vez, le mostrará un mensaje como se muestra a continuación:

Al guardar por primera vez, es mejor usar la opción ‘Saveas’.

El siguiente código guardaría el libro de trabajo activo como a .archivo xlsm en la ubicación predeterminada (que es la carpeta de documentos de mi sistema).

Sub SaveWorkbook()ActiveWorkbook.SaveAs Filename:="Test.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabledEnd Sub

Si desea que el archivo se guarde en una ubicación específica, debe mencionarlo en el valor del nombre de archivo. El siguiente código guarda el archivo en mi escritorio.

Sub SaveWorkbook()ActiveWorkbook.SaveAs Filename:="C:\Users\sumit\Desktop\Test.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabledEnd Sub

Si desea que el usuario obtenga la opción de seleccionar la ubicación para guardar el archivo, puede usar llamar al cuadro de diálogo Guardar. El siguiente código muestra el cuadro de diálogo Saveas y permite al usuario seleccionar la ubicación donde se debe guardar el archivo.

Sub SaveWorkbook()Dim FilePath As StringFilePath = Application.GetSaveAsFilenameActiveWorkbook.SaveAs Filename:=FilePath & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabledEnd Sub

Tenga en cuenta que en lugar de usar el formato de archivo:=xlOpenXMLWorkbookMacroEnabled, también puede usar el formato de archivo:=52, donde 52 es el código xlOpenXMLWorkbookMacroEnabled.

Guardar todos los libros de trabajo abiertos

Si tiene más de un libro de trabajo abierto y desea guardar todos los libros de trabajo, puede usar el siguiente código:

Sub SaveAllWorkbooks()Dim wb As WorkbookFor Each wb In Workbookswb.SaveNext wbEnd Sub

Lo anterior guarda todos los libros de trabajo, incluidos los que nunca se han guardado. Los libros de trabajo que no se hayan guardado anteriormente se guardarían en la ubicación predeterminada.

Si solo desea guardar los libros de trabajo que se han guardado anteriormente, puede usar el siguiente código:

Sub SaveAllWorkbooks()Dim wb As WorkbookFor Each wb In WorkbooksIf wb.Path <> "" Thenwb.SaveEnd IfNext wbEnd Sub

Guardar y Cerrar Todos los Libros

Si desea cerrar todos los libros, excepto el libro que tiene el actual código, usted puede utilizar el código siguiente:

Sub CloseandSaveWorkbooks()Dim wb As WorkbookFor Each wb In WorkbooksIf wb.Name <> ThisWorkbook.Name Thenwb.Close SaveChanges:=TrueEnd IfNext wbEnd Sub

El código anterior sería cierre todos los libros (excepto el libro que tiene el código de ThisWorkbook). En caso de que haya cambios en estos libros de trabajo, los cambios se guardarán. En caso de que haya un libro de trabajo que nunca se haya guardado, mostrará el cuadro de diálogo guardar como.

Guardar una copia del libro de trabajo (con marca de tiempo)

Cuando trabajo con datos complejos y paneles en libros de trabajo de Excel, a menudo creo diferentes versiones de mis libros de trabajo. Esto es útil en caso de que algo salga mal con mi libro de trabajo actual. Al menos tendría una copia guardada con un nombre diferente (y solo perdería el trabajo que hice después de crear una copia).

Aquí está el código VBA que creará una copia de su libro de trabajo y lo guardará en la ubicación especificada.

Sub CreateaCopyofWorkbook()ThisWorkbook.SaveCopyAs Filename:="C:\Users\sumit\Desktop\BackupCopy.xlsm"End Sub

El código anterior guardaría una copia de su libro de trabajo cada vez que ejecute esta macro.

Si bien esto funciona muy bien, me sentiría más cómodo si tuviera diferentes copias guardadas cada vez que ejecute este código. La razón por la que esto es importante es que si cometo un error involuntario y corro esta macro, ahorrará el trabajo con los errores. Y no tendría acceso al trabajo antes de cometer el error.

Para manejar tales situaciones, puede usar el siguiente código que guarda una nueva copia del trabajo cada vez que lo guarda. Y también agrega una fecha y una marca de hora como parte del nombre del libro de trabajo. Esto puede ayudarlo a rastrear cualquier error que haya cometido, ya que nunca pierde ninguna de las copias de seguridad creadas anteriormente.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)ThisWorkbook.SaveCopyAs Filename:="C:\Users\sumit\Desktop\BackupCopy" & Format(Now(), "dd-mm-yy-hh-mm-ss-AMPM") & ".xlsm"End Sub

El código anterior crearía una copia cada vez que ejecute esta macro y agregaría una marca de fecha/hora al nombre del libro de trabajo.

Crear un nuevo libro de trabajo para cada hoja de trabajo

En algunos casos, es posible que tenga un libro de trabajo que tenga varias hojas de trabajo y desee crear un libro de trabajo para cada hoja de trabajo.

Este podría ser el caso cuando tiene informes mensuales / trimestrales en un solo libro de trabajo y desea dividirlos en un libro de trabajo para cada hoja de trabajo.

O, si tiene informes de departamento y desea dividirlos en libros de trabajo individuales para que pueda enviar estos libros de trabajo individuales a los jefes de departamento.

Aquí está el código que creará un libro de trabajo para cada hoja de trabajo, le dará el mismo nombre que el de la hoja de trabajo y lo guardará en la carpeta especificada.

Sub CreateWorkbookforWorksheets()Dim ws As WorksheetDim wb As WorkbookFor Each ws In ThisWorkbook.WorksheetsSet wb = Workbooks.Addws.Copy Before:=wb.Sheets(1)Application.DisplayAlerts = Falsewb.Sheets(2).DeleteApplication.DisplayAlerts = Truewb.SaveAs "C:\Users\sumit\Desktop\Test\" & ws.Name & ".xlsx"wb.CloseNext wsEnd Sub

En el código anterior, hemos utilizado dos variables ‘ws’ y ‘wb’.

El código pasa por cada hoja de trabajo (utilizando el bucle Para Cada siguiente) y crea un libro de trabajo para ella. También utiliza el método de copia del objeto de la hoja de trabajo para crear una copia de la hoja de trabajo en el nuevo libro de trabajo.

Tenga en cuenta que he utilizado la instrucción SET para asignar la variable ‘wb’ a cualquier libro de trabajo nuevo creado por el código.

Puede usar esta técnica para asignar un objeto de libro de trabajo a una variable. Esto se trata en la siguiente sección.

Asignar un objeto de libro de trabajo a una variable

En VBA, puede asignar un objeto a una variable y, a continuación, usar la variable para referirse a ese objeto.

Por ejemplo, en el siguiente código, uso VBA para agregar un nuevo libro de trabajo y luego asignar ese libro a la variable wb. Para hacer esto, necesito usar la instrucción SET.

Una vez que he asignado el libro de trabajo a la variable, todas las propiedades del libro de trabajo también están disponibles para la variable.

Sub AssigntoVariable()Dim wb As WorkbookSet wb = Workbooks.Addwb.SaveAs Filename:="C:\Users\sumit\Desktop\Examples.xlsx"End Sub

Tenga en cuenta que el primer paso en el código es declarar ‘wb’ como una variable de tipo de libro de trabajo. Esto le indica a VBA que esta variable puede contener el objeto del libro de trabajo.

La siguiente instrucción utiliza SET para asignar la variable al nuevo libro de trabajo que estamos agregando. Una vez realizada esta tarea, podemos usar la variable wb para guardar el libro de trabajo (o hacer cualquier otra cosa con él).

Bucle a través de libros de trabajo abiertos

Ya hemos visto algunos ejemplos de códigos anteriores que usaban bucle en el código.

En esta sección, explicaré diferentes formas de recorrer libros abiertos utilizando VBA.

Supongamos que desea guardar y cerrar todos los libros de trabajo abiertos, excepto el que tiene el código, luego puede usar el siguiente código:

Sub CloseandSaveWorkbooks()Dim wb As WorkbookFor Each wb In WorkbooksIf wb.Name <> ThisWorkbook.Name Thenwb.Close SaveChanges:=TrueEnd IfNext wbEnd Sub

El código anterior utiliza el bucle For Each para recorrer cada libro de trabajo en la colección de libros de trabajo. Para hacer esto, primero necesitamos declarar ‘wb’ como la variable de tipo de libro de trabajo.

En cada ciclo de bucle, se analiza el nombre de cada libro de trabajo y si no coincide con el nombre del libro de trabajo que tiene el código, se cierra después de guardar su contenido.

Lo mismo también se puede lograr con un bucle diferente, como se muestra a continuación:

Sub CloseWorkbooks()Dim WbCount As IntegerWbCount = Workbooks.CountFor i = WbCount To 1 Step -1If Workbooks(i).Name <> ThisWorkbook.Name ThenWorkbooks(i).Close SaveChanges:=TrueEnd IfNext iEnd Sub

El código anterior utiliza el Siguiente bucle para cerrar todos los libros, excepto el que tiene el código. En este caso, no necesitamos declarar una variable de libro de trabajo, sino que, en su lugar, necesitamos contar el número total de libros de trabajo abiertos. Cuando tenemos el conteo, usamos el bucle For Next para recorrer cada libro de trabajo. Además, usamos el número de índice para referirnos a los libros de trabajo en este caso.

Tenga en cuenta que en el código anterior, estamos haciendo un bucle de WbCount a 1 con el Paso -1. Esto es necesario, ya que con cada bucle, el libro de trabajo se cierra y el número de libros de trabajo se reduce en 1.

Error al trabajar con el objeto del libro de trabajo (Error en tiempo de ejecución ‘9’)

Uno de los errores más comunes que puede encontrar al trabajar con libros de trabajo es-Error en tiempo de ejecución ‘9’ – Subíndice fuera de rango.

Generalmente, los errores de VBA no son muy informativos y a menudo le dejan a usted averiguar qué salió mal.

Aquí hay algunas de las posibles razones que pueden llevar a este error:

• El libro que estás intentando acceder no existe. Por ejemplo, si estoy tratando de acceder al quinto libro de trabajo usando Libros de trabajo(5), y solo hay 4 libros abiertos, obtendré este error.
• Si está utilizando un nombre incorrecto para referirse al libro de trabajo. Por ejemplo, si el nombre de su libro de trabajo es Ejemplos.xlsx y usa el ejemplo.xlsx. luego le mostrará este error.
• Si no ha guardado un libro de trabajo y utiliza la extensión, obtendrá este error. Por ejemplo, si el nombre de su libro de trabajo es Book1 y utiliza el nombre Book1.xlsx sin guardarlo, obtendrá este error.
• El libro de trabajo al que intenta acceder está cerrado.

Obtenga una lista de todos los libros de trabajo abiertos

Si desea obtener una lista de todos los libros de trabajo abiertos en el libro de trabajo actual (el libro de trabajo donde está ejecutando el código), puede usar el siguiente código:

Sub GetWorkbookNames()Dim wbcount As Integerwbcount = Workbooks.CountThisWorkbook.Worksheets.AddActiveSheet.Range("A1").ActivateFor i = 1 To wbcountRange("A1").Offset(i - 1, 0).Value = Workbooks(i).NameNext iEnd Sub

El código anterior agrega una nueva hoja de trabajo y luego enumera el nombre de todos los libros de trabajo abiertos.

Si también desea obtener su ruta de archivo, puede usar el siguiente código:

Sub GetWorkbookNames()Dim wbcount As Integerwbcount = Workbooks.CountThisWorkbook.Worksheets.AddActiveSheet.Range("A1").ActivateFor i = 1 To wbcountRange("A1").Offset(i - 1, 0).Value = Workbooks(i).Path & "\" & Workbooks(i).NameNext iEnd Sub

Abra el libro de trabajo especificado haciendo doble clic en la celda

Si tiene una lista de rutas de archivo para libros de trabajo de Excel, puede usar el siguiente código para simplemente hacer doble clic en la celda con la ruta de archivo y abrirá ese libro de trabajo.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)Workbooks.Open Target.ValueEnd Sub

Este código se colocará en la ventana de código de ThisWorkbook.

Para hacer esto:

• haga Doble clic en el objeto ThisWorkbook en el explorador de proyectos. Tenga en cuenta que el objeto ThisWorkbook debe estar en el libro de trabajo donde desea esta funcionalidad.
• Copie y pegue el código anterior.

Ahora, si tiene la ruta exacta de los archivos que desea abrir, puede hacerlo simplemente haciendo doble clic en la ruta del archivo y VBA abriría instantáneamente ese libro de trabajo.

¿Dónde colocar el código VBA

Preguntándose a dónde va el código VBA en su libro de Excel?

Excel tiene un motor de VBA llamado editor de VBA. Debe copiar y pegar el código en la ventana de código del módulo del editor VB.

Estos son los pasos para hacer esto:

1. Ir a la pestaña Desarrollador.
2. Haga clic en la opción Visual Basic. Esto abrirá el editor VB en el backend.
3. En el panel Explorador de proyectos en el Editor VB, haga clic con el botón derecho en cualquier objeto del libro de trabajo en el que desee insertar el código. Si no ve el Explorador de proyectos, vaya a la pestaña Ver y haga clic en Explorador de proyectos.
4. Vaya a Insertar y haga clic en Módulo. Esto insertará un objeto de módulo para su libro de trabajo.
5. Copie y pegue el código en la ventana del módulo.

También le pueden gustar los siguientes tutoriales de VBA de Excel:

• Cómo grabar una macro en Excel.
• Crear una Función Definida por el Usuario en Excel.
• Cómo crear y usar Complementos en Excel.
• Cómo Volver a asignar Macros colocándolas en el Libro de Trabajo de Macros Personal.
• Obtenga la Lista de nombres de archivo de una carpeta en Excel (con y sin VBA).
• Cómo usar la función InStr de Excel VBA (con EJEMPLOS prácticos).
• Cómo ordenar los datos en Excel usando VBA (Una Guía Paso a paso).