Copiar em VBA o valor de multiplos arquivos [EXCEL]

[andre_luis]

Pessoal,


Estou começando a mexer com o VBA porque surgiu uma necessidade de automatizar a extração de valores arquivados em várias planilhas. De inicio comecei com o PROCV apontando para esses arquivos, mas como todas essas planilhas teriam necessariamente de estar previamente abertas, tive de começar a fazer isso em VBA, mas estou com um problema no código a seguir, que não estou conseguindo resolver.

Esses são os arquivos que estou usando:

Origem

• Arquivos : Extracao_mensal_201003. Xlsx ... Extracao_mensal_201510. Xlsx ( de MAR/2010 até OUT/2015 )
• Folha : Plan3
• Celula : B14

Destino

• Arquivo: Planilha atual
• Celula : C3

O problema é que o programa está lendo apenas o primeiro arquivo apontado no teste abaixo ( Extracao_mensal_201404. Xlsx ) e mesmo se eu tentar alterar o contador abaixo de 4..7 para 5..7 ele não aceita, dando erro. Já conferi o nome do arquivo se textualmente está coerente, assim como o conteúdo das suas células, mas nada justifica esse erro sem sentido.

Alguém teria alguma luz ?

vbnet code

Sub ImportarDadosSemAbrir()
    
    Dim ExtensionOrigin As String
    Dim CaminhoOrigem As String
    Dim ArquivoOrigem As String
    Dim PastaOrigem As String
    Dim RangeOrigem As String
    Dim AnoOrigem As String
    Dim MesOrigem As String
    
    Dim ExtensionDestino As String
    Dim CaminhoDestino As String
    Dim ArquivoDestino As String
    Dim PastaDestino As String
    Dim RangeDestino As String
    
    Dim i As Integer
    
    AnoOrigem = "2014"
    MesOrigem = "04"
            
    ExtensionOrigin = ".xlsx"
    CaminhoOrigem = "C:\Andre\CR\"
        
    PastaOrigem = "Plan3"
    RangeOrigem = "$b$14:$b$14"
        
    CaminhoDestino = CaminhoOrigem
    PastaDestino = "Plan1"
    
    'Application.ScreenUpdating = False
    
    For i = 4 To 7
        ArquivoOrigem = "Extracao_mensal_" & AnoOrigem & "0" & CStr(i) & ExtensionOrigin
        Workbooks.Open Filename:=CaminhoOrigem & ArquivoOrigem, ReadOnly:=True
        ThisWorkbook.Activate
        
        ThisWorkbook.Names.Add "Valor", _
        RefersTo:="='" & CaminhoOrigem & "[" & ArquivoOrigem & "]" & PastaOrigem & "'!" & RangeOrigem
    
        With Sheets(PastaDestino)
                .[c3:c3] = "=Valor" ' Range("c3").Text
                .[c3:c3].Copy
            Sheets(PastaOrigem).Range(RangeOrigem).PasteSpecial xlPasteValues
        End With
        
        ThisWorkbook.Save
        ThisWorkbook.Close SaveChanges:=False
        'ActiveWorkbook.Save
        'ActiveWorkbook.Close
        'Application.Quit
        'Application.ScreenUpdating = True
        
    Next i

End Sub

[barboza]

1 - Tenho dúvidas se precisa realmente estar com o s arquivos origem aberto para copiarem os dados para o destino;

2 - Irá copiar múltiplos dados (março...outubro) para a mesma célula c3 do arquivo de destino?

Já fiz algo semelhante, copiando entre folhas e concatenando o nome de origem para o destino.

[andre_luis]

Irá copiar múltiplos dados (março...outubro) para a mesma célula c3 do arquivo de destino?

Realmente está acontecendo isso sim, mas depois eu vou distribuir em células diferentes.
O problema é que ainda nesse estágio embrionário do programa, já está com esse problema.

É impressão minha ou a ferramenta fornece poucos recursos para debugar ?
Os erros de compilação que aparecem explicam muito pouco sobre o motivo.

[barboza]

Fiz uns testes aqui e até consegui ler os dados de uma planilha em outra de forma dinâmica, concatenando o nome inicial com o ano e mês, gerando assim o nome final da planilha e depois adicionando a folha e célula. Depois usando a função "indireto()" recuperei os valores, mas ainda sim preciso dos arquivos abertos.

[andre_luis]

Poisé...mas o problema acima aconteceu contigo também ?

[barboza]

Poisé...mas o problema acima aconteceu contigo também ?

Você diz copiar todos para a mesma célula? Este não.

Único problema é ter que estar com os arquivos origem aberto.

[andre_luis]

Ahh...ok, achei que voce tivesse rodado o programa acima.
Burrice minha, de qualquer modo voce não teria mesmo os arquivos pra testar.

O problema não está em onde está sendo gravado, mas sim de onde está sendo copiado.
A gravação na célula C3 é só o teste inicial, depois vou distribuir cada arquivo sequenciamente em cada linha.
Por enquanto, está pegando apenas o valor do primeiro arquivo (mês 4), mas como há um loop, eu penso que deveria apresentar o último valor (mês 7) em C3.

