VBA to Open Hyperlink(Concatenate)

[S1lverface]

Hello All,

I've found useful VBA on this forum for activating my Hyperlink in cell A1
of my Excel spreadsheet : ActiveSheet.Range("A1").Hyperlinks(1).Follow

My Hyperlink in Cell A1 is made up of a Concatenate using cells from
different worksheets. Such as below :

=HYPERLINK((CONCATENATE(HoldPoints!$A$1,HoldPoints!A2),A3)

If I click on it, it opens fine, however, if I try to open it with the vba
code above, I get an error message "Subscript out of range".

I've tried different combinations. It always works by clicking, but not by
the vba code.

However - If i type the Hyperlink in without using concatenate, the code
works fine.... but I need to Concatenate.

Can anyone help.
Thanks.

 

[jaf]

Hi,
Try =HYPERLINK(HoldPoints!$A$1 & HoldPoints!A2,A3)

John

 

[Dave Peterson]

Maybe you can parse the formula and extract the first argument to the
=hyperlinks() worksheet function.

 

[Gary''s Student]

ANY hyperlink instantiated with the =HYPERLINK() function will give the same
error. Even a hyperlink as simple as:

=HYPERLINK("http://www.cnn.com","news")

That is because Inserted hyperlinks live in the Object model. They have all
the Properties and Methods associated with the Hyperlink Object.

Function hyperlinks do not.

 

[S1lverface]

Hi All,

Thanks for the help.

I've found a long winded workaround to get rid of the need to concatenate.
My vba code now opens the hyperlink from its stored location. So thanks again.

The last bit of my problem is, I now need to either : Save that pdf file to
a different location e.g. C:\MyDocuments\xxx.pdf OR rather than open it, go
to where it resides and copy if to where I need it i.e copy the file to
C:\MyDocuments\xxx.pdf

Note, I need the code to recognise and save the file with the same file name.

Thanks again.

 

[S1lverface]

Just found this which I run frm Excel:

Dim SrceFile
Dim DestFile

SrceFile = "g:\jerry\export.pdf"
DestFile = "c:\data\zippedfiles\export.pdf"
FileCopy SrceFile, DestFile

It works as written but I want the code to refer to range A1 or cell(1,1) on
my excel spreadsheet where it will see the file name. The file name will
change depending on other parameters, so i cannot hard code in the file name
itself.

Thanks.

 

[Gary''s Student]

Sub silverface()
Dim SrceFile As String
Dim DestFile As String
Dim fname As String
fname = Range("A1").Value
SrceFile = "g:\jerry\" & fname & ".pdf"
DestFile = "c:\data\zippedfiles\" & fname & ".pdf"
FileCopy SrceFile, DestFile
End Sub

 

[S1lverface]

Lovely job, thanks

Sub silverface()
Dim SrceFile As String
Dim DestFile As String
Dim fname As String
fname = Range("A1").Value
SrceFile = "g:\jerry\" & fname & ".pdf"
DestFile = "c:\data\zippedfiles\" & fname & ".pdf"
FileCopy SrceFile, DestFile
End Sub