Referencing A Range in Multiple Closed Workbooks

SureShotUK

New Member
Joined
Mar 28, 2012
Messages
5
Hello All

I am a VBA virgin (pretty much) but I think that I have a requirement that can only be solved using VBA so any help you can offer would be great.

I receive a spreadsheet (.xls extension) every workday and the filename is made up of the date in yyyymmdd format.

I have the individual excel spreadsheets for each day going back years.

The format of these spreadsheets changes every year or so!!

I would like to do something like a MATCH() formula to find out what row a specific value is located.

I have the following details in my new and separate spreadsheet where I would like to record the row numbers.

Date (eg 02/01/2008 ), Filepath (eg 'C:\Documents and Settings\My Documents\2008\[20080102.xls]Sheet1'!B:B ), Lookup value (eg Apple )then a Blank column for the row number to be posted.

I then have 1085 more rows (one for each working day since Jan 2008) where the filepaths are identical apart from the year (folder) and the filename.

I don't mind if the automated procedure when started takes a while as I intend to only do this once then copy and paste special values once I have them, but I don't want to have to open over 1000 spreadsheets (and growing day by day).

In my own head, the VBA would call up the range (column B in the desired woorkbook) then look for the relevant cell with the value in it without actually opening the workbook (if this is even possible). Then return the row number that it found that value on in the target workbook. As I say VBA virgin.

Any help will be grand and it would be easy if I could use VLOOKUP with INDIRECT on closed workbooks :)

Thanks
 
Last edited:

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Forum statistics

Threads
1,136,968
Messages
5,678,872
Members
419,787
Latest member
juanam

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