Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Linking to Unopened Workbook

This is a discussion on Linking to Unopened Workbook within the Excel Questions forums, part of the Question Forums category; Hey guys got another question for ya. I'm using a macro to try to pull data from a worksheet but ...

  1. #1
    New Member
    Join Date
    Oct 2009
    Posts
    45

    Default Linking to Unopened Workbook

    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!

  2. #2
    Board Regular
    Join Date
    Oct 2009
    Location
    Canada, EH
    Posts
    242

    Default Re: Linking to Unopened Workbook

    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)
    Last edited by Jobowo; Nov 30th, 2009 at 01:02 AM.

  3. #3
    New Member
    Join Date
    Oct 2009
    Posts
    45

    Default Re: Linking to Unopened Workbook

    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?

  4. #4
    Board Regular
    Join Date
    Oct 2009
    Location
    Canada, EH
    Posts
    242

    Default Re: Linking to Unopened Workbook

    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

  5. #5
    New Member
    Join Date
    Oct 2009
    Posts
    45

    Default Re: Linking to Unopened Workbook

    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?
    Last edited by Snake Guy; Nov 30th, 2009 at 01:45 AM.

  6. #6
    New Member
    Join Date
    Oct 2009
    Posts
    45

    Default Re: Linking to Unopened Workbook

    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...

  7. #7
    Board Regular
    Join Date
    Oct 2009
    Location
    Canada, EH
    Posts
    242

    Default Re: Linking to Unopened Workbook

    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

  8. #8
    New Member
    Join Date
    Oct 2009
    Posts
    45

    Default Re: Linking to Unopened Workbook

    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"?

  9. #9
    Board Regular
    Join Date
    Oct 2009
    Location
    Canada, EH
    Posts
    242

    Default Re: Linking to Unopened Workbook

    Ok sorry to be a pain but maybe if I explain this fully then I can stop bothering you
    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
    Last edited by Jobowo; Nov 30th, 2009 at 03:19 AM.

  10. #10
    New Member
    Join Date
    Oct 2009
    Posts
    45

    Default Re: Linking to Unopened Workbook

    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...

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com