VBA Script to pull data from multiple files into a single spreadsheet

JoHio3316

New Member
Joined
Mar 15, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I have created a template that all members on my team use to provide a quote to our customers. Each person on the team saves the file using a sequential name, i.e. the first document is called 000001, the next is 000002, etc. I need to pull data from those files into a master spreadsheet that tracks who was quoted, at what dollar amount, etc. For example, I will need to pull in cell A3 from document 000001 and have it populate in my master tracking form in cell A2, then pull in cell A3 from document 000002 and have it populate in my master tracking form in cell A3, etc. We currently have files 000001 through 000170. I want to create the master tracking file through VBA code because I don't want to manually populate it for all 170 documents already generated. In addition, I want to set it up so when file 000171 is created (it currently doesn't exist), the master tracking form will automatically populate with the information I need from that separate document. Is there a way to do this?
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

goesr

Well-known Member
Joined
Nov 15, 2013
Messages
661
Hello JoHio3316 and welcome to the forum. As nobody has responded I thought I would throw this out and see if it helps. Have you tried just using something like:

='C:\Users\xxxx\Excel\test3\[000001.xlsx]Sheet1'!$A$3

in your Master spreadsheet to reference the data needed from the other sheets?

Interestingly, if workbook 000001.xlsx is open the formula in Master reverts to:

=[000001.xlsx]Sheet1!$A$3

and it still pulls in the correct data.

Hope this helps get you started. Stay safe!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,493
Messages
5,636,641
Members
416,932
Latest member
mm07

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