Usando el objeto del libro de trabajo en Excel VBA (Abrir, Cerrar, Guardar, Establecer)

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.

Entonces empecemos.

Todos los códigos que menciono en este tutorial deben colocarse en el Editor de Visual Basic. Vaya a la sección "Dónde colocar el código VBA" para saber cómo funciona.

Si está interesado en aprender VBA de forma sencilla, consulte mi Capacitación en línea de Excel VBA.

Hacer referencia a un libro de trabajo usando VBA

Hay diferentes formas de hacer referencia a un objeto de libro de trabajo en VBA. El método que elija dependerá de lo que desee hacer. En esta sección, cubriré las diferentes formas de hacer referencia a un libro de trabajo junto con algunos códigos de ejemplo.

Usar nombres de libros 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 abiertos y desea activar el libro con el nombre - Examples.xlsx, puede usar el siguiente código:

Sub ActivateWorkbook () Libros de trabajo ("Examples.xlsx"). Activar End Sub

Tenga en cuenta que debe utilizar 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 del archivo.

Si no está seguro de qué nombre usar, busque la 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 (incluido el libro de trabajo y el nombre de la hoja de trabajo).

Sub ActivateWorkbook () Workbooks ("Examples.xlsx"). Worksheets ("Sheet1"). Active Range ("A1"). Seleccione End Sub

El código anterior primero activa Sheet1 en el libro de trabajo Examples.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 sobre esto en la sección ThisWorkbook).

Usar números de índice

También puede consultar los libros de trabajo según 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) .Name MsgBox Workbooks (2) .Name MsgBox Workbooks (3) .Name End Sub

El código anterior usa 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 los 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 abiertos y conocer su número de índice.

Excel trata el libro 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 resultar útil. Por ejemplo, si desea recorrer todos los libros abiertos y guardar todos, puede usar los números de índice. En este caso, dado que desea que esto suceda con 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 Integer WbCount = Workbooks.Count For i = WbCount To 1 Step -1 If Workbooks (i) .Name ThisWorkbook.Name Then Workbooks (i) .Close End If Next i End Sub

El código anterior cuenta el número de libros abiertos y luego pasa por todos los libros usando el ciclo 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 ciclo de WbCount a 1 con un paso de -1. Esto se hace como con cada ciclo, la cantidad de libros abiertos está disminuyendo.

Este libro de trabajo se trata en detalle en la sección posterior.

Uso de 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 activo.

Sub ActiveWorkbookName () MsgBox ActiveWorkbook.Name End Sub

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

He aquí 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 Examples.xlsx (que se activa con el código).

Sub ActiveWorkbookName () MsgBox ActiveWorkbook.Name Workbooks ("Examples.xlsx"). Active MsgBox ActiveWorkbook.Name End 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.

Uso de este libro de trabajo

Este libro de trabajo 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 "Este libro de trabajo" estará disponible en todo el libro de trabajo, en comparación con los eventos de nivel de hoja que están restringidos solo a 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, se mostrará la dirección de la celda cada vez que haga doble clic en cualquiera de las celdas de todo el libro.

Private Sub Workbook_SheetBeforeDoubleClick (ByVal Sh como objeto, ByVal Target como rango, Cancelar como booleano) MsgBox Target.Address End Sub

Si bien la función principal de ThisWorkbook es almacenar el procedimiento de eventos, también puede usarlo para consultar el 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.Name End 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 todos los casos. Entonces, si usa un código VBA para agregar un nuevo libro de trabajo, el ActiveWorkbook cambiaría, pero ThisWorkbook 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.Add End Sub

Cuando agrega un nuevo libro, se convierte en el libro 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 Book1 predeterminado).

Sub CreateNewWorkbook () Workbooks.Add MsgBox ActiveWorkbook.Name End Sub

Abra un libro de trabajo usando VBA

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

El siguiente código abrirá el libro de trabajo: Examples.xlsx, que se encuentra 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, 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 verificaría la ubicación del archivo y, si no existe, mostrará un mensaje personalizado (no el mensaje de error):

