Leitura automática de dados de arquivos Excel separados

Question

Digamos que eu tenha uma pasta de arquivos do Excel com nomes de arquivos previsíveis e formatação. Como posso ler automaticamente de todos eles em um arquivo Excel separado? Congratulo-me com respostas VBA, mas apenas as completas o suficiente para implementar sem experiência. Estou ciente de que a versão pode dar alguma diferença no comportamento, estou pessoalmente trabalhando com o Excel 2007.

Exemplo detalhado

Para desenhar uma imagem detalhada, imagine que eu tenha arquivos nomeados da seguinte maneira, continuando até um número conhecido.

• 1.xls
• 2.xls
• 3.xls
• 4.xls

Para simplificar, considere que todos eles têm uma planilha com o nome Sheet1 e apenas um valor inteiro em A1. Nesta mesma pasta, gostaria de ter um arquivo do Excel que tenha uma coluna com o inteiro de cada um desses arquivos. Só para ter certeza de que minha solicitação é 100% clara, quero um arquivo com esta aparência:

EmqueacolunaValorfoiobtidaautomaticamentedosrespectivosarquivos.

Oquenãofunciona

Euchamareiissode"solução de link manual". Esta solução é insuficiente porque requer digitação para todas as células. Se eu tiver 1000 arquivos de um site do governo, isso não é uma opção prática.

• Na foto acima, vá para a célula B2
• Tipo = 1.xls! a1
• Pressione Enter
• Repita logicamente para as outras 3 linhas

Eu chamarei este próximo de "falha indireta". Ele funciona, mas somente se o outro arquivo estiver aberto no momento em que os valores forem calculados. Novamente, isso também é impraticável se abrir 1000 arquivos do Excel pode travar o computador, sem mencionar ter que fechar muitos arquivos.

• Na foto acima, acesse cell B2
• Digite =INDIRECT(A2&"!A1")
• Pressione Enter

Parece que essa abordagem me coloca tentadoramente perto de uma solução, mas eu não sei bem como fazê-lo fazer o que eu quero. Ah, eu também preciso especificar que usar o nome completo do arquivo também não resolve o problema, o que significa que digitar ='C:\[1.xls]Sheet1'!A1 tem o mesmo problema que os arquivos devem estar abertos para que ele funcione (supondo que 1.xls esteja armazenado em o nível superior da unidade C).

Links que não corrigem bem

Você pode encontrar outras tentativas para esse problema na Internet em geral. Um , por exemplo, envolve planilhas (não arquivos), é opaco no que faz e não • Descrever totalmente como implementar a solução.

A resposta mais próxima que encontrei no Super User foi Posso usar o SQL para criar uma tabela de dados do Excel a partir de outros arquivos do Excel? Isso não satisfaz o que estou procurando, porque eu particularmente não quero uma solução SQL, e Eu quero algo tão simples quanto possível. É também por isso que tentei oferecer as minhas soluções falhadas, por isso não vamos dar passos largos sobre as meias soluções que já vi.

Esta é a minha primeira pergunta sobre Superusuário e eu acredita ser o lugar certo para fazer essas perguntas sobre o Excel porque minha pergunta usa -oriented.

    

Answer 1

Desculpe se pedi muitos esclarecimentos. Estou acostumada a responder uma pergunta e, em seguida, sou informada de que eles queriam perguntar algo diferente.

Tente isto:

