Referencing cell in multiple workbooks on server by searching folder

[dieguitosoto]

Win 7 Pro
Excel 2007

I have about 150 workbooks in a folder on a server and need to pull a specific value from a cell ("O1") in each of these workbooks. This cell is on the same worksheet in each workbook ("Main").

The workbook that I'm trying to pull the value into has the names of each of the workbooks in that folder on the server, so I had thought some type of lookup function would work, but I'm much too inexperienced to figure it out. I've tried googling it, but was not able to find an answer.

Thanks!!

-D

 

[dermie_72]

Have a look here it might lead you in the right direct. Not 100% on what you're after but it's close.
http://www.mrexcel.com/forum/excel-questions/666022-automating-tedious-copy-pasting.html
http://www.mrexcel.com/forum/excel-questions/676132-import-multi-workbooks-seperate-tabs.html
http://www.mrexcel.com/forum/excel-questions/677202-copy-data-multiple-folders.html

Not sure why you couldn't find these on google, but they're all here.
Good luck :D

 

[dieguitosoto]

Have a look here it might lead you in the right direct. Not 100% on what you're after but it's close.
http://www.mrexcel.com/forum/excel-questions/666022-automating-tedious-copy-pasting.html
http://www.mrexcel.com/forum/excel-questions/676132-import-multi-workbooks-seperate-tabs.html
http://www.mrexcel.com/forum/excel-questions/677202-copy-data-multiple-folders.html

Not sure why you couldn't find these on google, but they're all here.
Good luck :D

Thanks so much for the reply. In each of these examples, the files need to be opened to have the data copied. My files with data in them reside on a server and take a good 30 seconds or more for each to open up. I'm hoping to find a way to avoid opening them and read straight from the spreadsheet. Is this even possible?

The code being used to loop through the folder and file names worked just fine, but again, it accomplishes the task in the same amount of time.

-Diego