Running a PDF file from Excel

I have an Excel 2007 worksheet (xlsm) that is a text-only flat data-base (i.e. no spreadsheet functionality - just text in each cell). I've written a VBA procedure with a userform that creates a standard user interface to display the contents of any selected row.

Sitting beside the xlsm file is a folder of PDF files called: '##Filestore'. Some of the cells in the worksheet contain names of PDF files held in that folder.

When I see one of these filenames I want to be able to click on the adjacent label and display the contents of the file - e.g. by firing up the default PDF player such as Acrobat, much as one would by clicking directly on the file itself.

I can do this successfully (using different code) when the file is a JPG, or when the cell contains a URL, but I can't get it to display the PDF. Below is the code for the subroutine that was supposed to handle the PDF label click event:

Code:

---

Private Sub Label12_Click()
    Dim Link As String
    Link = ThumbnailFile.Text                                        'ThumbnailFile.Text is the PDF filename
    Link = ActiveWorkbook.Path & "\##Filestore\" & Link    'Link now holds full path to the file
    On Error GoTo NoCanDo
Viewit: ActiveWorkbook.FollowHyperlink Address:=Link, NewWindow:=True
    Exit Sub
   
NoCanDo: MsgBox "Cannot open " & Link
 
End Sub

---

However, when I run this is it generates my error exit message. The path shown in the error message seems to be correct, so it looks as if: 'ActiveWorkbook.FollowHyperlink' cannot run a PDF.

So how can one run a PDF from VBA in Excel 2007 (under Vista)?

Any advice gratefully received!

Hi John
This code will open a pdf file. See if you can adapt it to suit your needs
On Excel 2010 it does prompt a couple of security nags that I'm not sure if you can eliminate. I haven't looked very hard
Cheers

Code:

---

Sub OpenHlink()

Dim PDF2Open As String, FolderPath As String, File2OpenPath As String

    PDF2Open = "Coconut Bread Recipe.pdf" ' Name of pdf file to open
    FolderPath = "C:\Users\" & Environ("username") & "\Desktop\" ' Path to Desktop
    File2OpenPath = FolderPath & PDF2Open ' Folder path & name of the file to open
           
    ThisWorkbook.FollowHyperlink File2OpenPath  ' Open the file
                       
Exit Sub

End Sub

---

You can get rid of one of the nags by putting the pdf in a trusted folder & then set that folder as a trusted location in the trust centre

Thanks, PhilB

Your code example confirmed that my code was actually correct, and I already knew that the file path was correct, so I began to wonder if VBA was having some problems with the path labels, since I had used quite long labels that included characters such as '#', '-', and 'space'. So I did some experiments.

Path length (up to 160 characters) and use of '-', and 'space' seemed to be OK.

However, it turns out that '#' is a problem. Though Vista is happy with '#' in file or folder names, it seems that in Excel VBA '#' acts as some sort of 'end-of-path' marker for folder names (file names seem OK). So if '#' is the first character of a folder name, that folder and anything to the right of it 'disappears' and you end up opening the folder to its left. If '#' is at some other point in the folder name, that name becomes unreadable and you get a fault message. So I solved my problem by using '%' rather than '#' in my folder names.

Once I got the code to work, your issue about 'Trusted folders' did indeed become an issue - I got warning messages about the dangers of running unknown files and had to agree to proceed. I can deal with this on my own system, but I would like to be able to send my XLSM file and it associated folder of PDFs to others to use, so: #### do you know any way of avoiding any recipient having to cope with that hassle?####

Many thanks for your help

I don't see the need to use other folders than the regular ones:

Code:

---

Sub M_snb()
  MsgBox Application.DefaultFilePath
End Sub

---

Quote:

---

Originally Posted by snb

I don't see the need to use other folders than the regular ones:

Code:

---

Sub M_snb()
  MsgBox Application.DefaultFilePath
End Sub

---

---

Thanks, snb

You've lost me! The Folders I was using were normal Vista folders. My Excel worksheet included names of files held in these folders, and I wanted my VBA to run those files.

Since I have lots of new and old working folders I find it useful to mark the key ones so that they stand out visually in file lists, and get sorted to the top of the list. I was doing that by marking them with '##'. I was also using quite complex filenames to a standard format that conveyed a lot of what was in the file. As a result I ended up with paths like this:

Code:

---

C:\Users\Owner\Desktop\##DRAMSOC AREA/##Compiling 1965-1970\##64-5 to 72-3\##Filestore\1964-11-12-St-p1-SavoyOpera+FaragoReview+PicnicOnBattlefield-Raw-070.pdf

---

But it turned out that though Vista is happy with '##', Excel 2007 VBA is not! So I've replaced '##' with '%%' and all is (I hope!) well!

The more you restrict to 0-9, a-z, A-Z and incidentally an underscore the smoother VBA.
If you are familiar with wildcards you don't need exotic pathnames and/or filenames
e.g.

Code:

---

Sub M_snb()
  with application.filedialog(1)
    .initialfilename= Application.DefaultFilePath & "\*Picnic*.pdf"
    if .show then thisworkbook.followhyperlink .selecteditems(1)
  end with
End Sub

---

Quote:

---

Originally Posted by snb

The more you restrict to 0-9, a-z, A-Z and incidentally an underscore the smoother VBA.
If you are familiar with wildcards you don't need exotic pathnames and/or filenames
e.g.

Code:

---

Sub M_snb()
  with application.filedialog(1)
    .initialfilename= Application.DefaultFilePath & "\*Picnic*.pdf"
    if .show then thisworkbook.followhyperlink .selecteditems(1)
  end with
End Sub

---

---

Thanks, snb.

Point taken about non-alpha-numeric characters.

Re. your code example, I'll need to spend a while getting my head round it. I used to be pretty confident, way back, on the old procedural languages, but my grasp of modern object-oriented code is still very, very, basic, and largely trial and error. But your suggestion looks interesting so I'll persevere and I'll see what I can learn from it!

Thanks!

I created a pdf with VBA & then tried to open it with VBA, but it seems even with the trusted location, you can't get rid of the the office security nag.
Instead of using ### & the possible problems it could bring, why not use ZZZ, or 000 or similar.

Quote:

---

Originally Posted by PhilB

I created a pdf with VBA & then tried to open it with VBA, but it seems even with the trusted location, you can't get rid of the the office security nag.
Instead of using ### & the possible problems it could bring, why not use ZZZ, or 000 or similar.

---

Thanks, PhilB

You've beaten me to it re.testing the trust issue - thanks for letting me know. It is not too much of a hassle for me personally, because I know what is happening, but it would be annoying if I tried sending the file to someone else - lots of explaining, etc.

I'm not sure if snb's suggestion would bypass the Trusted Folder issue. I'll need to explore it.

Re. the ### problem, yes, I switched to %%% which worked OK, but obviously non-alpha-numerics are a bit iffy!