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]

No, there doesn't seem to be any reason why the combined functions shouldn't work now. As I've said, it works fine here.

Again may not be an issue in the spreadsheet but that should be =HYPERLINK(G4,"O")

The "Cannot open file" message is unrelated to this since your link doesn't open the file; if it can't find Species_Datat, it will give a #REF error. The message may be something to do with your .csv problem. Best thing is to blank out everything other than the link problem and test that until you are successful. Then add pieces back in to see where the problems with the file message come back in.