Prev: A macro for converting formulas to formula results only
Next: Using vba find
From: Marc T on 28 Apr 2010 11:42
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
From: Marc T on 29 Apr 2010 04:46
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

"Marc T" wrote:

> 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
 | 
Pages: 1
Prev: A macro for converting formulas to formula results only
Next: Using vba find