Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Say I have a folder of Excel files with predictable file names and formatting. How can I automatically read from all of them into a separate Excel file? I welcome VBA answers, but only those complete enough to implement without experience. I'm aware that the version can give some difference in behavior, I am personally working with Excel 2007.

Detailed Example

To draw a detailed picture, imagine that I have files named as follows continuing to some known number.

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

For simplicity, consider that they all have one sheet with the name Sheet1, and only one integer value in A1. In this same folder I would like to have an Excel file that has a column that has the integer from each of these files. Just to make absolutely sure that my request is 100% clear, I want a file that looks like this:

the problem

Where the Value column was automatically obtained from the respective files.

What Doesn't Work

I will call this the "manual link solution". This solution falls short because it requires typing for all of the cells. If I have 1000 files from a government website, this is not a practical option.

  • In the above picture, go to cell B2
  • Type =1.xls!a1
  • Hit enter
  • Repeat logically for the other 3 rows

I will call this next one the "indirect fail". It works, but only if the other file is open at the time the values are calculated. Again, this is also impractical if opening 1000 Excel files could crash the computer, not to mention having to close that many files.

  • In the above picture, go to cell B2
  • Type =INDIRECT(A2&"!A1")
  • Hit Enter

It seems like that approach puts me tantalizingly close to a solution, but I don't quite know how to get it to do what I want it to do. Oh, I also need to specify that using the full file name doesn't fix the problem either, meaning, typing ='C:\[1.xls]Sheet1'!A1 has the same problem that the files must be open for it to work (assuming that 1.xls is stored on the C drive top level).

Links That Don't Quite Fix It

You can find some other attempts at this problem on the general Internet. One, for instance, involves worksheets (not files), is opaque in what it does, and doesn't fully describe how to implement the solution.

The closest answer I found on Super User was Can I use SQL to build an Excel data table from other Excel files? This doesn't satisfy what I'm looking for because I don't particularly want an SQL solution, and I want something as simple as straightforward as possible. This is also why I tried to offer my failed solutions, so we won't spin our gears over the half-solutions I've already seen.

This is my first question on Super User, and I believe it to be the right place to ask such questions about Excel because my question us use-oriented.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Sorry if I asked too many clarifications. I am used to answering a question and then being told they meant to ask something different.

Try this:

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

I hope I have added enough explanations. Ask if you need more.

share|improve this answer
    
Tested and works. –  AlanSE Jun 26 '12 at 17:36

have a look here: http://www.ibm.com/developerworks/linux/library/l-pexcel/ I used those (and also CPAN examples) as starting point for my Excel adventures. I created 3 Excel workbooks containing a number in 0,0 in sheet1. Using the code snippet fom the IBM website (minus some print statements, I get:

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

for this, I used this perl code snippet, shamelessly stolen from the developerworks website:

#!/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);
  }
 }
}
share|improve this answer
    
Solution 2 would satisfy what I was looking for if there was an obvious way to automatically resave an arbitrary number of excel files as csv. That seems awfully nontrivial. –  AlanSE Jun 25 '12 at 19:29
    
Excel 2007 means Windows, I presume? Can you upload an example .xls? Do you have access to a UNIX/Linux or Mac OS X machine? Do you absolutely need the result in Excel, or is this negotiable? –  Florenz Kley Jun 26 '12 at 13:43
1  
If the data can be produced in a text format, then the problem is solved because text can be trivially imported into Excel. My present thinking is that creating a VBA function (that takes filename as an argument, for instance) would solve the problem in maybe 3 lines. The idea that temporarily switching OS would solve the problem faster seems wildly implausible to me. –  AlanSE Jun 26 '12 at 14:23
    
Alan, I considered adapting a small perl script for you, because I had a very, very similar problem with downloads from SAP. Took the Excel route first, but in the end put it all in a database because SQL was so much more powerful than pivot tables. This is where my questions come from. Now, when you start throwing around implausability scores, forget it, and have a nice day. –  Florenz Kley Jun 26 '12 at 14:41
    
What does "pivot tables" mean? What is an implausibility score and why is it relevant here? Thanks. –  AlanSE Jun 26 '12 at 14:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.