Mas beleza, vou continuar testando essa joça, e procurando outras opões na internet.


Valew.

[barboza]

Você realmente precisa ler os dados com os arquivos fechados?

Precisa ser em VBA? Porque não direto no excel?

[andre_luis]

O problema é que eu já havia tentado no Excel, mas pra cada arquivo apontado pelo PROCV eu preciso indicar manualmente via uma janela do Windows Explorer que abra na ocasião da edição. Como são uns 5 anos de planilhas mensais, eu teria de fazer isso ao menos 60 vezes. Sei que se eu insistir no Excel posso até achar uma solução lá, mas eu queria explorar um pouco mais dos recursos do VBA, mas até o momento continuo patinando nesse problema que parece trivial, mas não estou enxergando o que estou fazendo de errado.

[KrafT]

Sei lá... Não rola fazer um aplicativo no Visual Sudio? Deve haver uma boa interação entre o VS e o Office. Não que os scripts sejam ruins, mas me passam a impressão de um montanha russa: Você sabe o ponto de partida e o de chegada, mas o percurso não é esperado ser tranquilo.

[andre_luis]

Esse script deve rodar no meu PC do trabalho que não aceita instalar nada, já que não tenho perfil de Administrador, e qualquer executável tem de passar por um bloqueio do antivirus que detecta coisas não homologadas por eles, e se eu redistribuir isso pra galera das filiais, vai ser a mesma história...enfim, seria trabalho demais. Não deveria ser coisa de outro mundo fazer o que tou tentando, mas tá difícil fazer funcionar esse codigo mixuruca aí.

[Djalma Toledo Rodrigues]

VBA?
Porque não preservar o Formato Planilha ?

Dj.

[andre_luis]

VBA?
Porque não preservar o Formato Planilha ?

Nao entendi.
Estou justamente pretendendo copiar os valores de varias planilhas para a planilha que estou usando.

[andre_luis]

Bom, consegui resolver o problema de não estar aceitando abrir nenhum outro arquivo que não o primeiro da lista.
O problema é que os demais estavam com alguma restrição de acesso, e após copiar/colar o primeiro nos demais funcionou.

Porém, presiste o problema de estar apenas apresentando o valor do primeiro arquivo.
Embora agora abra os demais, apenas fica registrado na Célula C3 o falor do arquivo do mês 04, apesar de abrir até o 07.

Segue o código refeito, com pequenas modificações.
Se alguem puder dar uma olhada, e um palpite...

vbnet code

Sub ImportarDadosSemAbrir()
    
    Dim ExtensionOrigin As String
    Dim PathOrigin As String
    Dim FileOrigin As String
    Dim FolderOrigin As String
    Dim RangeOrigin As String
    Dim YearOrigin As String
    Dim MonthOrigin As String
    
    Dim ExtensionDestination As String
    Dim PathDestination As String
    Dim FileDestination As String
    Dim FolderDestination As String
    Dim RangeDestination As String
    
    Dim IndexFile As Integer
    Dim iRow As Integer
    
    iRow = 1
    iColumn = 1
    
    YearOrigin = "2014"
    MonthOrigin = "04"
            
    ExtensionOrigin = ".xlsx"
    PathOrigin = "C:\Andre\CR\"
        
    FolderOrigin = "Plan3"
    RangeOrigin = "$b$14:$b$14"
        
    PathDestination = PathOrigin
    FolderDestination = "Plan1"
    
    Application.ScreenUpdating = False
    
    For IndexFile = 4 To 7
        FileOrigin = "R_" & YearOrigin & "0" & CStr(IndexFile) & ExtensionOrigin
        Workbooks.Open Filename:=PathOrigin & FileOrigin, ReadOnly:=True
        ThisWorkbook.Activate
        
        ThisWorkbook.Names.Add "Value", _
        RefersTo:="='" & PathOrigin & "[" & FileOrigin & "]" & FolderOrigin & "'!" & RangeOrigin
  
        With Sheets(FolderDestination)
                .Cells(iRow, iColumn) = "=Value"
                .Cells(iRow, iColumn).Copy
            Sheets(FolderOrigin).Range(RangeOrigin).PasteSpecial xlPasteValues
                '.[b1:F14].Clear
        End With
        
        iColumn = iColumn + 1
        
        ThisWorkbook.Save
        ThisWorkbook.Close SaveChanges:=True
        ActiveWorkbook.Save
        ActiveWorkbook.Close
        Application.Quit
        Application.ScreenUpdating = True
        
    Next IndexFile

End Sub

[Djalma Toledo Rodrigues]

VBA?
Porque não preservar o Formato Planilha ?

Nao entendi.
Estou justamente pretendendo copiar os valores de varias planilhas para a planilha que estou usando.

VBA me parece um retrocesso, para que perder as "virtudes" da Planilha?

Abra a Primeira Planilha, posicione o Cursor após a última linha e adiciona a próxima Planilha
mas, atenção O Cursor desta deverá estar na Primeira Célula.

Ou se preferir lado a lado, na coluna adjacente - o mesmo proceder.

Já fiz isso muitas vezes, tempo da Aviação

Dj.