Sub OpenWorkbook () If Dir ("C: \ Users \ sumit \ Documents \ Examples.xlsx") "" Entonces Workbooks.Open ("C: \ Users \ sumit \ Documents \ Examples.xlsx") Else MsgBox "El archivo no 't existe "End If End Sub

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

Sub OpenWorkbook () En caso de error Reanudar Siguiente Dim FilePath como cadena FilePath = Application.GetOpenFilename Workbooks.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 del archivo a la variable FilePath. Workbooks.Open luego usa la ruta del archivo para abrir el archivo.

En caso de que el usuario no abra un archivo y haga clic en el botón Cancelar, FilePath se vuelve Falso. Para evitar obtener un error en este caso, hemos utilizado la declaración "En caso de error, reanudar a continuación".

Relacionada: Aprenda todo sobre el manejo de errores en Excel VBA

Guardar un libro de trabajo

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

Sub SaveWorkbook () ActiveWorkbook.Save End 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 un archivo .xlsm (o .xls), perderá la macro la próxima vez que la abra.

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 utilizar la opción "Guardar como".

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

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

Si desea que el archivo se guarde en una ubicación específica, debe mencionarlo en el valor 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: = xlOpenXMLWorkbookMacroEnabled End Sub

Si desea que el usuario tenga la opción de seleccionar la ubicación para guardar el archivo, puede usar llamar al cuadro de diálogo Saveas. 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 String FilePath = Application.GetSaveAsFilename ActiveWorkbook.SaveAs Filename: = FilePath & ".xlsm", FileFormat: = xlOpenXMLWorkbookMacroEnabled End Sub

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

Guardar todos los libros abiertos

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

Sub Guardar todos los libros de trabajo () Dim wb como libro de trabajo para cada wb en los libros de trabajo wb. Guardar siguiente wb End 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án 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 Workbook For each wb In Workbooks If wb.Path "" Then wb.Save End If Next wb End Sub

Guardar y cerrar todos los libros de trabajo

Si desea cerrar todos los libros de trabajo, excepto el libro de trabajo que tiene el código actual, puede usar el siguiente código:

Sub CloseandSaveWorkbooks () Dim wb como libro de trabajo para cada wb en libros de trabajo Si wb.Name ThisWorkbook.Name Entonces wb.Close SaveChanges: = True End If Next wb End Sub

