Excel - VBA to open a PDF, print, then close - Stumped

Asked By Marc T on 28-Apr-10 11:42 AM
Hi all,

I have the following code that opens a PDF and prints (as well as a cuople
of excel files). That part works perfectly, but how would I go about closing
the PDF after it prints?

Sub RunLoop()

Dim Folder As String
Dim FName As String
Dim bk As Workbook
Dim strPath As String

strPath = ThisWorkbook.Path

With Application.FileSearch
.SearchSubFolders = True
.LookIn = strPath
.FileType = msoFileTypeAllFiles

If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count

If .FoundFiles(i) Like "*File 1*.xls" Then
Workbooks.Open .FoundFiles(i)
Call PrintMacro
ElseIf .FoundFiles(i) Like "*File 2*.xls" Then
Workbooks.Open .FoundFiles(i)
Call PrintMacro2
ElseIf .FoundFiles(i) Like "*File 4*.pdf" Then
ActiveWorkbook.FollowHyperlink .FoundFiles(i), NewWindow:=True
Application.SendKeys "^p~", False

End If

Next i
Else
MsgBox "There were no files found."
End If

End With
End Sub




thanks as ever!
Marc




Marc T replied to Marc T on 29-Apr-10 04:46 AM
Just a quick update (in case anyone else has a similar problem)...

I managed to answer this myself through trial and error. I changed the code
as below using SendKeys to send the equivalent of Alt-F4 and seems to work a
treat! I added the 5 second waiting time to make sure one document is allowed
to print/close before the next is opened.

Sub RunLoop()

Dim Folder As String
Dim FName As String
Dim bk As Workbook
Dim strPath As String

strPath = ThisWorkbook.Path

With Application.FileSearch
.SearchSubFolders = True
.LookIn = strPath
.FileType = msoFileTypeAllFiles

If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count

If .FoundFiles(i) Like "*File 1*.xls" Then

Workbooks.Open .FoundFiles(i)
Call PrintMacro

ElseIf .FoundFiles(i) Like "*File 2*.xls" Then
Workbooks.Open .FoundFiles(i)
Call PrintMacro2

ElseIf .FoundFiles(i) Like "*.pdf" Then
ActiveWorkbook.FollowHyperlink .FoundFiles(i), NewWindow:=True
Application.SendKeys "^p~", False
Application.SendKeys "%{F4}", False
Application.Wait (Now + TimeValue("0:00:05"))

End If

Next i
Else
MsgBox "There were no files found."
End If

End With
End Sub