Referring to a worksheet in a closed workbook.

[Al Bert]

I've done quite a bit of searching, and apologize in advance if there is some obvious answer to this question I just haven't been able to come up with yet.

I'm reading data, from specific cells off a closed workbook. When the sheet that needs said cell data is activated, it automatically opens the workbook and references the sheet nessecary. The issue I've come across, is I now need to access another workbook (Easy to open) with 12 sheets 1 for each month, and only read from the worksheet of the actual Month...

Kind of lost on how to possibly make this work. I basically need something like:

=location/[workbook.xls]worksheet!cell - where worksheet = B12

and B12 =month(today()) and is formatted to MMMM



I realize that doesn't work, looking for another way around it. Any advice is greatly appreciated.

I'm using WinXP and Excel 1997

Thanks in advance for your help,

Albert


PS: This question has been posted on another forum as well... Post found here: http://www.mrexcel.com/forum/showthread.php?t=388107

[ravishankar]

HI
try the formula shown in the following link
http://www.excelforum.com/excel-prog...pening-it.html
Ravi

[Al Bert]

I already have it opening the closed workbook. I'm already past the step of reading from the workbook after its open. The trouble I'm having is reading from a sheet, that matches the current month and automatically updating to match the month....

So in May, it reads from the May worksheet after I open the the workbook. Then when June hits, it reads from June. Obviously I can just reference directly using June!E5. But having to change that each month is a pain, since its on multiple sheets in multiple cells and replace all isn't an option.

This is the code I'm using to open the workbook:

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
If MsgBox("Do you want to retrieve information from the Intranet?", vbQuestion + vbYesNo, "MyApp") = vbYes Then
Dim wb As Workbook
    Set wb = Workbooks.Open("http://companyweb/compliance/Shared%20Documents/Compliance.xls", True, True)
    wb.Close False
    Set wb = Workbooks.Open("http://companyweb/locks/Shared%20Documents/Lock%20Desk.xls", True, True)
    wb.Close False
    Set wb = Nothing
    End If
End Sub

I just need the cell formula, to read from Lock%Desk.xls the worksheet that has the current month's information.

Thanks again for any assistance.

Albert

[Al Bert]

Bump to the top, really need to resolve this issue!

[Al Bert]

The question was answered. See resolution here:

http://www.mrexcel.com/forum/showthread.php?t=388107