Data extraction with Excel

Question

I monthly receive 100+ excel spreadsheet from wich i take a fixed range and paste in other spreadsheet to make a report.

Im trying to write a vba script to iterate my excel files and copy the range in one spreadsheet, but i havent been able to do it.

Is there an easy way to do this?

Accepted Answer

Here's some VBA code that demonstrates iterating over a bunch of Excel files in a directory and opening each one:

Dim sourcePath As String
Dim curFile As String
Dim curWB As Excel.Workbook
Dim destWB As Excel.Workbook

Set destWB = ActiveWorkbook
sourcePath = "C:files"

curFile = Dir(sourcePath & "*.xls")
While curFile <> ""
    Set curWB = Workbooks.Open(sourcePath & "" & curFile)

curWB.Close
    curFile = Dir()
Wend

Hopefully that'll be a good enough starting point for you to work your existing macro code.

Question posted 2009 · +5 upvotes