Open a workbook using a link and work with it

Question

In the code below, I am trying to open an Excel file from another program and copy a value from it. I managed to get the file to open using the FollowHyperlink command, but I cannot refer to the file to copy anything from it. It seems it doesn't actually open the file until the very end of the Sub. Does anyone know how I can refer to the Excel file I'm trying to open so I can copy a value from it? I would really appreciate some help or tips.

Here is the code to open the file:

ActiveWorkbook.FollowHyperlink Address:="cdb://byname/classname/document/CDB_View/interactive?zeichnung.z_nummer=D00846554&zeichnung.z_index=01"

How can I add to it so that I can copy a value out of it?

This code doesn't work and gives an error:

ActiveWorkbook.FollowHyperlink Address:="cdb://byname/classname/document/CDB_View/interactive?zeichnung.z_nummer=D00846554&zeichnung.z_index=01"
Workbooks("D00846554-01.xlsx").Worksheets("ZACSAANA").Range("A1").Copy ThisWorkbook.Sheets(1).Range("A2")

I assume the newly opened workbook becomes the `ActiveWorkbook`. — BigBen, Dec 10 '19 at 13:25
You know you can get the value from the excel file without even [opening](https://stackoverflow.com/questions/9259862/executeexcel4macro-to-get-value-from-closed-workbook/9261915#9261915) it? — Siddharth Rout, Dec 10 '19 at 13:31
That only works if I have a filepath, which I don't, since the file isn't on my PC. — J. Hill, Dec 10 '19 at 15:27

KH808 · Answer 1 · 2019-12-10T16:19:58.070

0

Try something like this:

Sub RunIt()
Dim Wb As Workbook 'Your Original Workbook
Dim sh As Worksheet 'Sheet where you want to copy to
Dim Sh2 As Worksheet 'Sheet where you're grabbing data from
Dim Wb2 As Workbook 'The Workbook you are opening via hyperlink

    Set Wb = ThisWorkbook

    ActiveWorkbook.FollowHyperlink ("Your HyperLink")

    Set Wb2 = Workbooks(ActiveWorkbook.Name)

    Set sh = Wb.sheets("Sheet in Wb1")


        With Wb2
            Set Sh2 = Wb2.sheets("Sheet in Wb2")
            'Copy values from sh2 to sh1

        End With

    Wb2.Close (False) 'Close Wb2 without saving


End Sub

edited Dec 10 '19 at 16:19

answered Dec 10 '19 at 14:48

KH808

1
1

There is no file path. The file isn't stored on my PC. It's through the link. – J. Hill Dec 10 '19 at 15:25
Just a side note: `Set Wb2 = Workbooks(ActiveWorkbook.Name)` can be simplified to `Set Wb2 = ActiveWorkbook`. – BigBen Dec 10 '19 at 20:57
Hello, thanks for the answer. Unfortunately, it didn't work. It gives a "subscript out of range" error on this line: `Set Sh2 = Wb2.Sheets("ZACSAANA")` I think that happens because the FollowHyperlink command doesn't seem to execute until AFTER the sub has run, so the lines of code after FollowHyperlink can't access it. – J. Hill Dec 12 '19 at 12:31
Have you tried stepping through your code and pausing after the FollowHyperLink command to see if Wb2 loads? – KH808 Dec 12 '19 at 14:07
Yes I tried that, and it doesn't load after the FollowHyperlink command. It only seems to load once the sub has ended. Very strange, but I guess that's how FollowHyperlink works? – J. Hill Dec 12 '19 at 14:23
Next, I'd try splitting your code. One procedure to open the target workbook and another to copy data from it. – KH808 Dec 12 '19 at 14:32
I agree, but how would I do that? I could write it in two separate Subs, but then the user would have to click two separate buttons. Any way to run it all in one? – J. Hill Dec 16 '19 at 14:33
First step for testing, you'd need a sub that opens the workbook and sub that collects the data from the workbook. If that works out, you can try two separate subs and have the user click a buttons to run the subs individually. Alternatively, you can have a button run a 3rd sub that calls the other two other subs in order. – KH808 Dec 16 '19 at 17:46

Open a workbook using a link and work with it

1 Answers1