Option Explicit
Sub ReadFilesInSequence()

  Dim FileName As String
  Dim FileNumber As Long
  Dim PathCrnt As String
  Dim RowDestCrnt As Long
  Dim SheetDest As String
  Dim TgtValue As String
  Dim WBookSrc As Workbook

  PathCrnt = ActiveWorkbook.Path & "\AlanSE"

  SheetDest = "AlanSE"
  RowDestCrnt = 1

  With Worksheets(SheetDest)
    ' Delete current contents of destination sheet
    .Cells.EntireRow.Delete
  End With

  FileNumber = 1

  Do While True

    FileName = Dir$(PathCrnt & "\" & FileNumber & ".xls")
    If FileName = "" Then
      ' File does not exist
      Exit Do
    End If

    Set WBookSrc = Workbooks.Open(PathCrnt & "\" & FileName)
    With WBookSrc.Worksheets("Sheet1")
      TgtValue = .Cells(1, "A").Value
    End With
    WBookSrc.Close SaveChanges:=False
    With Worksheets(SheetDest)
      .Cells(RowDestCrnt, "A").Value = FileName
      .Cells(RowDestCrnt, "B").Value = TgtValue
    End With
    RowDestCrnt = RowDestCrnt + 1

    FileNumber = FileNumber + 1

  Loop

End Sub

Espero ter adicionado explicações suficientes. Pergunte se você precisa de mais.

    

Answer 2

dê uma olhada aqui: link Eu usei esses (e também exemplos de CPAN) como ponto de partida para minhas aventuras no Excel. Eu criei 3 pastas de trabalho do Excel contendo um número em 0,0 na planilha1. Usando o trecho de código do site da IBM (menos algumas instruções de impressão, recebo:

shiny:exceltest fl$ for f in $( find . -name book\*.xls ); do ../parse-excel.pl $f ; done
--------- SHEET:sheet1
( 0 , 0 ) =>3.1416
--------- SHEET:sheet1
( 0 , 0 ) =>678
--------- SHEET:sheet1
( 0 , 0 ) =>42

para isso, eu usei esse snippet de código perl, roubado descaradamente do site developerworks:

#!/opt/local/bin/perl -w

use strict;
use Spreadsheet::ParseExcel;

my $oExcel = new Spreadsheet::ParseExcel;

die "You must provide a filename to $0 to be parsed as an Excel file" unless @ARGV;

my $oBook = $oExcel->Parse($ARGV[0]);
my($iR, $iC, $oWkS, $oWkC);

for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++)
{
 $oWkS = $oBook->{Worksheet}[$iSheet];
 print "--------- SHEET:", $oWkS->{Name}, "\n";
 for(my $iR = $oWkS->{MinRow} ;
     defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ;
     $iR++)
 {
  for(my $iC = $oWkS->{MinCol} ;
      defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ;
      $iC++)
  {
   $oWkC = $oWkS->{Cells}[$iR][$iC];
   print "( $iR , $iC ) =>", $oWkC->Value, "\n" if($oWkC);
  }
 }
}

    

Answer 3

Oi eu estava olhando para resolver um problema semelhante, coletando dados de vários arquivos.

Eu recorri a instalar o Morefunc addin no Excel (nota: este link faz o download direto de um arquivo zip com o adicionar em). Ele tem um recurso chamado INDIRECT.EXT que lê informações como INDIRECT, mas sem arquivos que precisam ser abertos! Você pode ler mais aqui: link . Eu copiei as instruções do post abaixo.

You may follow the process mentioned below to extract data from specific cells of multiple closed Excel files.

1. All files in the Survey reports folder should be closed
2. In sheet1 of Book1.xlsx, type C4, R9 and C16 in A1:C1
3. From cell D3 downwards, type names of all Excel files (along with their extension i.e. xls, xlsx) in the Survey report folder. To generate this list of Excel files names automatically, refer to the following post.
4. Download and install the Morefunc addin from here. This addin will allow us to use the INDIRECT.EXT function. For MS Excel 2007 and prior versions, one can simply download, install and use this addin. In Excel 2007, once this addin is installed, it will appear under Formulas > Morefunc. For Excel 2010, the process for installing this addin is as follows:

a. Unzip the downloaded folder and double-click on the Setup file

b. Navigate to the following folder C:\Program Files\Morefunc and copy three files - Morefunc, Morefunc11 and Morefunc12

c. Navigate to the following folder C:\Program Files\Microsoft Office\Office14\Library and paste the files there

d. Open MS Excel 2010

e. Go to Files > Options > Addins > Manage Excel Add-ins > Go

f. Check the following three boxes - Morefunc (add-in functions), Morefunc Tools and Morefunc12

g. Click on OK

h. Close MS Excel 2010 and reopen

i. Morefunc should now appear under the Formulas Tab in the Ribbon

5. In cell A3 of sheet1 of Book1.xlsx, enter the following formula and copy till C3 and downwards

=INDIRECT.EXT("'C:\Users\Ashish\Desktop\test\Survey Reports["&$D3&"]Sheet1'!"&A$1)

You will observe that even with all Excel files in the Survey reports folder closed, the INDIRECT.EXT function will display the data from respective cells of those Excel files.

Since data is being extracted from closed Excel files via an addin, performance of the workbook will take a hit. Please try this on limited Excel files first.