VBA - COPIAR PLANILHAS DE VARIOS ARQUIVOS EXCEL

renaut77

19-09-2018 15:05:12

Olá!
Estou criando um VBA onde eu tenho em um diretório 30 arquivos de Excel onde nele tem uma Plan chamada tabela de Dados, preciso compilar essa tabela de dados dos 30 arquivos em um só, como uma lista de dados um abaixo do outro.
o código abaixo entra no diretorio e lê tudo que está nos arquivos, mas preciso exclusivamente copiar os dados da planilha "tabela de dados". Quando eu tento explicitar que é específico esta tabela ele dá erro.

Agradeço desde já!

Sub lsUnificarPlanilhas()


Dim lUltimaLinhaAtiva As Long
Dim lRng As Range
Dim sPath As String
Dim fName As String
Dim lNomeWB As String
Dim lIPlan As Integer
Dim lUltimaLinhaPlanDestino As Long

PlanilhaDestino = ThisWorkbook.Name
sPath = Localizar_Caminho

sName = Dir(sPath & "\*.xl*")

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Do While sName <> ""
fName = sPath & "\" & sName
Workbooks.Open Filename:=fName, UpdateLinks:=False

lNomeWB = ActiveWorkbook.Name

For lIPlan = 1 To ActiveWorkbook.Sheets.Count




Workbooks(lNomeWB).Worksheets(lIPlan).Activate

lUltimaLinhaAtiva = Cells(Rows.Count, 1).End(xlUp).Row
lUltimaColunaAtiva = ActiveSheet.Cells(1, 5000).End(xlToLeft).Column

Set lRng = Range(Cells(1, lUltimaColunaAtiva).Address)
Range("A" & 1 & ":" & gfLetraColuna(lRng) & lUltimaLinhaAtiva).Select
Selection.Copy

Workbooks(PlanilhaDestino).Worksheets(1).Activate

lUltimaLinhaPlanDestino = Cells(Rows.Count, 1).End(xlUp).Row

If lUltimaLinhaPlanDestino > 1 Then
lUltimaLinhaPlanDestino = Cells(Rows.Count, 1).End(xlUp).Row + 1
End If

Range("A" & lUltimaLinhaPlanDestino).Select

ActiveSheet.Paste
Application.CutCopyMode = False
Next lIPlan

Workbooks(lNomeWB).Close SaveChanges:=False
sName = Dir()
Loop

MsgBox "Planilhas unificadas!"


Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

Function gfLetraColuna(ByVal rng As Range) As String
Dim lTexto() As String

lTexto = Split(rng.Address, "$")

gfLetraColuna = lTexto(1)
End Function

Public Function Localizar_Caminho() As String

Dim strCaminho As String
With Application.FileDialog(msoFileDialogFolderPicker)
'Permitir mais de uma pasta
.AllowMultiSelect = False

'Mostrar janela
.Show

If .SelectedItems.Count > 0 Then
strCaminho = .SelectedItems(1)
End If

End With

'Atribuir caminho a variável
Localizar_Caminho = strCaminho
End Function

renaut77

24-09-2018 11:41:14

Bom dia
Caros dei uma melhorada no código, só que a Tabela de Dados tem apenas 7 linhas para cada arquivo que ele copia, só que eu fiz o teste com 2 arquivos e ele esta retornando a copia dos 2 mas multiplicando por muitas vezes o numero de linhas, deveria ter colado 14 linhas e vieram mais de 300.

Se algum colega puder avaliar. agradeço

Sub lsUnificarPlanilhas()


Dim lUltimaLinhaAtiva As Long
Dim lRng As Range
Dim sPath As String
Dim fName As String
Dim lNomeWB As String
Dim lIPlan As Integer
Dim lUltimaLinhaPlanDestino As Long
Dim A As Worksheets

PlanilhaDestino = ThisWorkbook.Name
sPath = Localizar_Caminho

sName = Dir(sPath & "\*.xl*")

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Do While sName <> ""
fName = sPath & "\" & sName
Workbooks.Open Filename:=fName, UpdateLinks:=False

lNomeWB = ActiveWorkbook.Name

For lIPlan = 1 To ActiveWorkbook.Sheets.Count

