Opening Files From Spreadsheet using VBA

Hi there,

I have a piece of code that I use to open up files (in this case, a pdf file) on my hard drive, however it opens it up and displays the file as an image, rather than as a pdf file via the Adobe program - see code below:

Sub OpenFile()

     Sheets("Sheet1").Select
     Dim FName As String
      
     FName = "C:\" & Range("D14") & ".pdf"
      
     ActiveSheet.OLEObjects.Add(Filename:=FName, _
     Link:=False, _
     DisplayAsIcon:=False).Select
End Sub

I would like help on two matters here:
1. How can I open the file via Adobe (or similar) rather than as an image?
2. How do I get the program to search using a different directory (rather than C:\ drive), as I plan on sending out the completed Excel file to customers on a CD or thumb drive. Not know what my customer's directory name will be makes this piece of code redundant apart from on my machine where I know I keep the pdf files.

Hoping that someone can assist me with this enquiry.

Thanks

Mark

Mark,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature.

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
                                      (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
                                       ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Const SW_SHOW            As Long = 5&

Sub OpenFile()
     Dim FName As String
     FName = "C:\" & Range("D14") & ".pdf"
     ShellExecute Application.hWnd, "Open", FName, 0&, 0&, SW_SHOW
End Sub

oughta work for any registered file type