Automating external sheet references - can it be done?

[WildHare]

Greetings.

I am in the process of constructing a workbook that will, of necessity, contain dozens of references to many external worksheets - the external worksheets all follow a naming pattern, and the cells I will be looking at will remain constant, however.

For example, I will be looking at Cell H16 in several different workbooks (one book per day, I need to do a weekly summary that will be ongoing every week for the foreseeable future. My formulas are referencing about 15 different cells in each external workbook.

I find it a pain in the rear to copy my formulae, and then have to edit them to update the target workbook name for each day, E.g. Feb20.xls, Feb21.xls, Feb22.xls.

Is there some way I can set up "reference text" cell values in MY workbook, containing the Drive and Path to the external workbooks, and use them? I've tried putting values such as:

C:\FEBHF[22.XLS]
HOTELFLASH'
!H16

in cells, and then building a formula like =+A3+A4+A5+A6, but my result is only the LITERAL C:\FEBHF\[22.XL]HOTELFLASH'!H16"

I've tried concatenating the equal sign in front as well, but that still doesn't work.

I need to return the ACTUAL value represented by the EXTERNAL REFERENCE that I've built. I checked out the INDIRECT function, but that returns a "#REF" error.

What am I missing? Can what I envision even be done?

Thanks much in advance.

Jim



Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Gavona]

Try the Indirect function.
=Indirect(A3+A4+A5+A6)

Or could Edit,Links provide an alertnative approach?

 

[WildHare]

Thx for the response - I've tried the INDIRECT function, but it does not work correctly in my situation. I believe the problem is converting my literal address into a cell value that is resolved correctly.

I have altered my scenario, and will now use worksheets within a workbook, and a consolidation sheet for each week. It's still not trouble-free, but it appears to be a bit simpler to assign the formulae.

Jim
Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[xlbo]

Just to clear up INDIRECT
a REF error will be returned if:
a:the workbook that is being referenced is closed
b:if you forget your ' '
ie you would need:
'C:\FEBHF\[22.XL]HOTELFLASH'!H16

Other than that, it'll work.... Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[xlbo]

Wildhare - thinking about it, you may want to have a look at this - courtesy of John Walkenbach (

http://www.J-Walk.com).

It's a code function to extract data from closed workbooks:

Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists
If Right(path, 1) <> &quot;\&quot; Then path = path & &quot;\&quot;
If Dir(path & file) = &quot;&quot; Then
GetValue = &quot;File Not Found&quot;
Exit Function
End If

' Create the argument
arg = &quot;'&quot; & path & &quot;[&quot; & file & &quot;]&quot; & sheet & &quot;'!&quot; & _
Range(ref).Range(&quot;A1&quot.Address(, , xlR1C1)

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function

Sub getdatastuff()
MsgBox GetValue(Range(&quot;A1&quot, Range(&quot;A2&quot, Range(&quot;A3&quot, Range(&quot;A4&quot)
End Sub


Obviously, the msgbox can be removed and replaced by a loop which gets values for changing cell refs
Path in A1
Workbook name in A2
Sheet Name in A3
Cell Ref in A4
You could have 2 arrays - 1st one holds the cell refs you want to pick up from and the 2nd holds the cell refs that you need to put the data in - summat like:

sub getnewvalues()
dim copyArr as variant, ValArr as variant

copyArr = array(&quot;A4&quot;,&quot;D14&quot;,&quot;B12&quot;,&quot;F32&quot;,&quot;A12&quot
ValArr = array(&quot;A10&quot;,&quot;A11&quot;,&quot;A12&quot;,&quot;A13&quot;,&quot;A14&quot

for i = 0 to ubound(copyArr)
activesheet.range(ValArr(i)).value = getValue(range(&quot;A1&quot;,range(&quot;A2&quot,range(&quot;A3&quot,range(copyArr(i)))
next i
end sub

HTH

ps - nice Access site you have (I presume it's still going) - got some tips off there a while back Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[WildHare]

Geoff thanks for the tip - I'm gonna hold that one aside for later on. For now, I think I've managed the process a little better with copy/pasting the new files into sheets in a consolidation workbook. The sheets are sent to my department from different accounting groups. There are about 80 cells scattered all over the individual single files I need to consolidate to weekly numbers from daily casino and hotel accounting files. F&B outlets have plan and actual revenue numbers for food, beverage, covers, comps and the like, plus other hotel and casino figures like parking, (valet, self park, hotel arrivals), bus patrons, non-gaming revenue (5 types) etc etc etc. Once I built the template, it was fairly easy to copy and paste, then fill the formula ranges.

So I'll just end up with workbooks with from 28 to 31 sheets in them, and one weekly consolidation sheet for each 7-day period.

You're right, though, INDIRECT only works if the refereneced workbook is open, and mine were not. I couldn't see having to open up to 7 other files each time I needed to see my weekly sums. And I DID have the quotes, too.

See ya around.

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm