multiple workbook references to choose from


New Member
Oct 1, 2006
I hope I can explain this clear enough...

I have a situation reports that are updated everyday into new excel sheets. Three different Companies do this in their own workbook. Then they save the information into a folder with the corresponding month, and then another folder of that day of the month. They likewise change the name of the workbook to reflect the date of the reports. I am trying to build a workbook that will consolidate all three everyday into one summarized report.

Now for my question...

Is there a way to define a workbook reference name using a series of formula results?

ie. I want to pull Cell A1 of the workbook .../September/30/30 SEP A_Co.xls, but also update the next day so that it pulls A1 from .../October/1/1 SEP A_Co.xls.

Any help would make my time more worthwhile, and much easier over here in Iraq! Thanks!

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Here's some code that I think will do what you want.
I put it into a command button. If you know how to do this, it's probably the best way.

Note that you will have to type in your actual file path - the code shows mine.

Note also that this assumes that the files and folders have already been created.

Private Sub CommandButton1_Click()
'Get data based on date in B1
Dim iCurrentMonth As Integer
Dim iCurrentDay As Integer
Dim iCurrentYear As Integer
Dim dDate As Date
Dim Mnth(12) As String
Dim sFileName As String
Dim sMainPath As String
Dim sNewDir As String
Dim vDesiredDataA As Variant
Dim vDesiredDataB As Variant
Dim vDesiredDataC As Variant

'Define your main path here
sMainPath = "C:\mydocs\Miscellaneous"

Mnth(1) = "January"
Mnth(2) = "February"
Mnth(3) = "March"
Mnth(4) = "April"
Mnth(5) = "May"
Mnth(6) = "June"
Mnth(7) = "July"
Mnth(8) = "August"
Mnth(9) = "September"
Mnth(10) = "October"
Mnth(11) = "November"
Mnth(12) = "December"

Application.ScreenUpdating = False 'Speed up data retrieval

dDate = ActiveSheet.Range("$B$1").Value
iCurrentMonth = Month(dDate)
iCurrentDay = Day(dDate)
iCurrentYear = Year(dDate)

sNewDir = sMainPath & "\" & Mnth(iCurrentMonth) & "\" & iCurrentDay
ChDir sNewDir

sFileName = iCurrentDay & " SEP A_Co.xls"
Workbooks.Open sFileName, ReadOnly:=True
vDesiredDataA = ActiveSheet.Range("A1").Value

sFileName = iCurrentDay & " SEP B_Co.xls"
Workbooks.Open sFileName, ReadOnly:=True
vDesiredDataB = ActiveSheet.Range("A1").Value

sFileName = iCurrentDay & " SEP C_Co.xls"
Workbooks.Open sFileName, ReadOnly:=True
vDesiredDataC = ActiveSheet.Range("A1").Value

ActiveSheet.Range("A1").Value = vDesiredDataA
ActiveSheet.Range("A2").Value = vDesiredDataB
ActiveSheet.Range("A3").Value = vDesiredDataC

Application.ScreenUpdating = True 'Normal screen
End Sub
Upvote 0
Dear Excel Army:

In reviewing my post to you, I found that I included SEP in the file name. So it won't work in its present form after 9/30.

I've reworked the code, but before sending it to you, I would like to get some idea of the "shape" of the reports you're working with. The code I sent does indeed read cell A1 of the three separate reports, and place the results in A1, A2, A3 of the calling sheet.

If you need to get multiple cells from each of the several reports, I would need to have information about where the cells are. For example, A!, E7, R12, etc.

That given, it will be an easy matter of reading the data and placing it into the result report.

We'll also need to deal with the desired placement of the values in the result report.
The same kind of data about cell addresses.

If you need just a single cell, though, the work's all done.
Upvote 0
Dear excelarmy:

Please come back to the conversation. If you still have an interest,
I have a thorough solution, this time formula-based - though with a
certain amount of code.

I won't bother to publish anything until you say so.
Upvote 0

Forum statistics

Latest member

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
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 "".
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