Considere una situación en la que se encuentra con una cantidad de archivos de Excel a diario y desea un mecanismo rápido que lo ayude a encontrar el recuento de hojas de trabajo presentes en cada libro de trabajo. Si tiene un problema similar, no debe perderse este artículo, ya que lo ayudará mucho.
En este artículo, aprenderemos cómo contar hojas de trabajo en varios archivos con código VBA.
Pregunta:Necesito una macro que pueda leer una lista de nombres de archivos y devolver la cantidad de hojas de trabajo que están presentes en cada uno de los archivos (este es un mecanismo de auditoría para garantizar que la cantidad correcta de hojas de trabajo esté presente en una serie de archivos creados a través de otro proceso). La macro necesitaría establecer la ruta de la carpeta donde se encuentran los archivos (todos en la misma carpeta), luego ubicar el primer archivo, identificar el recuento de la hoja de trabajo y repetir para el siguiente archivo, etc.
Pensé que podría hacer esto con una fórmula simplemente haciendo referencia a los nombres de los archivos, pero creo que Excel no tiene una fórmula sencilla para los recuentos de hojas de trabajo. ¡Gracias!
Si desea leer la pregunta original, haga clic aquí.
A continuación se muestra la instantánea de los archivos guardados en una carpeta con la extensión .xlsx
Nota: No hay archivos protegidos con contraseña.
Para obtener el código, debemos seguir los pasos a continuación para iniciar el editor de VB:
- Haga clic en la pestaña Desarrollador
- Desde el grupo Código, seleccione Visual Basic
- Copie el siguiente código en el módulo estándar
Sub ListSheetCounts () Dim Cell As Range Dim Conn As Object Dim Cat As Object Dim ConnStr As String Dim n As Long Dim Rng As Range Dim RngEnd As Range Dim WkbPath As Variant Dim Wks As Worksheet 'Ruta de la carpeta donde se encuentran los libros de trabajo. WkbPath = "C: \ Users \ Test" 'Nombre de la hoja de trabajo con la lista de libros de trabajo. Establezca Wks = Worksheets ("Sheet1") 'Celda inicial de la lista de libros de trabajo. Set Rng = Wks.Range ("A2") 'Obtiene el rango de celdas en la lista de nombres del libro de trabajo. Establecer RngEnd = Wks.Cells (Rows.Count, Rng.Column) .End (xlUp) Si RngEnd.Row> = Rng.Row Then Set Rng = Wks.Range (Rng, RngEnd) 'Cree los objetos ADO necesarios para esta macro . Set Conn = CreateObject ("ADODB.Connection") Set Cat = CreateObject ("ADOX.Catalog") 'Agrega una barra invertida final en la ruta si es necesario. WkbPath = IIf (Right (WkbPath, 1) "\", WkbPath & "\", WkbPath) 'Recorra cada celda de la lista del libro de trabajo. For Each Cell In Rng 'Obtenga el recuento de hojas de trabajo para el libro de trabajo. ConnStr = "Proveedor = Microsoft.ACE.OLEDB.12.0; Fuente de datos =" _ & WkbPath & Cell _ & "; Propiedades extendidas =" "Excel 12.0 Xml; HDR = YES; IMEX = 1;" "" Conn.Open ConnStr Establecer Cat.ActiveConnection = Conn 'Copie el recuento en la celda una columna a la derecha del nombre del libro de trabajo en la lista. Cell.Offset (n, 1) = Cat.Tables.Count Conn.Close Next Cell 'Clean up. Establecer Cat = Nada Establecer Conn = Nada End Sub
- A medida que ejecutamos la macro, obtendremos el número de hojas de trabajo. Consulte la siguiente instantánea:
Nota: la macro anterior funcionará para la extensión .xlsx y .xls y no para la extensión habilitada para macros .xlsm.
- Todos los archivos anteriores tienen la extensión .xlsx
- Agreguemos una hoja de Excel ficticia, es decir, la hoja 10
- En caso de que tengamos un archivo con el mismo nombre con extensiones .xlsx y .xls, entonces debemos mencionar el nombre del archivo con sus respectivas extensiones también en nuestro archivo de prueba (columna A) para que la macro pueda identificar el archivo que estamos. refiriéndose y dándonos el resultado correcto
- Si no hemos mencionado o no hemos mencionado la extensión del archivo con el mismo nombre, entonces la macro nos dará el recuento de la extensión .xlsx. Consulte la siguiente instantánea:
- Para obtener el número de hojas de la Hoja 10 con extensiones .xlsx y .xls, debemos mencionar el nombre del archivo con sus respectivas extensiones
La instantánea del resultado final se muestra a continuación:
Conclusión: Usando el código de macro anterior, podemos contar el número de hojas de trabajo en varios archivos y, si es necesario, para obtener el resultado personalizado, podemos hacer pequeñas modificaciones en el código VBA.
Si te gustaron nuestros blogs, compártelo con tus amigos en Facebook. Y también puedes seguirnos en Twitter y Facebook.
Nos encantaría saber de usted, háganos saber cómo podemos mejorar, complementar o innovar nuestro trabajo y hacerlo mejor para usted. Escríbanos al sitio de correo electrónico