Macro para modificar archivos de Excel sin abrirlos

Modificar varios archivos de Excel sin abrirlos usando Macros VBA

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.

lista

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

You may also like...

2 Responses

  1. mario vergara says:

    Gracias!

  2. SebaXX says:

    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.

Leave a Reply

Your email address will not be published.

%d bloggers like this: