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]

Sorry, that was my oversight. It was late at night and I lost track of what the problem was from your first post.

But it is a puzzle. In this form it should work as is and in fact opening the workbook in the macro may not help. I need to experiment with this a bit and have some other priorities so give me a half a day or so.

 

[Jobowo]

Oh, it may be easier than I thought. Try this.

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)
    wb.Activate
    If pRange.Hyperlinks.Count <> 0 Then 
          ST1 = pRange.Hyperlinks(1).Address
          ST2 = pRange.Hyperlinks(1).SubAddress
          If ST2 <> "" Then ST1 = "[" & ST1 & "]" & ST2
          HyperLinkText = ST1
    else
          HyperLinkText = "No address available"
    End if
    wb.Close
End Function

 

[Snake Guy]

No worries, I just grateful for all the time you're putting into this!

That still didn't work...maybe if I can show you exactly what I'm doing it will help...

Here are links to the Species_Data.xlsx and the sample spreadsheet Snake_Data_2010.xlsm.
http://www.mediafire.com/?2mmjotmzkj3
http://www.mediafire.com/?znmnzuj2kcz

Good luck!

 

[Jobowo]

Thanks for the files. That really saved me a lot of trouble piecing together data that sometimes doesn't bring up the issues you're having, so it helped a great deal.

Thought I'd provide an update but it's more complicated--bizarre even--than I thought. Messing with this has crashed Excel a couple of times. The problem is that when the Species workbook is closed, the INDEX function returns a different type of value, which doesn't match the macro call argument (range), so the macro isn't even called.

Note that you can get access to the link. Strip off everything to leave only the INDEX function and you'll get the same link whether the workbook is open or closed (or at least it does in my timezone, around 30 minutes ago). You just don't get the "O" link that you had before. Other options include changing the nature of the calls so that the macro gets all the pieces and puts the links together. (eg =HLINK(fnm,row,col)

I'll continue trying to fathom this but it is one of those anomalous behaviours that takes time to unravel.

 

[Snake Guy]

When I strip off HYPERLINK and my macro then all I get is the species name that looks like a hyperlink...

And yeah I get the same crash every once in a while, didn't know what is was from.

There's no rush on this, and feel free to give up if it gets too weird lol. If you figure something out let me know! Thanks

 

[Jobowo]

Just noticed your post as I was about to post this! See below re that. Problem solved but small essay needed to describe problem and fix. Because of the possibility of typos here, let me know of any problems with this. Be sure to describe exactly what happened so I can pinpoint any problems.

Problem: the INDEX function that fetches the "link" from the other workbook fetches a link if the other workbook is open, but fetches only the visible text if the workbook is not open. As a result, the macro is not called if the workbook is not open and the Hyperlink therefore doesn't work either.

Non-Solutions:
(1)macros shouldn't be used to fetch the data because a macro requires the workbook to be open and if you are going open the Species workbook everytime you open the Snake Data you might as well store the Snake Data in the workbook. It is very inefficient.

(2) Contrary to what I suggested before, you cannot use INDEX alone. The result looks like a link but isn't a link.

Solution
Change the Species workbook a little. Store the hyperlink (text) address in another column (say D). You can hide this column if you wish. I've provided a macro to do this.

Now change your Snake_Data workbooks formula to something like:

=HYPERLINK(INDEX('[Species_Data workbook path and name]Sheet1'!$A2:$B999,MATCH(F3,'[Species_Data workbook path and name]Sheet1'!$B2:$B999,0),2),"O")

["O", or whatever text you would like appear as the link]

As a first step, you may want to just put the INDEX function in the cell and get that producing the text of the link whether the Species workbook is open or closed, then just wrap that in HYPERLINK([the index function this already there],"O")

Creating the links

Caution! This code wipes out the contents of Column D of Sheet1. Make sure you have saved a copy.

Close all other workbooks, open the Species_Data workbook, ALT-F11, Insert/Module and cut and paste this code.

Public Sub mkLinks()
    ThisWorkbook.Worksheets(1).Activate
    With ActiveSheet
        .Columns(4).ClearContents
        For i = 1 To .UsedRange.Rows.Count
            If .Cells(i, 2).Hyperlinks.Count > 0 Then
                .Cells(i, 4).Value = .Cells(i, 2).Hyperlinks(1).Address
            End If
        Next
    End With
End Sub

Put the cursor somewhere in the code and press F5. Check the links. If they are OK, delete the macro and save the workbook. Your links in the other workbooks should now work.

 

[Snake Guy]

Now we are getting somewhere! I'm still getting something strange going on though.

I ended up with this function:
=HYPERLINK(INDEX('C:\Users\Owner\Documents\Herping\Data\[Species_Data.xlsm]Sheet1'!$B$1:$D$500,MATCH(F5,'C:\Users\Owner\Documents\Herping\Data\[Species_Data.xlsm]Sheet1'!$B$1:$B$500,0),3),"O")

When it is all in one cell it doesn't return a working link but if I take the HYPERLINK off and HYPERLINK the return value of the INDEX from a different cell, it does work. Why is this?

So I would have something like this is in cell G4:
=INDEX('C:\Users\Owner\Documents\Herping\Data\[Species_Data.xlsm]Sheet1'!$B$1:$D$500,MATCH(F4,'C:\Users\Owner\Documents\Herping\Data\[Species_Data.xlsm]Sheet1'!$B$1:$B$500,0),3)

and then this:
=HYPERLINK(G4,0)

and it works, but not if the function is all in one cell

The same thing may be happening in columns Y and Z. The .csv file is a created by a perl script and I posted it here:

http://www.mediafire.com/?mmuqwygdrkq

I can't get those functions to return a value from the unopened file either, but it works when it's opened

 

[Jobowo]

OK. I'm assuming that you have successfully created the link addresses and stored them in column D of your Species_Data sheet?

If that is the case, INDEX should fetch the address text and HYPERLINK(INDEX should create the link--or it did here.

I do note that you have referred to F5 in the first (HYPERLINK) version and F4 in the INDEX versions posted here ....

As for the .csv file, let's get the link problem solved first and then you could maybe post that as a second thread.

 

[Snake Guy]

Yes I used your macro to create the links, thanks for that.

Don't worry about the F4 or F5, it's the same thing in the workbook.

The INDEX does fetch the address text fine but the HYPERLINK function only works when I put it in a different cell and direct it at the INDEX value cell. This is what I have to have for it to work:

Cell G4:
=INDEX('C:\Users\Owner\Documents\Herping\Data\[Species_Data.xlsm]Sheet1'!$B$1:$D$500,MATCH(F4,'C:\Users\Owner\Documents\Herping\Data\[Species_Data.xlsm]Sheet1'!$B$1:$B$500,0),3)

and then this in a different cell:
=HYPERLINK(G4,0)

I'll play around with this some more cause it doesn't make sense why it's like that...

EDIT:
I don't know what I was doing before but now it is saying "Cannot open the specified file" when written in the separate cells and does nothing when the functions are together

 

[Snake Guy]

I don't know what I was doing before but now it is saying "Cannot open the specified file" when written in the separate cells regardless if the workbook is open
and does nothing when the functions are together when the file is closed but works when it's open