VBA Code to Copy data from Multiple Excel workbooks, without opening them, and Paste it into Master Excel workbook.

[Reetesh]

Hello Everyone,

I was trying to come up with a VBA code which copies data from the last two rows of multiple workbooks in a folder, without out opening them, and pastes it into a master workbook.

After googling for it a lot, I got a VBA code mentioned below:


'credit for this technique goes to John Walkenback
'17 MVP tips, tricks and shortcuts for Excel » The Spreadsheet Page

Sub GetDataDemo()

Dim FilePath$, Row&, Column&, Address$

'change constants & FilePath below to suit
'***************************************
Const FileName$ = "Book1.xls"
Const SheetName$ = "Sheet1"
Const NumRows& = 10
Const NumColumns& = 10
FilePath = ActiveWorkbook.Path & "\"
'***************************************

DoEvents
Application.ScreenUpdating = False
If Dir(FilePath & FileName) = Empty Then
MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
Exit Sub
End If
For Row = 1 To NumRows
For Column = 1 To NumColumns
Address = Cells(Row, Column).Address
Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address)
Columns.AutoFit
Next Column
Next Row
ActiveWindow.DisplayZeros = False
End Sub

Private Function GetData(Path, File, Sheet, Address)
Dim Data$
Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
Range(Address).Range("A1").Address(, , xlR1C1)
GetData = ExecuteExcel4Macro(Data)
End Function

However this code only copies the data from First 10 rows & first 10 columns of one workbook.
I tried to amend the above mentioned code. However, I've been unsuccessful in getting the result that I want.

I want to copy the data from last two rows of all the workbooks present in a folder, without opening those workbooks, and paste it in a master file. In the Master file the data which is supposed to be pasted, should be pasted at the top, after inserting a row, below the header(First Row is the Header).

The above code copies and pastes the data without opening the workbook. However, it does it only for one workbook and copies the data from first 10 rows & first 10 columns.

Any Help is appreciated.

Thanks in Advance

Thanks in advance

 

[Reetesh]

Hello Dave,
I tried the code, however its not work the way I want it to.
Will keep on try to get a solution to this.
Thanks a lot again for all you help

 

[NdNoviceHlp]

You are Welcome. Thanks for posting your outcome and good luck with your solution. Dave