Problema con Excel que consolida datos de otros libros con VBA

Tengo una macro que copia datos de diferentes libros, y las pega en un archivo línea por línea. Básicamente funciona así:

1. Archivo "Capturador", se corre la macro. Inicia preguntando la ruta de los archivos, que puede ser la carpeta en donde se encuentra el mismo archivo capturador o una diferente seleccionada por el usuario. La dirección se guarda en una celda.

2. El usuario especifica si quiere borrar los datos que ya están allí, o si prefiere continuar listando los datos desde la última fila disponible. En el primer caso, se borra el contenido de la celda A3 hasta la celda V5000 (mis títulos están en A1 y A2).

3. Mediante un ciclo Do While, abre cada archivo <> "Capturador", y copia los datos de la página "Resumen" del rango A2:U2. Regresa al archivo Capturador y lo pega en la siguiente línea a la última utilizada. Además, pega en la columna "V" el nombre del archivo de donde salió la información.

4. Si ya no hay más archivos, termina el ciclo y la macro.

Tengo 2 problemas con esto:

A. Me está copiando en la columna "V", siempre en la fila 2, mi nombre "Capturador". Esto hace que toda la información de dicha columna se corra una fila. Por ejemplo, copia datos de 4 archivos y lo hace bien, pero en la columna V, la fila 1 está bien con su nombre correspondiente, pero la fila 2 dice "Capturador". De la fila 3 en adelante está el nombre que debió de corresponder a la fila 2; en la fila 4 en la que corresponde al 3, etc. Todos mis registros en esa columna se corren 1 debido a esto.

B. No estoy muy familiarizado con los ErrorHandler, y no sé si se pueda lo siguiente: Si un archivo no tiene la hoja "Resumen", no puede abrirse, está protegido, etc. Que pegue en la columna V el nombre del archivo que tiene inconvenientes, y una observación en su fila (Por ejemplo, "archivo con error"). De esa forma, ya se vería manualmente cuál es el problema..

Mi código es el siguiente, por si sirve:

Option Explicit

Sub Copia_rangos_Libros()

On Error Resume Next
'***** CONSOLIDA *****
'Copia determinado rango de "Resumen" de cada archivo que exista en la carpeta

'Definición de tipos de variables:
Dim Ruta As String, archivo As String, aviso As String
Dim w As Object
Dim cantidad As Long
Dim nombreprincipal As String

Ruta = Range("AA1").Value 'Variable que encuentra la ruta del archivo.
ChDir Ruta 'Cambia el directorio a la ruta
archivo = Dir("*.xls*") 'Variable para archivos que contengan xls (*es comodín, sirve para xls,xlsx,xlsm)
Set w = ThisWorkbook.Sheets("Base") 'Variable w para fijar libro y hoja de trabajo (Libro actual)
nombreprincipal = ThisWorkbook.Name

Application.ScreenUpdating = False 'Desactiva centelleo pantalla.

'w.[A3:v50000].Clear 'Borra el rango dado de hoja1 en libro Base. Reemplazar.show

Do While archivo <> "" 'Inicia bucle p/todos los archivos
If InStr(1, archivo, nombreprincipal) = 0 Then 'Para todos los archivos distintos de nuestro consolidado
Workbooks.Open archivo 'Abre el 1er. Archivo a copiar
cantidad = 1 'Variable p/obtener cantidad de registros a copiar
Sheets("Resumen").Range("a2:u2").Copy

w.Range("a" & w.[a65000].End(xlUp).Row + 1).PasteSpecial xlPasteValuesAndNumberFormats 'Encuentra fila vacía en A y Pega datos ***VER NOTA***
w.Range("v" & w.[v65000].End(xlUp).Row + 1) = Workbooks(archivo).Name 'Pega nombre de archivo en 1 celda de v
Application.CutCopyMode = False

Application.DisplayAlerts = False 'Desactiva las alertas
Workbooks(archivo).Close 'Cierra el libro copiado
Application.DisplayAlerts = True 'Activa las alertas

