Dynamically referencing worksheets from a closed workbook.

D

DCM - Albert

New Member
Joined
May 4, 2009
Messages
33
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]Month(Today())!cell

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

Thanks in advance for your help,

Albert
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
I dont see an edit option, sorry I failed to mention:

Using Windows XP and Excel 2007 (However I need the code to work in older versions as well).

Thanks again,

Albert
 
Upvote 0
Just to make sure my question is clear.

I need to format:
A B C D E F G
<TABLE style="WIDTH: 490pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=651 border=0><COLGROUP><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3328" width=91><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" span=3 width=89><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=114 height=20>This information</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 68pt; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-background-source: auto; mso-pattern: black thin-diag-stripe" width=91></TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 67pt; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-background-source: auto; mso-pattern: black thin-diag-stripe" width=89></TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 68pt; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-background-source: auto; mso-pattern: black thin-diag-stripe" width=90></TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 67pt; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-background-source: auto; mso-pattern: black thin-diag-stripe" width=89></TD><TD class=xl75 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; WIDTH: 67pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=89>0</TD><TD class=xl78 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; WIDTH: 67pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=89>0</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #ccffff" height=20>Comes from</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: black; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #CCFFFF thin-diag-stripe"></TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: black; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #CCFFFF thin-diag-stripe"></TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: black; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #CCFFFF thin-diag-stripe"></TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: black; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #CCFFFF thin-diag-stripe"></TD><TD class=xl74 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff">0</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ccffff">0 </TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Another workbook</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-background-source: auto; mso-pattern: black thin-diag-stripe"></TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-background-source: auto; mso-pattern: black thin-diag-stripe"></TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-background-source: auto; mso-pattern: black thin-diag-stripe"></TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: transparent; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-background-source: auto; mso-pattern: black thin-diag-stripe"></TD><TD class=xl75 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">0</TD></TR></TBODY></TABLE>

Each of those cells, need to be updated with information from a closed workbook. I already have the following code for opening the workbook:

