VBA to Open Hyperlink(Concatenate)

S

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.
 
D

Dave Peterson

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

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.
 
S

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.
 
S

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.
 
G

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
 
S

S1lverface

Lovely job, thanks


Gary''s Student said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top