Workbooks(lNomeWB).Worksheets("Tabela Dados ").Visible = True
Workbooks(lNomeWB).Worksheets("Tabela Dados ").Activate
ActiveWorkbook.Sheets("Tabela Dados ").Select
lUltimaLinhaAtiva = Cells(Rows.Count, 1).End(xlUp).Row
lUltimaColunaAtiva = ActiveSheet.Cells(1, 5000).End(xlToLeft).Column

Set lRng = Range(Cells(1, lUltimaColunaAtiva).Address)
Range("b5:bk16").Select
Selection.Copy


Workbooks(PlanilhaDestino).Worksheets(1).Activate

lUltimaLinhaPlanDestino = Cells(Rows.Count, 1).End(xlUp).Row

If lUltimaLinhaPlanDestino > 1 Then
lUltimaLinhaPlanDestino = Cells(Rows.Count, 1).End(xlUp).Row + 1
End If

Range("A" & lUltimaLinhaPlanDestino).Select

ActiveSheet.Paste
Application.CutCopyMode = False
Next lIPlan

Workbooks(lNomeWB).Close SaveChanges:=False
sName = Dir()
Loop

MsgBox "Planilhas unificadas!"


Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

Function gfLetraColuna(ByVal rng As Range) As String
Dim lTexto() As String

lTexto = Split(rng.Address, "$")

gfLetraColuna = lTexto(1)
End Function

Public Function Localizar_Caminho() As String

Dim strCaminho As String
With Application.FileDialog(msoFileDialogFolderPicker)
'Permitir mais de uma pasta


'Mostrar janela
.Show

If .SelectedItems.Count > 0 Then
strCaminho = .SelectedItems(1)
End If

End With

'Atribuir caminho a variável
Localizar_Caminho = strCaminho
End Function

Reinaldo

24-09-2018 14:14:58

