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.


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


End Sub

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

  • Tested and works. – AlanSE Jun 26 '12 at 17:36
  • @Excellll Would it possible to adapt this code, where the filenames are not consecutive numbers? Rather the filenames are random, and it would get the value of the specified cell from all files in the directory? – wysiwyg Nov 17 '17 at 19:40
  • Sorry that should be @Tony Dallimore – wysiwyg Nov 17 '17 at 19:52
  • 1
    @wysiwyg Delete the code to generate the next name in sequence. Move FileName = Dir$(...)to just above the loop and change to FileName = Dir$(PathCrnt & "\*.xls*"). Change Do While True to Do While FileName <> "". Just above Loop add FileName = Dir$. Using Dir$ like this finds every file in PathCrnt that matches "*.xls*". The asterisk is a wildcard that will match anything. If you search for “VBA function Dir” you will find a fuller explanation. I hope that makes sense; it is difficult to explain in a comment. – Tony Dallimore Nov 18 '17 at 11:42

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} ;
  for(my $iC = $oWkS->{MinCol} ;
      defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ;
   $oWkC = $oWkS->{Cells}[$iR][$iC];
   print "( $iR , $iC ) =>", $oWkC->Value, "\n" if($oWkC);
  • 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

Hi I was looking to solve a similar issue, collecting data from multiple files.

I resorted to installing the Morefunc addin to Excel (note: this link directly downloads a zip file with the addin). It has a feature called INDIRECT.EXT function that will read information like INDIRECT but without files having to be open! You can read more here: http://www.ashishmathur.com/extract-data-from-multiple-cells-of-closed-excel-files/. I've copied the instructions from that post below.

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

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

  • Welcome to Super User! Whilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference. – slhck May 17 '15 at 15:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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