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.
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!