multiple workbook references to choose from

excelarmy

New Member
Joined
Oct 1, 2006
Messages
2
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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

larrydunn

Board Regular
Joined
Jun 1, 2003
Messages
130
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
ActiveWorkbook.Sheets("Sheet1").Select
vDesiredDataA = ActiveSheet.Range("A1").Value
ActiveWorkbook.Close

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

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

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

Application.ScreenUpdating = True 'Normal screen
ActiveSheet.Range("A1").Select
End Sub
 

excelarmy

New Member
Joined
Oct 1, 2006
Messages
2
thanks

Thanks! I haven't had a chance to try it yet, but just wanted to say thanks for your time.
 

larrydunn

Board Regular
Joined
Jun 1, 2003
Messages
130
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.
 

larrydunn

Board Regular
Joined
Jun 1, 2003
Messages
130
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.
 

Forum statistics

Threads
1,137,205
Messages
5,680,174
Members
419,887
Latest member
Vasokir

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