Open or print files in VBA

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.

' adapted from Access 2003 VBA Programmer's Reference
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.

Sub TestPrint()
  ' 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? :-?

About JP

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 to learn more about VBA. Keep Reading »


Related Articles:


Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 10 Comment(s) on Open or print files in VBA:

  1. Very clever JP. I like this one.

  2. 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.

  3. M Cabilin writes:

    Nice work, thanks for sharing!
    How do I print with the default printer without prompting for any user interaction?

  4. chrisham writes:

    What a timesaver! Added that code to a regular routine! Now the whole process is lightening fast. On a different note JP, as a Purchaser, I receive attachment with Quotations. I have figured a way how to save the attachments to a particular folder, but incase the Sender has some fancy pictures included in his Email Signature or Email Body, then this gets stored as an attachment too. Is there anywhere where I can have only the Selected file to be stored in the folder?.
    Currently I have a Routine that Grabs all the files from this Folder containing my Quotations. This piece of code allows me print out the Quotations without opening these files Manual individually and then manually printing them. Thanks for sharing the code!

  5. chrisham writes:

    Sorry, I should add that I use MS Outlook 2010, as my email client!

  6. Hi JP,

    I'm new to VBA and I need to do something like what you've posted in my macro. I'm wondering whether it is possible for me to specify my printer name in this code, since it is a network printer?

    Basically, what I need to do is to open a freshly created PDF file from a folder, then print this pdf file to my network printer. Can you pls pls help me? THANKEW!! :)

  7. Hej,
    This works great but if i print multiple files faster than adobe can send them to the printer they come out in random order from the printer.
    I have read all pdf-files from a folder structure into excel and then put them in the order i want them. I wold not like to sort 100+documents after print.
    I added a delay betwen prints but it is not a very nice sulution and takes lots of time. Can i somehow check if adobe is ready to recive the next job?

This article is closed to any future comments.
Peltier Tech Charting Utilities for Excel