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

Reetesh

New Member
Joined
Sep 6, 2020
Messages
37
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Sep 6, 2020
Messages
37
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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 :)
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,776
You are Welcome. Thanks for posting your outcome and good luck with your solution. Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,130,346
Messages
5,641,598
Members
417,225
Latest member
LukiiMaxim96

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top