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.
In Excel We Trust
Excelente planilha! Parabéns!
Qual a adaptação necessária na macro quando os arquivos excel possuirem mais de uma planilha (várias abas).
o código atual me retorna sempre a planilha que está “ativa” (por exemplo: planilhas “Dados” e “Informações” – se a planilha ativa for “Informações” o código atual retorna os dados da planilha “Informações” em vez da Planilha “Dados” que está nomeada no Código.
Show!