Macro para modificar archivos de Excel sin abrirlos
La siguiente macro que les comparto es en base a una consulta que me hicieron en en Blog. La pregunta decía:
La idea es la siguiente: Tengo “n” archivos y todos ellos contienen en la celda E6 una valor que quiero modificar. Los nuevos valores los tengo en un listado también en excel, que relaciona cada archivo.
¿Existe alguna manera de grabar una macro para cambiar automáticamente todos los archivos en lugar de abrir uno por uno?
Fue entonces cuando caí en la cuenta que ya antes había visto que usuarios tenían esa duda, por lo que decidí hacer este artículo.
Ver video Modificar archivos de Excel sin abrirlos
Suscríbete al canal de EXCELeINFO en YouTube para aprender más de Excel y macros.
Modificando un archivo en particular
En la siguiente macro, modificamos la ruta y nombre completo en la variable NombreArchivo.
Sub Modificar1XLCerrado() 'Declaramos variables Dim Archivo As Application Dim NombreArchivo As String ' 'Creamos el objecto Excel Set Archivo = CreateObject("Excel.Application") ' With Archivo ' 'Asignamos el nombre del archivo NombreArchivo = "C:carpetaLibro1.xlsx" ' 'Validamos si el archivo ya está abierto If IsFileOpen(NombreArchivo) Then Else ' With .Workbooks.Open(NombreArchivo) 'Hacemos las modificaciones en el archivo .Worksheets("Hoja1").Range("A1").Value = "Total1" .Worksheets("Hoja1").Range("A2").Value = 11 'Cerramos el archivo guardando cambios .Close SaveChanges:=True End With End If ' 'Cerramos la aplicación de Excel .Quit End With End Sub
Modificando varios archivos
Para esta macro recomiendo hacer una lista con las rutas y los nombres de los archivos a modificar. Antes de ejecutar la macro debemos seleccionar el listado para que el constructor For Each Next haga el resto. La lista la podremos generar con mi herramienta que viene dentro de EXCELeINFO addin para enlistar archivos. La siguiente es una tabla de ejemplo.
Macro
Sub ModificarXLCerrados() 'Declaramos variables Dim Archivo As Application Dim Celda As Object Dim NombreArchivo As String ' 'Creamos el objecto Excel Set Archivo = CreateObject("Excel.Application") ' With Archivo ' 'Recorremos cada celda de la selección para tomar el nombre de cada archivo For Each Celda In Selection NombreArchivo = Celda.Value ' 'Validamos si el archivo ya está abierto If IsFileOpen(NombreArchivo) Then Else ' With .Workbooks.Open(NombreArchivo) 'Hacemos las modificaciones en el archivo .Worksheets("Hoja1").Range("A1").Value = "Total" .Worksheets("Hoja1").Range("A2").Value = 10 'Cerramos el archivo guardando cambios .Close SaveChanges:=True End With End If ' Next Celda ' 'Cerramos la aplicación de Excel .Quit End With End Sub
Función IsFileOpen
La siguiente función nos permitirá saber si el archivo a modificar ya está abierto. Es una función publicada en la página de Soporte de Microsoft y la podremos descargar desde http://support.microsoft.com/kb/291295/es
Macro
' This function checks to see if a file is open or not. If the file is ' already open, it returns True. If the file is not open, it returns ' False. Otherwise, a run-time error occurs because there is ' some other problem accessing the file. ' Código de macro para comprobar si un archivo ya está abierto ' http://support.microsoft.com/kb/291295/es ' Function IsFileOpen(filename As String) Dim filenum As Integer, errnum As Integer ' On Error Resume Next ' Turn error checking off. filenum = FreeFile() ' Get a free file number. ' Attempt to open the file and lock it. Open filename For Input Lock Read As #filenum Close filenum ' Close the file. errnum = Err ' Save the error number that occurred. On Error GoTo 0 ' Turn error checking back on. ' Check to see which error occurred. Select Case errnum ' No error occurred. ' File is NOT already open by another user. Case 0 IsFileOpen = False ' Error number for "Permission Denied." ' File is already opened by another user. Case 70 IsFileOpen = True ' Another error occurred. Case Else Error errnum End Select End Function
Gracias!
Estimado, intente aplicar tu código pero se me complico un poco, a ver si me puedes ayudar.
Necesito hacer una(s) macro(s) para modificar más rápido unos archivos. Tengo unas planillas mensuales que tienen hojas por días, de las cuales debo hacer un resumen mensual para luego obtener un resumen anual.
Con el complemento EXCELeINFO, obtuve las siguientes direcciones de archivos que deseo modificar
D:2008 ok11 Diario_ene_2008.xls
D:2008 ok12 Diario_Feb_2008.xls
D:2008 ok13 Diario_Mar_2008.xls
D:2008 ok14 Diario_Abr_2008.xls
D:2008 ok15 Diario_May_2008.xls
D:2008 ok16 Diario_Jun_2008.xls
D:2008 ok17 Diario_Jul_2008.xls
D:2008 ok18 Diario_Ago_2008.xls
D:2008 ok19 Diario_Sep_2008.xls
D:2008 ok110 Diario_oct_2008.xls
D:2008 ok111 Diario_Nov_2008.xls
D:2008 ok112 Diario_Dic_2008.xls
Aparte de lo mostrado para el 2008, tengo archivos hasta el 2013.
Si me puedes ayudar a modificar tu código con las siguientes caracteristicas:
1ro) Debo modificar los nombres de las hojas de los archivos mensuales, para lo cual tengo esta macro que me funciona en un archivo, pero quiero cambiar los nombres de hojas sin tener que abrir los archivos uno por uno. Las Hojas me las renombra como 1, 2, 3, etc….
Sub RenombrarHojasEnOrden()
Dim iX As Integer
For iX = 1 To ActiveWorkbook.Sheets.Count
Sheets(iX).Name = Format(iX, “0”)
Next iX
End Sub
2do) Para mi resumen mensual, tengo un archivo abierto llamado “Libro Resumen Mensual” con una “Hoja1”. Deseo que esta “Hoja1” se copie en todos los archivos mensuales de los directorios mostrados.
3ro) Deseo hacer un libro resumen anual, que se cree a partir de todas las hojas resumen de los archivos mensuales. Osea de todas las “Hoja1” que se copiaron en los archivos mensuales.
Supongo que tendré que crear 3 macros por separados. Si me puedes orientar, ya que soy muy principiante en esto de las macros.
Saludos.