Printing different files from a Folder in Excel

[frisfrancis]

Hi Guys,

I am looking if some one could help me with the Excel VBA Code to print multiple files (different types of files) from a specified path or from a specified folder. I actually have my files stored in a particular folder. So I want a VBA Code which would be able to print files from the specified path (irrespective of file type). Waiting for your response.

Thanks,
Francis

[Bob Phillips]

Tricky, because different files need to be opened in different applications, which may not support automation. Excel files easy, Word not too difficult, but the rest gets harder.

[Ivan F Moala]

Have a look @ using the API ShellExecuteA to do the printing

http://www.xcelfiles.com/ShellExecuteA.html

[Simon Lloyd]

Hi, Ivan F Moala posted some code a few years ago tha may get you started, hope it helps!

Sub Tester()
Dim Ret As Long, myAddress1 As String, myAddress2 As String, myPath As String
myAddress1 = ActiveCell.Address
myAddress2 = "$B$" & Right(myAddress1, 1)
myPath = Range(myAddress1).Value + Range(myAddress2).Value
'// Substitute here your Doc full path
Ret = fnShellOperation(myPath, "print", SW_MAXIMIZE)
End Sub
'---------------------------------------------------------------------------------------
' Module : basShellOp
' DateTime : 29/09/03 18:31
' Author : Ivan F Moala
' Purpose : Opens ANY Document/File
'---------------------------------------------------------------------------------------
Option Explicit

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
Public Declare Function GetDesktopWindow Lib "user32" () As Long
Private Const SW_HIDE As Long = 0
Private Const SW_NORMAL As Long = 1
Private Const SW_MAXIMIZE As Long = 3
Private Const SW_MINIMIZE As Long = 6
'//---------------------------------------------------------------------------------------
'// Function : fnShellOperation
'// DateTime : 20/09/03 20:50
'// Author : "Ivan F Moala"
'// Site : "http://www.xcelfiles.com (http://www.xcelfiles.com/)"
'// Purpose : Performs an operation eg Open,Print
'// : a specified file. The file can be an executable file or a document file.
'---------------------------------------------------------------------------------------
Public Function fnShellOperation(strFilePath As String, _
Optional strOperation As String, _
Optional nShowCmd As Double) As Long
Dim hWndDesk As Long
'// use the desktop as default ... you should use your App.handle
hWndDesk = GetDesktopWindow()
If Len(strOperation) = 0 Then strOperation = "Open"
If nShowCmd = Null Then nShowCmd = SW_MAXIMIZE
'// Failure when >0 or <=32
fnShellOperation = ShellExecute(hWndDesk, strOperation, strFilePath, 0, 0, nShowCmd)
If fnShellOperation <= 32 Then
MsgBox "Couldn't " & strOperation & " " & strFilePath & vbCrLf & vbCrLf & _
"Error:= " & fnShellErr(fnShellOperation)
End If
'// OK check IF there was an Association Error
If fnShellOperation = 31 Then
'// OK Ask user if they want to Open it using another program
If MsgBox(strOperation & " Using another Application", vbYesNo) = vbYes Then
Shell "rundll32.exe shell32.dll,OpenAs_RunDLL " & strFilePath, vbNormalFocus
End If
End If
End Function
'//---------------------------------------------------------------------------------------
'// Function : fnShellErr
'// DateTime : 20/09/03 20:50
'// Author : "Ivan F Moala"
'// Site : "http://www.xcelfiles.com (http://www.xcelfiles.com/)"
'// Purpose :
'---------------------------------------------------------------------------------------
Public Function fnShellErr(Ret As Long) As String
Select Case Ret
'// Typical Errors
Case 0: fnShellErr = "The operating system is out of memory or resources."
Case Is = 2: fnShellErr = "The specified FILE was not found."
Case Is = 3: fnShellErr = "The specified PATH was not found."
Case Is = 5: fnShellErr = "The operating system denied access to the specified file."
Case Is = 8: fnShellErr = "There was not enough memory to complete the operation."
Case Is = 11: fnShellErr = "The .EXE file is invalid (non-Win32 .EXE or error in .EXE image)."
Case Is = 26: fnShellErr = "A sharing violation occurred."
Case Is = 27: fnShellErr = "The filename association is incomplete or invalid."
Case Is = 28: fnShellErr = "The DDE transaction could not be completed because the request timed out."
Case Is = 29: fnShellErr = "The DDE transaction failed."
Case Is = 30: fnShellErr = "The DDE transaction could not be completed because other DDE transactions were being processed."
Case Is = 31: fnShellErr = "There is no application associated with the given filename extension."
Case Is = 32: fnShellErr = "The specified dynamic-link library was not found."
Case Else: fnShellErr = "*UNDEFINED* Error"
End Select
End Function

[frisfrancis]

Hi Ivan,

Thanks for the same, I tried it out and i am able to print all attachments except for Excel. and i have modified it to print excel workbooks as well. I am still in the process of testing the same. Will come back to you guys if i still have some more query on the same. Thanks Guys.. Hi Ivan, Also if you could mail me your contact details, it would be great. my mail id is frisfrancis@gmail.com

Regards,
Francis