Programmatically inserting hyperlinks

  • Thread starter Thread starter FrigidDigit
  • Start date Start date
F

FrigidDigit

Hi Everyone

I have a list of files generated via VBA in a sheet. I would like to
automatically generate a hyperlink to each file (The full path is generated
in the existing code) and place this hlink in the last column for each file.

Can this easily be done?

Any help will be much appreciated.

FD
 
Turn on the macro recorder (tools=>Macro=>Record a New Macro) while you
insert a hyperlink manually. Then turn off the macro recorder Then modify
this recorded code to use the information in your cell and also to loop
over all the rows you want to process.

Other alternatives include using the Hyperlink worksheet function. Look in
Excel Help.
 
Thanks Tom,

I tried the macro recorder route, but but the resulting code did not show
the full path. Do i only need to specify the filename or should it be
concatenated with the path?

FD
 
I would put in the full path if the workbook will be closed. Recording the
code is just a start. It gives you the format for the commands.
 
Thanks for the help Tom
I have figured out that using the Worksheet Hyperlink function from VBA is
not possible according to the online help so I am trying to write VBA code
to accomplish this. I have come up with the mess below, but I get a 438
Error ("Object does not support this property or method")

fname = objFolder.Path & "\" & objFile.Name
Addrs = Workbooks("Subcontractor invoices Overview.xls").Worksheets("Invoice
Listing").Cells(eRow, 8).Address
Workbooks(" Subcontractor invoices Overview.xls").Worksheets("Invoice
Listing").Hyperlink.Add.Range (Addrs), fname

Any chance of pointing out the error?
Thanks

FD
 
Or maybe you could just fill that last column with =hyperlink() formulas:

=hyperlink("File:////" & a1)
 
Hi Dave!

Thanks again for your input!
I have a fname variable that contains the full path of the file that I want
to create a link to. However, when I try to create the formula in the cell
I get an error.

Here is the code I'm using:


fname = objFolder.Path & "\" & objFile.Name
Workbooks("RTI Subcontractor invoices Overview.xls").Worksheets("Invoice
Listing").Range(Addrs).Formula = "=Hyperlink(" & fname & ")"

Can you see what I'm doing wrong?

Thanks so much for the help!
FD
 
See your other thread for a suggestion.
Hi Dave!

Thanks again for your input!
I have a fname variable that contains the full path of the file that I want
to create a link to. However, when I try to create the formula in the cell
I get an error.

Here is the code I'm using:

fname = objFolder.Path & "\" & objFile.Name
Workbooks("RTI Subcontractor invoices Overview.xls").Worksheets("Invoice
Listing").Range(Addrs).Formula = "=Hyperlink(" & fname & ")"

Can you see what I'm doing wrong?

Thanks so much for the help!
FD
Click to expand...
 
Here is an example of a hyperlink to an cell in a close workbook

Path: "C:\Data6\"
Workbook: American Express.xls
Worksheet: AMEX
Cell: A1

Sub efg()
ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
Address:="C:\Data6\American Express.xls", _
SubAddress:="AMEX!A1", _
TextToDisplay:="My Hyperlink"
End Sub

You need to design your macro to set up a similar string.
 
Back
Top