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?

Answer 1

Did you try

Tools->Macro->Record New Macro

to create maco to do the same thing

Answer 2

Another solution is to have your roll-up spreadsheet access the other spreadsheets by filename and grab the data itself.

To do that, you'll need to have all of the spreadsheets open at the same time so it can update the links, but that's still probably faster than opening and copying/pasting one at a time, even with a macro. Every spreadsheet will need to have a unique filename.

If the names of the spreadsheets aren't known until you receive them, or they change regularly, create a column in your roll-up table to store the filename of the sheets, then build the address you need using string manipulation and get the data using INDIRECT().

Example to grab one cell of data from one particular file:

=INDIRECT("'[C:\path\workbook.xls]MyWorksheet'!$A$2")

Rinse and repeat the above for each cell of each spreadsheet you want to get.

You should be clever about how to get the string to pass to INDIRECT(). Build it as a formula so you can use literally the same formula for every cell you need to retrieve.

Example:

= INDIRECT("'[" & $A2 & "]MyWorksheet'!$" & ADDRESS(3, COL()))

The formula above will go to the spreadsheet whose filename is in $A2 (note the lack of $ before "2" so you can paste the same formula to other rows for other files), and get the value of the cell on the MyWorksheet sheet on row three and the current column (so, if this is in B2 on your roll-up, it gets B3 from the other file).

Adjust the ADDRESS function to add offsets to the row and column needed.

The advantage of the solution above is that the same formula can be copied and pasted across the rows and columns you need to populate, and Excel will adjust the $A2 and COL() as needed. Very maintainable.

Edit once I had a similar situation, and I couldn't load all of the spreadsheets at once (more than 200). I think I ended up writing the VBA so it did not actually open and read the Excel files. Instead, I had it loop through the filenames, open an ODBC connection to each, and use ADO to read the values I needed from a prescribed named range (which appears as a "table" in ODBC--the worksheets also appear as "tables" but there are rules about allowed names). This was much faster than opening and closing Excel files, and had the added advantage of not crashing Excel.

Answer 3

Rodrigo,

I'm guessing you mean 100+ workbooks that you need to individually open and copy and paste into one? Sounds like fun :)

If you could put them all in a single directory, opening each file is reasonably easy, have a search on that first. (@Mark Biek has posted a good example for you )

Once you have a file open, i would then copy the data into an ADO recordset which you would then append to. I have posted some code for a doing something very similar with merging multiple sheets in one workbook.

It's not exactly what you need, but it should help. If not, post how far you get and i'll have a another look during the week.

Answer 4

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.

Answer 5

I wrote this years ago, but maybe it will help you out. I added the extension for the latest version of Excel (xlsx). Seems to work.

Sub MergeExcelDocs()
    Dim lastRow As Integer
    Dim docPath As String
    Dim baseCell As Excel.range
    Dim sysObj As Variant, folderObj As Variant, fileObj As Variant
    Application.ScreenUpdating = False
    docPath = Application.GetOpenFilename(FileFilter:="Text Files (*.txt),*.txt,Excel Files (*.xls),*.xls,Excel 2007 Files (*.xlsx),*.xlsx", FilterIndex:=2, Title:="Choose any file")
    Workbooks.Add
    Set baseCell = range("A1")
    Set sysObj = CreateObject("scripting.filesystemobject")
    Set fileObj = sysObj.getFile(docPath)
    Set folderObj = fileObj.ParentFolder
    For Each fileObj In folderObj.Files
        Workbooks.Open Filename:=fileObj.path
        range(range("A1"), ActiveCell.SpecialCells(xlLastCell)).Copy
        lastRow = baseCell.SpecialCells(xlLastCell).row
        baseCell.Offset(lastRow, 0).PasteSpecial (xlPasteValues)
        baseCell.Copy
        ActiveWindow.Close SaveChanges:=False
    Next
End Sub

EDIT:

I should mention how it works. When you start the macro, it brings up an Open File dialog. Double-click the first file in the list (or any file for that matter). It will create a new workbook then loop through all the files in the folder. For each file, it copies all the content from the first worksheet and pastes it at the end of the new workbook. That's pretty much all there is to it.

Answer 6

This can be achieved by using TransferSpreadsheet in Access. See this link:

http://datapigtechnologies.com/blog/index.php/using-access-to-combine-multiple-excel-files-method-2/comment-page-1/#comment-1741

This solution doesn't require any VBA.

Answer 7

In the past, I used VBA to create external references (links).

I posted about it here (see example 2):

http://stackoverflow.com/questions/704572/best-short-examples-of-the-need-for-excel-vba

It's similar to using INDIRECT, but without needing to have the excel workbooks open.

The only disadvantage is that an old computer or an old version of excel, not sure which, can make this process slow. I believe it is because every time a new external reference is added, all the other external references gets updated. In order to make it go faster, I set Calculation to Manual, added the external references, and set Calculation to Automatic to update them.

After that, if you just want the values, you can use Break Links, or Copy and Paste Special Values.