Opening a PDF from VBA

Several years ago I needed to open the newest CSV file from a particular directory. Now I’m faced with a similar problem. My accounting system produces PDFs in some kind of proprietary PDF reader. It doesn’t have near the feature set of Foxit, my preferred reader. Instead of fighting it, now I immediately save the pdf and open it in Foxit.

Drawing from that previous post, I made a function to find the name of the most recently created PDF.

Function GetNewestPDFFileName()

Dim fso As Scripting.FileSystemObject
Dim fsoFile As Scripting.File
Dim fsoFldr As Scripting.Folder
Dim dtNew As Date, sNew As String

Const sTYPE As String = " PDF "
Const sFLDR As String = "\\99991-dc01\99991\dkusleika\My Documents\"

Set fso = New Scripting.FileSystemObject
Set fsoFldr = fso.GetFolder(sFLDR)

For Each fsoFile In fsoFldr.Files
If fsoFile.DateCreated > dtNew And InStr(1, fsoFile.Type, sTYPE) > 0 Then
sNew = fsoFile.Path
dtNew = fsoFile.DateCreated
End If
Next fsoFile

GetNewestPDFFileName = sNew

End Function

Once I have the name, a simple FollowHyperlink method will get me where I want to go. Oh, except that hyperlinks are bad and Excel needs to show me a warning. That’s not going to work. Instead, I take the long way around. I create a batch file to open the PDF and run that.

Sub OpenNewestPDF()

Dim sNew As String
Dim sFile As String, lFile As Long

sNew = GetNewestPDFFileName

sFile = "\\99991-dc01\99991\dkusleika\My Documents\OpenPDF.bat"
lFile = FreeFile
Open sFile For Output As lFile
Print #lFile, "K:" & vbNewLine & "start " & Dir(sNew)
Close lFile

'ThisWorkbook.FollowHyperlink snew
Shell sFile

End Sub

The file opens and there’s no warning to click through. Coincidentally, JW has been working around that same security measure, only for MP3 files. There’s an interesting approach.

Sub OpenNewestPDF2()

Dim oleo As OLEObject
Dim sFile As String

sFile = GetNewestPDFFileName

Set oleo = wshPdf.OLEObjects.Add(, sFile, True)

End Sub

It works and no message. Good one John.

Finally, I wanted a third method. fzz commented that I should use a console command because that’s what consoles are good at. I made a batch file following his example:

for /F %%a in ('dir /b/o-d "K:*.pdf"') do (start %%a & exit)

No warnings, obviously, and even though the VBA above is lightning quick, I think we can all appreciate that this is the quickest and most direct way. I’m having a problem running from VBA though.

Sub OpenNewestPDF3()

Shell "K:\OpenPDF2.bat"
'ShellExecute 0, "OPEN", "K:\OpenPDF2.bat", "", "", 0

End Sub

It got the file name right, but says it can’t find it. As you can see, I tried ShellExecute too. Same result. It doesn’t matter. I have a batch file, so I don’t need Excel. I put a shortcut to the batch file on my desktop and set the shortcut key. Now I can open it regardless of which applications are open or have the focus.

4 thoughts on “Opening a PDF from VBA

  1. Sub M_snb()
    CreateObject("shell.application").Namespace("G:\OF\").Items.Item(Split(CreateObject("").exec("cmd /c Dir G:\OF\*.pdf /b /o-d").stdout.readall, vbCrLf)(0)).InvokeVerb "Open"
    End Sub

  2. I have to amend into
    Sub M_snb()
    c00 = Split(CreateObject("").exec("cmd /c Dir G:\OF\*.pdf /b /o-d").stdout.readall, vbCrLf)(1)
    CreateObject("shell.application").Namespace("G:\OF\").Items.Item(c00).InvokeVerb "Open"
    End Sub

  3. Lightly tested…

    Once you have the filename of interest…

    ShellExecute 0&, vbNullString,{filename including path}, _
    vbNullString, vbNullString, vbNormalFocus

  4. Quite possibly — and I am far from an expert in command line use — the reason that the “console command” does not work with the Shell statement is that Dir returns only the file name (not the full name including the path). You have to do change drives and do a chdir or append the path to the %%a in the “start %%a…”

Leave a Reply

Your email address will not be published. Required fields are marked *