Populate a Master spreadsheet using VBA and hyperlinks to open files

[svendfj]

I am trying to make certain that I properly use hyperlinks to access the documents I will be pulling from to update a Master spreadsheet. I have a Master spreadsheet that includes a column with hyperlinks (for example: http://application.company.com/1234567).

To allow updating of this spreadsheet, I have code that will go down the sheet row by row, use the hyperlink on each row to access the document associated with that row, and then update the information and proceed to the next row.

I have recorded a macro to gain insight into this, but I am not certain what vba code I should use to open each document using the repsective hyperlink. I have looked for an existing example of something similar with no luck so far. I am very grateful for any guidance.

Thanks,

Sven

 

[Tom Urtis]

Considering that you know which documents you want to open and you will be using VBA anyway, why are you planning to use hyperlinks when you can access the documents with VBA's easier methods of Open (or Connect if you are dealing with databases)?

 

[svendfj]

The documents are retained within an application that only allows access via hyperlinks.
Thanks

 

[Tom Urtis]

Well, you are not providing very much info, so here is all I can suggest. Not knowing...

...where on the worksheet your hyperlinks are

...what document types the hyperlinks lead to, example, other Excel files, or Word documents, or PDF documents

...what you want to do when you access the files


...this macro is one way to generally approach the issue:

Sub Test1()
Dim cellHyp As Hyperlink
For Each cellHyp In ActiveSheet.Hyperlinks
cellHyp.Follow
 
'Do something
 
Next
End Sub

 

[svendfj]

Tom,

Thank you very much! The hyperlinks point to other excel documents. Suppose each row on the Master spreadsheet contains information for a product. Also, suppose Column B contains hyperlinks that provide the one means of accessing Excel Report documents for each product.

A 'For' loop works down the sheet row by row. On each row:
- the hyperlink in Column B is manipulated in order to open the excel Report
- Once the excel Report is opened, that information is used to update the information on that row.
- The VBA code then procedes to the next row

So, I have the 'For' loop VBA code, and I know that the Open statement in general is: Workbooks.Open Filename:= (strPath, strName)

There will be one line of code with 'i' as the 'For' loop index that points to the cell on each row in Column B that contains the hyperlink. I just do not know how to code that line so that, as the value of 'i' increases, the code walks through each row and opens the document using the hyperlink on that row.

Thanks again,

Sven