Code: 
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("[URL="http://companyweb/compliance/Shared%20Documents/Compliance.xls"]http://companyweb/compliance/Shared%20Documents/Compliance.xls[/URL]", True, True)
    wb.Close False
    Set wb = Workbooks.Open("[URL]http://companyweb/locks/Shared%20Documents/Lock%20Desk.xls[/URL]", True, True)
    wb.Close False
    Set wb = Nothing
    End If
End Sub

I just need to, on the Lock Desk.xls workbook, select information from the current month's worksheet and have it populate the cells formatted above.

Currently, for the Compliance.xls this formula populates properly but the name is static (Compliance is worksheet name)

='http://companyweb/compliance/Shared Documents/[Compliance.xls]Compliance'!$C$14

Again, any assistance is greatly appreciated.

Regards,

Albert
 
Last edited:
Upvote 0
Hi Albert,

Its well past this lad's sack time, but I'll look later. In the meantime, I cannot speak fo anyone else, but I read thru a couple of times and remained unclear. Per the title, it appeared we were trying to get data from a closed wb(s). But reading the awfully short snippet, it looks like we are indeed opening them.

Now, I think you have cells with formulas referring to cells in other workbooks, and you would like the formulas to grab the (same) cell from the correct month.

If this is the case - as you are already using vba, why not either just get the data thru vba straightaway, wherein you could use the current date to select the sheet...

Anyways, I'll look back later, but show us how the worksheets are named, ie JAN, FEB or January, February etc

Hope to help,

Mark
 
Upvote 0
Thanks for the direction. I'll try to be a little more clear.

In workbook OP, on sheet Production I need cell c29 to be equal to workbook Rate Locks, sheet May (current month) cell E14.

Currently, the only way I can do this is (I'm not all that great with VBA) is with the following formula in the [OP.xls]Production!C29 cell:

Code: 
='http://companyweb/Locks/Shared Documents/[Lock%Desk.xls]May'!$E$14

It works perfectly. What I'm trying to do, is either setup a string, or variable of some form, to reference the current month automatically rather than having to go through the 100's of lines, and replace May with June next month.

The name of the worksheets I'm reading FROM are: January, February, March, April, May, June, July, August, September, October, November, December.

I can change those names to the 3 letter abbr. if necessary. Currently just trying to get the sheet to a point I can protect, and not have to update on the first of every month.

I HOPE that makes it a little more clear. Again I appreciate your assistance.

Thanks,

Albert
 
Upvote 0
If the cells of both sheets are constant, look up the download MOREFUNC and check out INDIRECT.EXT. I did the download and added the formulas using variables. I have a Master sheet that accesses a different monthly payroll sheet. The monthly payroll sheet does not change in rows and columns so the formula on the master works just by changing a Month input in a single cell.
 
Upvote 0
DCM - Albert said:
...In workbook OP, on sheet Production I need cell c29 to be equal to workbook Rate Locks, sheet May (current month) cell E14...

...It works perfectly. What I'm trying to do, is either setup a string, or variable of some form, to reference the current month automatically rather than having to go through the 100's of lines, and replace May with June next month...
Click to expand...

Hi Albert,

TedSki's suggestion may well be the best thing and sounds neat:)

I have not used files over an intranet or with http// type addresses, and you seem to need to open the files for the links to update. This being the case, I was thinking that if the vba is already required, maybe just have the code update the vals as well as open the files.

You mention several hundred rows/cells, but as you gave one cell address, here is but a simple way to have the code pull the val(s).

In the sheet's module:
<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Activate()<br><br>    <SPAN style="color:#00007F">If</SPAN> MsgBox("Do you want to retrieve information from the Intranet?", _<br>              vbQuestion + vbYesNo, "MyApp") = vbYes <SPAN style="color:#00007F">Then</SPAN><br>        <br>        <SPAN style="color:#00007F">Call</SPAN> RetrieveVals<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

In a Standard Module:
<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> RetrieveVals()<br><SPAN style="color:#00007F">Dim</SPAN> wb <SPAN style="color:#00007F">As</SPAN> Workbook<br><SPAN style="color:#00007F">Dim</SPAN> strMonthName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#007F00">'// Grab the full name of the current month//</SPAN><br>    strMonthName = MonthName(Month(Date), <SPAN style="color:#00007F">False</SPAN>)<br>    <br>    <SPAN style="color:#007F00">'// updates link, no change//</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wb = Workbooks.Open("http://companyweb/compliance/Shared%...Compliance.xls", <SPAN style="color:#00007F">True</SPAN>, <SPAN style="color:#00007F">True</SPAN>)<br>    wb.Close <SPAN style="color:#00007F">False</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wb = Workbooks.Open("http://companyweb/locks/Shared Doc...ock Desk.xls", <SPAN style="color:#00007F">True</SPAN>, <SPAN style="color:#00007F">True</SPAN>)<br>    <SPAN style="color:#007F00">'// updates value//</SPAN><br>    ThisWorkbook.Worksheets("Production").Range("C29").Value = _<br>        wb.Worksheets(strMonth).Range("E14").Value<br>        <br>    wb.Close <SPAN style="color:#00007F">False</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wb = <SPAN style="color:#00007F">Nothing</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

This of course just updates the one cell you gave as an example. If you decide on a vba approach, you could post the range descripts.

Hope this helps and have a great day,

Mark
 
Upvote 0
GTO said:
In a Standard Module:
ThisWorkbook.Worksheets("Production").Range("C29").Value = _
wb.Worksheets(strMonth).Range("E14").Value
Click to expand...

Mark,

That works perfectly and I appreciate your help. In order to populate multiple cells, on the same sheet from the same source, i would just:

ThisWorkbook.Worksheets("Production").Range("D59").Value = _
wb.Worksheets(strMonth).Range("k15").Value
ThisWorkbook.Worksheets("Production").Range("L46").Value = _
wb.Worksheets(strMonth).Range("F1").Value
ThisWorkbook.Worksheets("Production").Range("C2").Value = _
wb.Worksheets(strMonth).Range("P9").Value


Right? Either way, you're awesome. Took me a week of looking before I found you to answer it for me:biggrin:
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Macro Does Not Close Excel Workbook
2
Replies
14
Views
311
Jeffrey Mahoney
Jeffrey Mahoney
I
  • Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
363
Insurance Dave
I
T
  • Question
Referencing Another Workbook/File
Replies
1
Views
158
dermie_72
D
M
  • Question
Setting global vars in Workbook Open not working
Replies
5
Views
168
missingsc
M
AndyTampa
  • Solved
Limit Quick Tasks macro to a specific worksheet
Replies
4
Views
116
mumps
mumps

Forum statistics

Threads
1,216,025
Messages
6,128,348
Members
449,443
Latest member
Chrissy_M

Share this page

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
Back
Top