w.[v65000].End(xlUp).Activate 'Se posiciona en última celda ocupada en V
Selection. Copy Selection. Resize(cantidad) 'Copia y pega nombre de archivo de origen en todas las celdas de V
End If 'Cierre del If

archivo = Dir() 'Selecciona el siguiente archivo

Loop 'Repite ciclo hasta terminar con todos los archivos que existan en la carpeta

Application.ScreenUpdating = True 'Activa centelleo pantalla

End Sub

1 respuesta

Respuesta
2

Te anexo la macro actualizada. Verifica que se pueda abrir el archivo. También valida que existe la hoja "resumen".

Para cada archivo con problemas, te pondrá un registro con error en la columna A y el nombre del archivo en la columna V

Option Explicit
Sub Copia_rangos_Libros()
'Act.Por.Dante Amor
    'On Error Resume Next
    '***** CONSOLIDA *****
    'Copia determinado rango de "Resumen" de cada archivo que exista en la carpeta
    'Definición de tipos de variables:
    Dim Ruta As String, archivo As String, aviso As String
    Dim w As Object
    Dim cantidad As Long
    Dim nombreprincipal As String
    Dim existe As String
    Dim h As Object
    Dim u As Integer
    Dim werr As Variant
    '
    Ruta = Range("AA1").Value 'Variable que encuentra la ruta del archivo.
    ChDir Ruta 'Cambia el directorio a la ruta
    archivo = Dir("*.xls*") 'Variable para archivos que contengan xls (*es comodín, sirve para xls,xlsx,xlsm)
    Set w = ThisWorkbook.Sheets("Base") 'Variable w para fijar libro y hoja de trabajo (Libro actual)
    nombreprincipal = ThisWorkbook.Name
    Application.ScreenUpdating = False 'Desactiva centelleo pantalla.
    Application.DisplayAlerts = False 'Desactiva las alertas
    'w.[A3:v50000].Clear 'Borra el rango dado de hoja1 en libro Base. Reemplazar.show
    Do While archivo <> "" 'Inicia bucle p/todos los archivos
        If InStr(1, archivo, nombreprincipal) = 0 Then 'Para todos los archivos distintos de nuestro consolidado
            On Error Resume Next
            Workbooks.Open archivo 'Abre el 1er. Archivo a copiar
            werr = Err.Number
            On Error GoTo 0
            If werr = 13 Or werr = 0 Then
                existe = False
                For Each h In ActiveWorkbook.Sheets
                    If UCase(h.Name) = "RESUMEN" Then
                        existe = True
                        Exit For
                    End If
                Next
                If existe Then
                    Sheets("Resumen").Range("a2:u2").Copy
                    u = w.[a65000].End(xlUp).Row + 1
                    w.Range("a" & u).PasteSpecial xlPasteValuesAndNumberFormats 'Encuentra fila vacía en A y Pega datos ***VER NOTA***
                    w.Range("v" & u) = Workbooks(archivo).Name 'Pega nombre de archivo en 1 celda de v
                Else
                    u = w.[a65000].End(xlUp).Row + 1
                    w.Range("a" & u) = "Archivo no contiene la hoja Resumen"
                    w.Range("v" & u) = Workbooks(archivo).Name 'Pega nombre de archivo en 1 celda de v
                End If
                Workbooks(archivo).Close False 'Cierra el libro copiado
            Else
                u = w.[a65000].End(xlUp).Row + 1
                w.Range("a" & u) = "No se puede abrir el archivo"
                w.Range("v" & u) = archivo 'Pega nombre de archivo en 1 celda de v
            End If
        End If 'Cierre del If
        archivo = Dir() 'Selecciona el siguiente archivo
    Loop 'Repite ciclo hasta terminar con todos los archivos que existan en la carpeta
    Application.ScreenUpdating = True 'Activa centelleo pantalla
    Application.DisplayAlerts = True 'Activa las alertas
    Application.CutCopyMode = False
    MsgBox "Proceso terminado"
End Sub
'S aludos. Dante Amor. Recuerda valorar la respuesta. G racias

Añade tu respuesta

Haz clic para o

Más respuestas relacionadas