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