Extracting data from Spreadsheets in a folder

IainAL · April 19th, 2007, 10:30 AM

I am looking to write a VBA Macro to extract certain information held in particular cells from all spreadsheets in different folders.

Is there an easy way to move from one spreadsheet to the next in a folder and to write the code so that it runs through to the last spreadsheet, be there 20 or 200 in a folder?

BrianWren · April 19th, 2007, 03:59 PM

There are a few ways to do this.

Using VBâs Dir() function is popular. You specify a file spec on the first call, then provide no argument on subsequent calls. Each call will find the next file that fits the spec. When there are no more that match, calling Dir() returns "". If you call it without arguments after that, and error is raised.

Code:

    Dim fNam As String

    fNam = Dir("*.xls")

    Do Until fNam = ""
        ' Do your processing here

        fNam = Dir()
    Loop

In the "Do processing here" you would put a call to a routine that has the file name as an argument, and which opens the spreadsheet, and processes its values, then closes it.

Excel will let you open more than one spreadsheet at a time, so you can have this code in one spreadsheet, which then opens additional spreadsheets for processing. Those sheets would be accessed through the collection of sheets, Application.Sheets("<[green]Name you used to open it here>").

IainAL · April 20th, 2007, 02:57 AM

Thanks Brian.

What is the code to use if I don't know the filename of each particular spreadsheet in the folder?

Can I do something like MySheets

For each MySheet or something as all the spreadsheet names are going to be individual, and I'm looking for a standardised Macro to run through and extract the data?

Thanks for your help!

Iain