Help with creating hyperlink via VBA

[FrigidDigit]

Hi All,

I'm trying to create a hyperlink for each entry in a list of filenames.
Below is the code that I have come up with so far (I know very little about
VBA)
fname = objFolder.Path & "\" & objFile.Name
Addrs = Workbooks("Subcontractor invoices Overview.xls").Worksheets("Invoice
Listing").Cells(eRow, 8).Address
MyFormula = "=Hyperlink(" & fname & ")"
Workbooks("Subcontractor invoices Overview.xls").Worksheets("Invoice
Listing").Range(Addrs).Value = MyFormula

When I try to run this it gives me a runtime error 1004 Application defined
or object defined error on the last line above.

Any help would be much appreciated.

FD

 

[Dave Peterson]

Shouldn't this:

MyFormula = "=Hyperlink(" & fname & ")"
look more like:

MyFormula = "=Hyperlink(""" & fname & """)"
or
MyFormula = "=Hyperlink(" & chr(34) & fname & Chr(34) & ")"

 

[FrigidDigit]

Dave,

what do the """" do?

FD

Shouldn't this:

MyFormula = "=Hyperlink(" & fname & ")"
look more like:

MyFormula = "=Hyperlink(""" & fname & """)"
or
MyFormula = "=Hyperlink(" & chr(34) & fname & Chr(34) & ")"

 

[FrigidDigit]

Dave you are a genius!!
Thank you so much!!

Dave,

what do the """" do?

FD

 

[Dave Peterson]

I take it that it worked!!

Glad you got it going.

 

[FrigidDigit]

Worked like a bomb!

Could you explain to me wht """ was used?

Thanks again.

FD

 

[Tom Ogilvy]

It puts in a double quote within a string.

You can see this by examining it from the immediate window:

fname = "Goofy"
? "=Hyperlink(""" & fname & """)"
=Hyperlink("Goofy")


--
Regards,
Tom Ogilvy

Worked like a bomb!

Could you explain to me wht """ was used?

Thanks again.

FD

 

[FrigidDigit]

Thanks Tom

FD

It puts in a double quote within a string.

You can see this by examining it from the immediate window:

fname = "Goofy"
? "=Hyperlink(""" & fname & """)"
=Hyperlink("Goofy")

 

[Dave Peterson]

Sorry, I thought you saw the quote marks when you looked at the formula in the
cell.