Open pdf from a cell

[rpick60]

I would like to open a pdf file on m disk drive from a cell. I can get
hyperlink to work but the program that generates the pdf adds #1 to
the file if it has been revised. So I have file like this

C:\testfile.pdf ( which works)
C:\testfile#1.pdf (does not wotk with hyperlink)

=HYPERLINK("C:\testfile.pdf","open file") this will work

=HYPERLINK("C:\testfile#1.pdf","open file") this will not work

The problem is the "#" character.
Hyperlink does not like wildcards or I am not using the right format.

I have over 500 files and more than half hve the #1 or #2 or #3

Is ther another way to open the file other than hyperlink?

Any ideas? This is the 3rd excel group I posted the question to with
no answers.

I would appreciate any help on this.

Thanks

 

[Dave Peterson]

Could you use a macro?

This worked for me with xl2003 and WinXP Home:

Dim myFileName As String
myFileName = "c:\my documents\excel\test#1.pdf"
Shell Environ("comspec") & " /c " & Chr(34) & myFileName & Chr(34), vbHide

Maybe you could plop a button from the Forms toolbar on row 1 of your
worksheet. Then use window|freeze panes to make sure that button is always
visible.

Then tell the user to select the cell with the pdf filename in it and click the
button.

You can assign this macro to the button:

Option Explicit
Sub testme()

Dim myFileName As String
Dim TestStr As String

myFileName = ActiveCell.Value

If myFileName = "" Then
Beep
Else
TestStr = ""
On Error Resume Next
TestStr = Dir(myFileName)
On Error GoTo 0

If TestStr = "" Then
'file wasn't found
Beep
MsgBox "That file wasn't found"
Else
Shell Environ("comspec") _
& " /c " & Chr(34) & myFileName & Chr(34), vbHide
End If
End If

End Sub

The Shell command opens a Command window that starts the program associated with
the .pdf extension (for me Adobe Reader).

The /c says to close that (hidden!) window when it's done (after the user closes
the .pdf file).

Change /k to see the command window (/k = keep open???).

 

[Dave Peterson]

ps. I'd go back to the developer and ask them to change that # to an underscore
or hyphen.