0181-Dica VBA: Usando a função Dir para consolidar arquivos

A função Dir retorna informações sobre uma pasta ou sobre os arquivos nela contidos.

A linha de comando a seguir, retorna o nome do primeiro arquivo do Excel da pasta C:\Blog

stArquivo = Dir("C:\Blog\*.xl*")

Quando utilizado sem argumentos, Dir fornece o nome do próximo arquivo da lista.

Por isso utilizamos Dir quando precisamos percorrer uma lista de arquivos de um diretório, para, por exemplo, consolidar uma série de arquivos numa única planilha.

No exemplo a seguir serão consolidadas as informações de todos os arquivos do Excel localizados na pasta C:\Blog. Em cada um deles serão extraídas as informações das colunas de A até E, até a última linha preenchida, sempre na planilha chamada “Dados”.

Sub ConsolidarArquivos()
'Declarar variáveis
Dim stNome      As String
Dim stPasta     As String
Dim stArq       As String
Dim wsConsol    As Worksheet
Dim wsArq       As Worksheet
Dim ulConsol    As Long
Dim ulArq       As Long
 
'Congelar a atualização de tela
Application.ScreenUpdating = False

'Determinar a planilha de consolidação dos dados
Set wsConsol = Worksheets("Consol")
 
'Definir a pasta que será pesquisada
stPasta = "C:\Blog\"
 
'Retornar o nome do primeiro arquivo localizado
stArq = Dir(stPasta & "*.xl*")
 
'Se o primeiro nome for o do arquivo que contém a macro
'segue-se para o próximo arquivo
If stArq = ThisWorkbook.Name Then stArq = Dir()


'Loop para percorrer todos os arquivos
Do Until stArq = ""
    
    'Evitar a tentativa de abrir o arquivo que contém a macro
    'Esse teste é importante quando os arquivos a serem consolidados
    'estão na mesma pasta que este arquivo
    If stArq  ThisWorkbook.Name Then
    
    
        'Determinar a última linha que contém valores na coluna A em wsConsol
        ulConsol = wsConsol.Cells(Rows.Count, "A").End(xlUp).Row
         
        'Definir o nome completo do arquivo
        stArq = stPasta & stArq
        
        'Abrir o workbook
        Workbooks.Open Filename:=stArq
         
        'Definir a planilha cujos dados serão copiados
        Set wsArq = ActiveWorkbook.Worksheets("Dados")
        
        'Determinar a última linha que contém valores na coluna A em wsArq
        ulArq = ActiveWorkbook.ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
         
        'Copiar dados de wsArq e colá-los em wsConsol
        wsArq.Range("A2:E" & ulArq).Copy wsConsol.Range("A" & ulConsol).Offset(1, 0)
         
        'Fechar o arquivo cujas informações foram copiadas sem salvar alterações
        ActiveWorkbook.Close SaveChanges:=False
         
        'Redefinir a variável stArq para buscar o nome do próximo arquivo a ser aberto
        'Se o valor retornado for uma String vazia, o loop será interrompido com
        'base na condição Until
        stArq = Dir()
    End If
Loop

'Salvar o arquivo que contém a macro
ThisWorkbook.Save
  
'Permitir a atualização de tela
Application.ScreenUpdating = True

End Sub

O arquivo que contém a macro e os arquivos exemplo para realizar a consolidação estão disponíveis em 0181-Exemplo de Consolidacao. Ao descomprimir os arquivos, salve-os numa pasta chamada “C:\BLOG” ou edite o código da macro para adequá-lo à pasta na qual os arquivos foram salvos.

Sobre adilsonsoledade

In Excel We Trust

‹ Estatísticas do Blog – Junho 2013

Desafio 0001 ›

Marcado com: Excel, Macros, Planilha, Spreadsheet, VBA
Publicado em Nível: Avançado