multiple workbook references to choose from

[excelarmy]

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!

 

[larrydunn]

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]

thanks

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

 

[larrydunn]

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]

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.