Open or print files in VBA
February 17, 2010 • JP • 5 Comments • Rate This Article
• Links to this article
I've previously posted code for opening any email attachment. See Open Any Email Attachment From Outlook. Note that this technique works for any file and in any VBA environment; they don't have to be email attachments for you to open them.
But you can open OR print any file using the ShellExecute API, and it also works anywhere. The following function can do this.
Public Enum actionType
openfile
printfile
End Enum
Public Const SW_SHOWNORMAL As Long = 1
Public 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
Function ExecuteFile(fileName As String, action As actionType)
' action can be either "Openfile" or "Printfile".
Dim sAction As String
Select Case action
Case 0 ' openfile
sAction = "Open"
Case 1 ' printfile
sAction = "Print"
End Select
ShellExecute 0, sAction, fileName, vbNullString, "", SW_SHOWNORMAL
End Function
The Enum section delimits how you can call the function, so the API will always be called correctly.
Sample usage
To call the ExecuteFile function, just pass in the filename and the action you want to perform (either open or print). If using this in Outlook, you need to save the file first before opening it. And there's no way to customize the output; you'll need to use the object model for the given file for that. This is just a quick way to open or print any file.
' open a file
ExecuteFile "C:\MyFile.xls", openfile
' print another
ExecuteFile "C:\MyPDF.pdf", printfile
End Sub
Now the challenge becomes: how to close the file? 
VBA ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Excel worksheet interactivity with ActiveX controls
Next Post: Display New Mail Desktop Alerts for specific senders
Very clever JP. I like this one.
Did you ever figure out how to close the file? That is really making my life suck right now, if I could close it all would be well with the world.
No deal, but I'm sure you could use some Windows API to grab the window and close it that way. Exactly how, escapes me.
Nice work, thanks for sharing!
How do I print with the default printer without prompting for any user interaction?
That's what the above method should do.