El código anterior cerraría todos los libros de trabajo (excepto el libro de trabajo que tiene el código - 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 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 ejecuto este código. La razón por la que esto es importante es que si cometo un error inadvertido y ejecuto esta macro, guardaré 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 tiempo como parte del nombre del libro de trabajo. Esto puede ayudarlo a rastrear cualquier error que haya cometido, ya que nunca perderá ninguna de las copias de seguridad creadas anteriormente.

Private Sub Workbook_BeforeSave (ByVal SaveAsUI como booleano, cancelar como booleano) ThisWorkbook.SaveCopyAs Filename: = "C: \ Users \ sumit \ Desktop \ BackupCopy" & Format (Now (), "dd-mm-aa-hh-mm-ss -AMPM ") y" .xlsm "End Sub

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

Cree 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 poder 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 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 \ sumit \ Desktop \ Test \" & ws.Name & ".xlsx" wb.Close Next ws End Sub

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

El código pasa por cada hoja de trabajo (usando el ciclo For Each Next) y crea un libro de trabajo para él. 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 usado la instrucción SET para asignar la variable 'wb' a cualquier libro de trabajo nuevo creado por el código.

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

Asignar objeto de libro de trabajo a una variable

En VBA, puede asignar un objeto a una variable y luego 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 Workbook Set wb = Workbooks.Add wb.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 dice a VBA que esta variable puede contener el objeto del libro de trabajo.

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

Recorrer libros abiertos

Ya hemos visto algunos códigos de ejemplo anteriores que usaban bucles en el código.

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

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

Sub CloseandSaveWorkbooks () Dim wb como libro de trabajo para cada wb en libros de trabajo Si wb.Name ThisWorkbook.Name Entonces wb.Close SaveChanges: = True End If Next wb End Sub

El código anterior usa el bucle For Each para revisar cada libro de trabajo en la colección Workbooks. Para hacer esto, primero debemos 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 Integer WbCount = Workbooks.Count For i = WbCount To 1 Step -1 If Workbooks (i) .Name ThisWorkbook.Name Then Workbooks (i) .Close SaveChanges: = True End If Next i End Sub

El código anterior usa el bucle For Next para cerrar todos los libros de trabajo, excepto el que tiene el código. En este caso, no necesitamos declarar una variable de libro de trabajo, sino que debemos contar el número total de libros de trabajo abiertos. Cuando tenemos el recuento, usamos el ciclo For Next para revisar 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 pasando de WbCount a 1 con el Paso -1. Esto es necesario ya que con cada ciclo, el libro de trabajo se cierra y la cantidad de libros de trabajo se reduce en 1.

Error al trabajar con el objeto del libro de trabajo (error de tiempo de ejecución "9")

Uno de los errores más comunes que puede encontrar al trabajar con libros de trabajo es: Error de tiempo de ejecución "9": subíndice fuera de rango.

Generalmente, los errores de VBA no son muy informativos y, a menudo, dejan que usted averigüe qué salió mal.

Estas son algunas de las posibles razones que pueden provocar este error:

• El libro de trabajo al que está intentando acceder no existe. Por ejemplo, si intento acceder al quinto libro de trabajo usando Workbooks (5), y solo hay 4 libros de trabajo abiertos, obtendré este error.
• Si está utilizando un nombre incorrecto para hacer referencia al libro de trabajo. Por ejemplo, si el nombre de su libro de trabajo es Examples.xlsx y usa Example.xlsx. entonces le mostrará este error.
• Si no ha guardado un libro de trabajo y usa la extensión, aparece este error. Por ejemplo, si el nombre de su libro de trabajo es Libro1 y usa el nombre Libro1.xlsx sin guardarlo, obtendrá este error.
• El libro de trabajo al que intentas 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 Integer wbcount = Workbooks.Count ThisWorkbook.Worksheets.Add ActiveSheet.Range ("A1"). Active For i = 1 To wbcount Range ("A1"). Offset (i - 1, 0). Valor = Libros de trabajo (i). Nombre Siguiente i Fin Sub

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

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

Sub GetWorkbookNames () Dim wbcount As Integer wbcount = Workbooks.Count ThisWorkbook.Worksheets.Add ActiveSheet.Range ("A1"). Active For i = 1 To wbcount Range ("A1"). Offset (i - 1, 0). Valor = Libros de trabajo (i). Ruta & "\" & Libros de trabajo (i). Nombre Siguiente i Finalizar sub

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

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

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

Este código se colocaría 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á instantáneamente ese libro de trabajo.

Dónde poner el código VBA

¿Se pregunta dónde va el código VBA en su libro de Excel?

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

Estos son los pasos para hacer esto:

1. Vaya a la pestaña Desarrollador.
2. Haga clic en la opción de Visual Basic. Esto abrirá el editor de VB en el backend.
3. En el panel Explorador de proyectos en el Editor de VB, haga clic con el botón derecho en cualquier objeto del libro en el que desea 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 Excel VBA:

• Cómo grabar una macro en Excel.
• Creación de una función definida por el usuario en Excel.
• Cómo crear y usar un complemento en Excel.
• Cómo recuperar macros colocándolo en el libro de macros personal.
• Obtenga la lista de nombres de archivos de una carpeta en Excel (con y sin VBA).
• Cómo utilizar la función InStr de Excel VBA (con EJEMPLOS prácticos).
• Cómo ordenar datos en Excel usando VBA (una guía paso a paso).