Help with creating hyperlink via VBA

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

Shouldn't this:

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

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



FrigidDigit wrote:
>
> 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

Dave,

what do the """" do?

FD


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Shouldn't this:
>
> MyFormula = "=Hyperlink(" & fname & ")"
> look more like:
>
> MyFormula = "=Hyperlink(""" & fname & """)"
> or
> MyFormula = "=Hyperlink(" & chr(34) & fname & Chr(34) & ")"
>
>
>
> FrigidDigit wrote:
>>
>> 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

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


"FrigidDigit" <[email protected]> wrote in message
news:[email protected]...
> Dave,
>
> what do the """" do?
>
> FD
>
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
>> Shouldn't this:
>>
>> MyFormula = "=Hyperlink(" & fname & ")"
>> look more like:
>>
>> MyFormula = "=Hyperlink(""" & fname & """)"
>> or
>> MyFormula = "=Hyperlink(" & chr(34) & fname & Chr(34) & ")"
>>
>>
>>
>> FrigidDigit wrote:
>>>
>>> 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
>
>

I take it that it worked!!

Glad you got it going.

FrigidDigit wrote:
>
> Dave you are a genius!!
> Thank you so much!!
>
> "FrigidDigit" <[email protected]> wrote in message
> news:[email protected]...
> > Dave,
> >
> > what do the """" do?
> >
> > FD
> >
> >
> > "Dave Peterson" <[email protected]> wrote in message
> > news:[email protected]...
> >> Shouldn't this:
> >>
> >> MyFormula = "=Hyperlink(" & fname & ")"
> >> look more like:
> >>
> >> MyFormula = "=Hyperlink(""" & fname & """)"
> >> or
> >> MyFormula = "=Hyperlink(" & chr(34) & fname & Chr(34) & ")"
> >>
> >>
> >>
> >> FrigidDigit wrote:
> >>>
> >>> 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
> ?
> ?

--

Dave Peterson

Worked like a bomb!

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

Thanks again.

FD


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
>I take it that it worked!!
>
> Glad you got it going.
>
> FrigidDigit wrote:
>>
>> Dave you are a genius!!
>> Thank you so much!!
>>
>> "FrigidDigit" <[email protected]> wrote in message
>> news:[email protected]...
>> > Dave,
>> >
>> > what do the """" do?
>> >
>> > FD
>> >
>> >
>> > "Dave Peterson" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Shouldn't this:
>> >>
>> >> MyFormula = "=Hyperlink(" & fname & ")"
>> >> look more like:
>> >>
>> >> MyFormula = "=Hyperlink(""" & fname & """)"
>> >> or
>> >> MyFormula = "=Hyperlink(" & chr(34) & fname & Chr(34) & ")"
>> >>
>> >>
>> >>
>> >> FrigidDigit wrote:
>> >>>
>> >>> 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
>> ?
>> ?
>
> --
>
> Dave Peterson

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

"FrigidDigit" <[email protected]> wrote in message
news:[email protected]...
> Worked like a bomb!
>
> Could you explain to me wht """ was used?
>
> Thanks again.
>
> FD
>
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> >I take it that it worked!!
> >
> > Glad you got it going.
> >
> > FrigidDigit wrote:
> >>
> >> Dave you are a genius!!
> >> Thank you so much!!
> >>
> >> "FrigidDigit" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Dave,
> >> >
> >> > what do the """" do?
> >> >
> >> > FD
> >> >
> >> >
> >> > "Dave Peterson" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> Shouldn't this:
> >> >>
> >> >> MyFormula = "=Hyperlink(" & fname & ")"
> >> >> look more like:
> >> >>
> >> >> MyFormula = "=Hyperlink(""" & fname & """)"
> >> >> or
> >> >> MyFormula = "=Hyperlink(" & chr(34) & fname & Chr(34) & ")"
> >> >>
> >> >>
> >> >>
> >> >> FrigidDigit wrote:
> >> >>>
> >> >>> 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
> >> ?
> >> ?
> >
> > --
> >
> > Dave Peterson
>
>

Thanks Tom

FD

"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
> "FrigidDigit" <[email protected]> wrote in message
> news:[email protected]...
>> Worked like a bomb!
>>
>> Could you explain to me wht """ was used?
>>
>> Thanks again.
>>
>> FD
>>
>>
>> "Dave Peterson" <[email protected]> wrote in message
>> news:[email protected]...
>> >I take it that it worked!!
>> >
>> > Glad you got it going.
>> >
>> > FrigidDigit wrote:
>> >>
>> >> Dave you are a genius!!
>> >> Thank you so much!!
>> >>
>> >> "FrigidDigit" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Dave,
>> >> >
>> >> > what do the """" do?
>> >> >
>> >> > FD
>> >> >
>> >> >
>> >> > "Dave Peterson" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> Shouldn't this:
>> >> >>
>> >> >> MyFormula = "=Hyperlink(" & fname & ")"
>> >> >> look more like:
>> >> >>
>> >> >> MyFormula = "=Hyperlink(""" & fname & """)"
>> >> >> or
>> >> >> MyFormula = "=Hyperlink(" & chr(34) & fname & Chr(34) & ")"
>> >> >>
>> >> >>
>> >> >>
>> >> >> FrigidDigit wrote:
>> >> >>>
>> >> >>> 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
>> >> ?
>> >> ?
>> >
>> > --
>> >
>> > Dave Peterson
>>
>>
>
>

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

FrigidDigit wrote:
>
> Worked like a bomb!
>
> Could you explain to me wht """ was used?
>
> Thanks again.
>
> FD
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> >I take it that it worked!!
> >
> > Glad you got it going.
> >
> > FrigidDigit wrote:
> >>
> >> Dave you are a genius!!
> >> Thank you so much!!
> >>
> >> "FrigidDigit" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Dave,
> >> >
> >> > what do the """" do?
> >> >
> >> > FD
> >> >
> >> >
> >> > "Dave Peterson" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> Shouldn't this:
> >> >>
> >> >> MyFormula = "=Hyperlink(" & fname & ")"
> >> >> look more like:
> >> >>
> >> >> MyFormula = "=Hyperlink(""" & fname & """)"
> >> >> or
> >> >> MyFormula = "=Hyperlink(" & chr(34) & fname & Chr(34) & ")"
> >> >>
> >> >>
> >> >>
> >> >> FrigidDigit wrote:
> >> >>>
> >> >>> 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
> >> ?
> >> ?
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson