Linking to Unopened Workbook

[Snake Guy]

Hey guys got another question for ya.

I'm using a macro to try to pull data from a worksheet but it only works when its opened. I found a macro that is supposed to get data from an unopened worksheet but I don't understand VBA very well so is there anyone here that could tell me how to merge my macro with this one to allow me to access the unopened worksheet?

This is the website for retrieve data from unopened worksheets:
http://www.ozgrid.com/VBA/ExtractFromClosedWorkbook.htm

And this is my macro I'm trying to use:

Function HyperLinkText(pRange As Range) As String

Dim ST1 As String
Dim ST2 As String

If pRange.Hyperlinks.Count = 0 Then
Exit Function
End If

ST1 = pRange.Hyperlinks(1).Address
ST2 = pRange.Hyperlinks(1).SubAddress

If ST2 <> "" Then
ST1 = "[" & ST1 & "]" & ST2
End If

HyperLinkText = ST1

End Function

I would really appreciate any assistance on this one! Thanks in advance!

 

[Jobowo]

Checking to see how you'd use the code somewhere else is more complicated than opening the workbook yourself (you can cut and paste this code directly into your macro)

Dim Flnm as string, wb as workbook
Flnm = "[Workbook file path and name]"
Workbooks.open Flnm
set wb = workbooks(Flnm)

and then preface any references to that other workbook with wb. (as in Workbooks.activesheet etc)

Remember that thisWorkbook is the assumed default for any sheet references like ActiveSheet so if you want to refer to another workbook you just specifically identify that you want to get the data from that other wb.

(It isn't clear which value in your code you want to get from that other sheet or I'd be more specific)

 

[Snake Guy]

Thanks Jobowo! Im sure this would work if I could use it correctly...

I am trying to retrieve a hyperlink from "C:\Users\Owner\Documents\Herping\Data\Species_Data.xlsx" sheet 1

When I write it like this I get "compile error: Invalid outside procedure"

Dim Flnm As String, wb As Workbook
Flnm = "C:\Users\Owner\Documents\Herping\Data\Species_Data.xlsx"
Workbooks.Open Flnm
Set wb = Workbooks(Flnm)

Like I said I'm not very good with VBA...what am I doing wrong?

 

[Jobowo]

Hi

Nothing serious I think. You may have pasted your code outside the

Public Sub xxx
code lines
End sub

The code I gave you must be pasted betwen Sub...End sub, just after Dim ST2 in your first code listing

 

[Snake Guy]

Hmmm...
I now have

Function HyperLinkText(pRange As Range) As String

Dim ST1 As String
Dim ST2 As String

Dim Flnm As String, wb As Workbook
Flnm = "C:\Users\Owner\Documents\Herping\Data\Species_Data.xlsx"
Workbooks.Open Flnm
Set wb = Workbooks(Flnm)

If pRange.Hyperlinks.Count = 0 Then
Exit Function
End If

ST1 = pRange.Hyperlinks(1).Address
ST2 = pRange.Hyperlinks(1).SubAddress

If ST2 <> "" Then
ST1 = "[" & ST1 & "]" & ST2
End If

HyperLinkText = ST1

End Function

But it returns #VALUE! just like before when the other workbook is closed. Any ideas?

 

[Snake Guy]

I'm guessing that when it's looking up ST1, ST2 it's not getting directed over to wb but I don't know how to do that...

 

[Jobowo]

Well that's what I wasn't clear about in my first post. You are referencing pRange, which is likely a reference in the current workbook, so neither ST1 or ST2 appear to be coming from any other workbook.

If you want ST2 to be fetched from that other workbook, you can use something like

ST2 = wb.Worksheets("Sheet1").range("A1")

Note that you may not need a macro to do this, or may not need to open the other workbook. You can get that value (ST2) into a cell in the current workbook by simply referencing it in a normal worksheet cell. Just use a formula like this:

='[C:\Users\Owner\Documents\Herping\Data\Species_Data.xlsx]Sheet1'!A1

 

[Snake Guy]

Ok sorry to be a pain but maybe if I explain this fully then I can stop bothering you

I am trying to retrieve a hyperlink and keep it active from Species_Data.xlsx into Snake_Data_2010.xlsm with this function:

=HYPERLINK(HyperLinkText(INDEX('C:\Users\Owner\Documents\Herping\Data\[Species_Data.xlsx]Sheet1'!$B:$B,MATCH(F3,'C:\Users\Owner\Documents\Herping\Data\[Species_Data.xlsx]Sheet1'!$B:$B,0),MATCH("Species",'C:\Users\Owner\Documents\Herping\Data\[Species_Data.xlsx]Sheet1'!$B$1:$B$1,0))), "O")

HyperLinkText being the macro I've been posting.

And no, I tried just referencing the other workbook but it doesn't work.

I don't understand at all what
ST1 = pRange.Hyperlinks(1).Address
ST2 = pRange.Hyperlinks(1).SubAddress
are literally meaning so I don't know what ST1 would be if ST2 is
ST2 = wb.Worksheets("Sheet1").range("A1").

Would I replace A1 with something like "Hyperlinks(1).SubAddress"?

 

[Jobowo]

Ok sorry to be a pain but maybe if I explain this fully then I can stop bothering you :wink:

No problem, we're both here to get this right. But now I can see what I suspect is going wrong with the macro. In fact there are a host of possible errors before it even reaches the macro.

1. You're already pulling a value from the other file, and it isn't the "range" that the macro is expecting but a value

2. The value is "INDEX(range,[1] MATCH(...), [2] MATCH(..))". INDEX provides the value in [1] Row, and [2] Column of range (which is in that other workbook). But the range being indexed is a single column (B:B), so column should be 1--that's one question mark.

3. The MATCHES lookup values in a range. MATCH [1] looks up the value in F3 of the current sheet in that other workbook sheet, column B. But if F3 isn't found, the MATCH will return an #NA error and your formula crashes there. The second MATCH is just as weird. It looks to see if "species" is in cell B1 of that other sheet. Again, if it isn't it will generate an error.

Bottom line, I suggest that Copy/Paste all of that INDEX function (everything up that ),"O") at the end, and put it into a separate cell ("=INDEX(range, match, match)"). See if you can get that to work: to generate a valid value and if not, get to the bottom of that problem. Then the best thing to do would be to simply take that value into a Hyperlink value (that is, if the =INDEX is in A1 your link will say =Hyperlink(A1,...) . Now I do seem to recall that if A1 is a link, you have to covert it to text before you can put it back into a link and so that might be where the macro comes in.

But now I can solve that problem in 10 seconds

 

[Snake Guy]

Thanks for sticking with me on this but I think I am not conveying my problem correctly.

The function I posted with the first macro I posted works great but only when Species_Data.xlsx is open. When it is closed it returns #VALUE!

So what I need to do is add to my existing macro to allow it to read from a closed file...