Excel: How to Access closed workbook Cell Value(s)?

[KHMcKenna]

I need some help on how to create an Excel function that will allow me to check for the value of a Dynamically determined NameRange (Day1, Day2.. etc...) on a CLOSED workbook/worksheet.

I have found how to create such a function (via search engine(s)), but its only accessible via VBA calls, and not a callable via a worksheet formula.

I'm currently working on a "work-around" of creating a link to another workbook (GUI method), then creating a macro that will "replace" the contents of the link with a VBA composed path/link. I'm sure that after I hack my way thru the VBA Search/Replace syntax it will work, but it is really a backdoor approach to solve the problem.

So it is, that I am here... rubbing the proverbial lantern of the VBA Genie(s), hoping I will get at least one wish granted...

 

[SkipVought]

Hi,

Here's a way using a spreadsheet reference.

Create a new sheet/cell that can be used to retrieve the desired reference.

Using VBA, manipulate the cell coordinates to obtain the desired value...

Sub GetClosedWorkbookValue()
    Dim sPath           'closed workbook path down to SHEET
    Dim sReference      'cell reference to closed workbook
    Dim sValue          'value from closed workbook
    
    sPath = "='C:\My Documents\My Data Sources\[IndIndex.xls]MailMerge'!"
    sReference = sPath & Cells(2, 2).Address
    
    Sheets(2).Cells(1, 1).Value = sReference
    
    sValue = Sheets(2).Cells(1, 1).Value
End Sub
[\code]
By manipulating the Cells(row, col) values you can "navigate" the sheet in the closed workbook.

Hope this helps  :-) Skip,
SkipAndMary1017@mindspring.com

 

[KHMcKenna]

Thanks Skip... This VB newbie is attempting to digest what you sent, and how to apply it to my scenario....

(enter here, the scent of a smoldering brain)

I'll be back in a while.. (hopefully)

Kevin

 

[SkipVought]

By the way, MailMerge is a Sheet Name. So to generalize the path reference...

sPath = "='C:\YourPath\[YourExcel.xls]YourSheet'!"
[\code]
:-)
    
 Skip,
SkipAndMary1017@mindspring.com

 

[KHMcKenna]

OK... I've been reading and RE-READING this...

One question that comes to me as I try to come to grips of the mechanics involved here is... Would this convertible to a FUNCTION?? With me not quite up to speed as to what is exactly happening here yet, my arms lenght question is, could it look like:

Function GetClosedWorkbookValue(sPath,sReference,sValue)
' Dim sPath 'closed workbook path down to SHEET
' Dim sReference 'cell reference to closed workbook
' Dim sValue 'value from closed workbook

sPath = "='C:\MyPath\[MyExcel.xls]MySheet'!"
sReference = sPath & Cells(2, 2).Address

Sheets(2).Cells(1, 1).Value = sReference

sValue = Sheets(2).Cells(1, 1).Value
GetClosedWorkbookValue = sValue
End Sub

(smiling) If it looks like I'm lost, it's probably because I am!

 

[KHMcKenna]

OK.. I see lots of problems with my proposed post. (blush) I'm glad not many people read these posts! (wink).

(the smoldering smell continues)

 

[SkipVought]

The VALUE is in Sheet(2) A1 as well as in sValue. Yes, you could make it a function that you would call to get the value, like this...

Function GetClosedWorkbookValue(sPath,lRow,iCol)
[\code]
where lRow & iCol are the row/col coordinates of the value you want.  

Then set the sValue to GetClosedWorkbookValue
[code]
   GetClosedWorkbookValue = sValue
[\code]
:-)
 Skip,
SkipAndMary1017@mindspring.com

 

[KHMcKenna]

I'm hacking around on this to get it to work for my specific application... In my efforts prior to posting here, I had gotten everything to work ALMOST to my satisfaction with the exception that the cell values were showing the PATH to the value I was wanting, and not the VALUE.... It's looking like the missing link in my attempts may be the ".Value".. Now if I can just go back and re-create the one of the 100's of attempts I had made that got me those results I may be OK!

(the smoldering smell continues)

Thanks for you help and input, and I'll be back either with the results of what I did, or more HELP! (grin)

Kevin

 

[KHMcKenna]

....

Is there any way to access the "VALUE" of the EXTERNAL cell, other than making a local value of it?

(RE: "Sheets(2).Cells(1, 1).Value = sReference" )

???

The reason I ask, is I will have a few dozen references to this function, all requesting their unique values. That would create another programming issue of WHERE the values are stored locally. *IF* this were to be the only way to access the external value.. then perhaps creating a completely seperate sheets with a (3-D'tionally) VERTICAL references.. (same cell as the calling cell, diff sheet). Then just making a straight up cell reference to those cells. (?)

 

[SkipVought]

I am not following what you are asking. If you just want data on a sheet, you don't have to use VBA. If you want data in your procedure, then you can get it using row & col coordinates. Skip,
SkipAndMary1017@mindspring.com

 

[KHMcKenna]

The end functionality that I am needing is to test the external books' cell (actually identified as a single cell RANGE for ease of locating the information [its actually DATE oriented naming]) for a non-null value.

So, a typical application in this scenario might look like...

&quot;IF(GetClosedWorkbookValue(&quot;PathName&quot;,&quot;RangeName&quot <> &quot;&quot;, True, blah blah blah...)

I am actually constructing a dynamic &quot;PathName&quot; in an adjacent cell with a whole slew of text and concatentate(s). The RangeName, I have built a function to format out the DATE to be aleagal range naming.. 08-04-2002 is actually becoming &quot;Day04&quot;, and there is a &quot;Range Name&quot; (single cell)in the client workbook with a matching name.

It's a report tree, where the clients are supposed to be entering COMPLIANCE figures in their respective sheets. This MASTERBOOK needs to insure they are, and in another sheet, accumulate the compliance figures.

It had orginally started as a &quot;FileExist&quot; check, I could easily check for that and all was great. Then the report structure changed and now I'm needingto interogate the contents of the sheets/books to find out if the (formish) values have been filled in.

Apologies if this got way to wordy, but I'm hoping it gives you a better sense of the goal.

Thankful for your time and effort,

Kevin

 

[tmktech]

Skip and Mary,

This post is several months old, but here's a STAR.
This is a great and powerful solution. Much better than the Excel4Macro solution in a similar post.

Hats off!

TMKTech

 

[tmktech]

I meant to add that I then pasted the value of the cell over itself to eliminate the link.

If somebody knows of a cleaner way to get the VALUE of the closed workbook's cell without leaving a residual link (I messed with it and could get it so reverted to a copy /pastespecial - values to elim. the link).

Thanks!

TMKTech