Fica difícil analisar somente o código, mas a linha: -->For lIPlan = 1 To ActiveWorkbook.Sheets.Count<--
Indica um loop por todas as planilhas do workbook recém aberto, como não há um outro questionamento,
todo a rotina que está entre esse For.. até o Next lIPlan; será executado nVezes quanto forem as planilhas desse workbook; muito provavelmente esse e o motivo da repetição apontada acima.
Por Exemplo, se no Woorkbook houver 7 planilhas o processo de copia será deflagrado 7 vezes, serão copiados 98 linhas e não apenas 14.
Tendo Certeza que em cada arquivo desses há uma planilha -->Worksheets("Tabela Dados ")<-- exclua/comente essas duas linhas
Não entendi o trecho: determina dinamicamente a range (lRng) e depois copia uma range especifica/diferente
[code[ Set lRng = Range(Cells(1, lUltimaColunaAtiva).Address)
Range("b5:bk16").Select
Selection.Copy[/code]
Dentro do que entendi experimente sua rotina conforme abaixo:
Obs.: Se não atender a demanda, por favor poste seu arquivo base e fonte (dados fictícios porem que represente sua realidade), demonstrando manualmente oque deseja

Código: Selecionar todos

Sub lsUnificarPlanilhas()
Dim lUltimaLinhaAtiva As Long
Dim lRng As Range
Dim sPath As String
Dim fName As String
Dim lNomeWB As String
Dim lIPlan As Integer
Dim lUltimaLinhaPlanDestino As Long
Dim A As Worksheets

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

PlanilhaDestino = ThisWorkbook.Name
sPath = Localizar_Caminho

sName = Dir(sPath & "\*.xl*") '

Do While sName <> ""
    fName = sPath & "\" & sName
    Workbooks.Open Filename:=fName, UpdateLinks:=False
    
    lNomeWB = ActiveWorkbook.Name
    
    With Workbooks(lNomeWB).Worksheets("Tabela Dados ")
        'Determina ultima linha e coluna do arquivo Fonte (recem aberto)
        lUltimaLinhaAtiva = .Cells(Rows.Count, 1).End(xlUp).Row
        lUltimaColunaAtiva = .Cells(1, 5000).End(xlToLeft).Column
        
        'Determina ultima linha do arquivo destino
        lUltimaLinhaPlanDestino = Workbooks(PlanilhaDestino).Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
        If lUltimaLinhaPlanDestino > 1 Then lUltimaLinhaPlanDestino = Cells(Rows.Count, 1).End(xlUp).Row + 1
    
        Set lRng = Range(Cells(1, lUltimaColunaAtiva).Address)
        lRng.Copy Destination:=Workbooks(PlanilhaDestino).Worksheets(1).Range("A" & lUltimaLinhaPlanDestino)
        'Fecha o arqivo Fonte
     End With
    Workbooks(lNomeWB).Close SaveChanges:=False
    sName = Dir()
Loop

MsgBox "Planilhas unificadas!"

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

renaut77

24-09-2018 16:05:46

Olá boa tarde!
Agradeço pelo retorno e fiz o teste mas o resultado veio vazio na planilha base.

Veja que tenho 2 arquivos no diretorio "Base do Teste" onde tenho a plan "Tabela dados ".
Preciso copiar as informações desta planilha apartir da linha 5 e colar no arquivo Macro VBA abaixo do cabeçalho. Em seguida será feita a copia do arquivo 2 onde tambem tem a plan "Tabela Dados " e assim até finalizar pela quantidade de arquivos no diretorio.

webmaster

24-09-2018 16:16:04

Para união de planilhas, veja se isso ajuda:

https://www.youtube.com/watch?v=yhzc6Rt9AbQ

Reinaldo

24-09-2018 16:56:13

veja se é assim:
Macro VBA.zip
Obs.: A estrutura do arquivo 1.xlsx e 2.xlsx são diferentes coluna A de 1.xlsx e data e coluna A de 2.xlsx e texto


Cross Post https://gurudoexcel.com/forum/viewtopic.php?f=12&t=7846

renaut77

24-09-2018 18:40:51

boa tarde!
obrigado, mas veja no arquivo o que retornou, como eu tenho outras abas no arquivo, especificamente ele tem que entrar na aba Tabela Dados e copiar.

Reinaldo

24-09-2018 18:59:57

Podia jurar que tinha que o arquivo tinha a rotina mais atual, abaixo a ultima versão; porem é necessario que todos os arquivos "doadores" tenham a mesma estrutura, o que não ocorreu com seus 2 exemplos.

Código: Selecionar todos

Sub lsUnificarPlanilhas()
Dim lUltimaLinhaAtiva As Long
Dim lRng As Range
Dim sPath As String
Dim fName As String
Dim lNomeWB As String
Dim lIPlan As Integer
Dim lUltimaLinhaPlanDestino As Long
Dim A As Worksheets

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

PlanilhaDestino = ThisWorkbook.Name
sPath = Localizar_Caminho

sName = Dir(sPath & "\*.xl*") '

Do While sName <> ""
    fName = sPath & "\" & sName
    Workbooks.Open Filename:=fName, UpdateLinks:=False
    
    'lNomeWB = ActiveWorkbook.Name
    
    With Workbooks(sName).Worksheets("Tabela Dados ")
        'Determina ultima linha e coluna do arquivo Fonte (recem aberto)
        lUltimaLinhaAtiva = .Cells(Rows.Count, 1).End(xlUp).Row
        lUltimaColunaAtiva = .Cells(4, 5000).End(xlToLeft).Column
        'Determina ultima linha do arquivo destino
        lUltimaLinhaPlanDestino = Workbooks(PlanilhaDestino).Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
        If lUltimaLinhaPlanDestino > 1 Then lUltimaLinhaPlanDestino = lUltimaLinhaPlanDestino + 1
    
        Set lRng = Range(Cells(5, 1).Address & ":" & Cells(lUltimaLinhaAtiva, lUltimaColunaAtiva).Address)
        lRng.Copy Destination:=Workbooks(PlanilhaDestino).Worksheets(1).Range("A" & lUltimaLinhaPlanDestino)
        'Fecha o arqivo Fonte
     End With
    Workbooks(sName).Close SaveChanges:=False
    sName = Dir()
Loop

Application.ScreenUpdating = True
Application.EnableEvents = True

MsgBox "Planilhas unificadas!"

Application.Calculation = xlCalculationAutomatic
End Sub
Seu modelo "receptor" anterior e diferente do modelo que disponibilizado por ultimo. Qual e o valido?
O conteúdo desse modelo reflete o conteudo/estrutura do exemplo anterior nomeado planilha 2.xlsm
A rotina busca e copia os dados sempre da planilha "Tabela Dados", se não houver essa planilha vai dar erro.

Veja o que retornou utilzando os modelos corrigidos postado no forum Guru
Macro VBA(Ul).zip

renaut77

25-09-2018 11:49:44

Bom dia!
Caro ainda nao funcionou bem porque tive que colocar essa parte de codigo abaixo especificando que eu quero a tabela de dados apartir da linha 5, mas ainda assim ele esta puxando o cabeçalho. só que a macro esta puxando tambem os dados da linha 4.

Workbooks(lNomeWB).Worksheets("Tabela Dados ").Visible = True
Workbooks(lNomeWB).Worksheets("Tabela Dados ").Activate

Range("b5:bk16").Select
Selection.Copy


Sub lsUnificarPlanilhas()
Dim lUltimaLinhaAtiva As Long
Dim lRng As Range
Dim sPath As String
Dim fName As String
Dim lNomeWB As String
Dim lIPlan As Integer
Dim lUltimaLinhaPlanDestino As Long
Dim A As Worksheets

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

PlanilhaDestino = ThisWorkbook.Name
sPath = Localizar_Caminho

sName = Dir(sPath & "\*.xl*") '

Do While sName <> ""
fName = sPath & "\" & sName
Workbooks.Open Filename:=fName, UpdateLinks:=False

lNomeWB = ActiveWorkbook.Name

Workbooks(lNomeWB).Worksheets("Tabela Dados ").Visible = True
Workbooks(lNomeWB).Worksheets("Tabela Dados ").Activate

Range("b5:bk16").Select
Selection.Copy


With Workbooks(sName).Worksheets("Tabela Dados ")
'Determina ultima linha e coluna do arquivo Fonte (recem aberto)
lUltimaLinhaAtiva = .Cells(Rows.Count, 1).End(xlUp).Row
lUltimaColunaAtiva = .Cells(4, 5000).End(xlToLeft).Column
strcel1 = Cells(1, 1).Address
strcel2 = Cells(lUltimaLinhaAtiva, lUltimaColunaAtiva).Address
'Determina ultima linha do arquivo destino
lUltimaLinhaPlanDestino = Workbooks(PlanilhaDestino).Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
If lUltimaLinhaPlanDestino > 1 Then lUltimaLinhaPlanDestino = Cells(Rows.Count, 1).End(xlUp).Row + 1

Set lRng = Range(Cells(1, 1).Address & ":" & Cells(lUltimaLinhaAtiva, lUltimaColunaAtiva).Address)
lRng.Copy Destination:=Workbooks(PlanilhaDestino).Worksheets(1).Range("A" & lUltimaLinhaPlanDestino)
'Fecha o arqivo Fonte
End With
Workbooks(sName).Close SaveChanges:=False
sName = Dir()
Loop

MsgBox "Planilhas unificadas!"

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

Public Function Localizar_Caminho() As String

Dim strCaminho As String
With Application.FileDialog(msoFileDialogFolderPicker)
'Permitir mais de uma pasta
.AllowMultiSelect = False

'Mostrar janela
.Show

If .SelectedItems.Count > 0 Then
strCaminho = .SelectedItems(1)
End If

End With

'Atribuir caminho a variável
Localizar_Caminho = strCaminho
End Function

Reinaldo

25-09-2018 13:16:07

Seu modelo real provavelmente não bate com o que disponibilizou. E pelo código que expôs no post acima(de maneira incorreto por sinal, código deve ser adicionado utilizando a ferramente Code, o quinto botão logo acima de FontMenu), não é o ultimo que enviei. compare o seu acima com o que esta no post imediatamente anterior.
Não há necessidade de "deixar" a sheet visível e nem de seleciona-la se utilizar os comandos corretamente.
Espero que o colega GFranco do Forum Guru tenha acertado na proposta que foi feita.

renaut77

25-09-2018 15:57:32

Agradeço pelas considerações caro Reinaldo,mas não funcionou. a base da da tabela Dados é a mesma em todos os arquivos, o que difere é que tem outras abas que fazem parte dos documentos que nao te enviei. Independente disto a macro deveria ir só na tabela dados e buscar as informações.
Mas veja no anexo que ele foi na primeira aba ativa de cada documento e me deu informações erroneas. Se nao há necessidade de deixá-la visivel como proceder para ele buscar as informações especificamente nela? se o VBA esta indicando que deve procurar as informações na tabela dados concorda?

Reinaldo

25-09-2018 17:47:03

Experimente: