Excel – Automatically reading data from separate Excel files

microsoft excel

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.

Best Answer

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.

Related Question