Programmatically inserting hyperlinks
 

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

Programmatically inserting hyperlinks
 

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.

--
Regards,
Tom Ogilvy


"FrigidDigit" wrote in message
...
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

Programmatically inserting hyperlinks
 

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
"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"FrigidDigit" wrote in message
...
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

Programmatically inserting hyperlinks
 

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.

--
Regards,
Tom Ogilvy


"FrigidDigit" wrote in message
...
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
"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"FrigidDigit" wrote in message
...
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

Programmatically inserting hyperlinks
 

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

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"FrigidDigit" wrote in message
...
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
"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"FrigidDigit" wrote in message
...
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

Programmatically inserting hyperlinks
 

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

=hyperlink("File:////" & a1)



FrigidDigit wrote:

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

--

Dave Peterson

Programmatically inserting hyperlinks
 

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

"Dave Peterson" wrote in message
...
Or maybe you could just fill that last column with =hyperlink() formulas:

=hyperlink("File:////" & a1)



FrigidDigit wrote:

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

--

Dave Peterson

Programmatically inserting hyperlinks
 

See your other thread for a suggestion.

FrigidDigit wrote:

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

"Dave Peterson" wrote in message
...
Or maybe you could just fill that last column with =hyperlink() formulas:

=hyperlink("File:////" & a1)



FrigidDigit wrote:

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

--

Dave Peterson

--

Dave Peterson

Programmatically inserting hyperlinks
 

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.



--
Regards,
Tom Ogilvy

"FrigidDigit" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"FrigidDigit" wrote in message
...
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
"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy


"FrigidDigit" wrote in message
...
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