Printing Macro

[shickles]

Is their a simple macro that will print files from a directory? I have 20 files I change on a daily basis and would like to have a macro I can call when I am done that will print all the files. I hope this can be done.

Thanks....

[Zack Barresse]

Hi shickles,

For clarification, do you want to print ALL files in a specific directory? And if so, how will you specify that directory? Please explain so the coder's have a good idea of what your desired results are.

[shickles]

All the files are in one directory on our network. If I could be prompted for the directory that would be great. If I need to code the directory in I could do that as well.

[Jacob Hilderbrand]

Try something like this:

[VBA]
Option Explicit

Private Sub PtintAllFiles()

Dim WB As Workbook
Dim WS As Worksheet
Dim FileName As String
Dim Path As String
Dim Prompt As String
Dim Title As String
Dim MyResponse As VbMsgBoxResult

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False

'*** Get folder from user ***

Prompt = "Select the folder with the files that you want to print."
Title = "Folder Selection"
MsgBox Prompt, vbInformation, Title
Application.FileDialog(msoFileDialogFolderPicker).Show
Path = CurDir

'*** Confirm the procedure before continuing ***

Prompt = "Are you sure that you want to print all the files in the folder:" & _
vbCrLf & Path & " ?"
Title = "Confirm Procedure"
MyResponse = MsgBox(Prompt, vbQuestion + vbYesNo, Title)
If MyResponse = vbNo Then GoTo Canceled:

'*** Print all Excel files in the specified directory ***

FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
Workbooks.Open Path & "\" & FileName
Set WB = ActiveWorkbook
For Each WS In WB.Worksheets
WS.PrintOut
Next
WB.Close
FileName = Dir()
Loop

Canceled:

Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub
[/VBA]

[Zack Barresse]

Nice one Jake! The only thing I'd change (and I know, I'm no critic) is add a false after the close in your file open/print loop ...

[vba] WB.Close False[/vba]

This will not prompt you for printing. I realize the enable events have been turned off, but it's good habit (imho). Sorry to barge in.

[tommy bak]

Nice Jake...
Just one question
[VBA]
Application.FileDialog(msoFileDialogFolderPicker).Show
[/VBA]

Am I missing a reference or is this only for XP ->
(running win2000 and Office2000 at work)

Br
Tommy Bak

[Jacob Hilderbrand]

Yeah, I believe it is Excel 2002 or later only. To do this with a previous version it is much more trickier. But with API we can do it. Credit for this next bit of code goes to Chip Pearson.

[VBA]
Option Explicit

Private Const BIF_RETURNONLYFSDIRS As Long = &H1
Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
Private Const BIF_RETURNFSANCESTORS As Long = &H8
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260

Type BrowseInfo
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszINSTRUCTIONS As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Type SHFILEOPSTRUCT
hwnd As Long
wFunc As Long
pFrom As String
pTo As String
fFlags As Integer
fAnyOperationsAborted As Boolean
hNameMappings As Long
lpszProgressTitle As String
End Type

Declare Function SHGetPathFromIDListA Lib "shell32.dll" ( _
ByVal pidl As Long, _
ByVal pszBuffer As String) As Long
Declare Function SHBrowseForFolderA Lib "shell32.dll" ( _
lpBrowseInfo As BrowseInfo) As Long

Function BrowseFolder(Optional Caption As String = "") As String

Dim BrowseInfo As BrowseInfo
Dim FolderName As String
Dim ID As Long
Dim Res As Long

With BrowseInfo
.hOwner = 0
.pidlRoot = 0
.pszDisplayName = String$(MAX_PATH, vbNullChar)
.lpszINSTRUCTIONS = Caption
.ulFlags = BIF_RETURNONLYFSDIRS
.lpfn = 0
End With
FolderName = String$(MAX_PATH, vbNullChar)
ID = SHBrowseForFolderA(BrowseInfo)
If ID Then
Res = SHGetPathFromIDListA(ID, FolderName)
If Res Then
BrowseFolder = Left$(FolderName, InStr(FolderName, _
vbNullChar) - 1)
End If
End If

End Function

Sub GetFolder()

Dim FName As String

FName = BrowseFolder("Select A Folder")
If FName = "" Then
MsgBox "You didn't select a folder"
Else
MsgBox "You selected: " & FName
End If

End Sub

[/VBA]

Copy/Paste all of the code into a module and run the macro Sub GetFolder()

[Jacob Hilderbrand]

Now I suppose I should combine my original code with this API method for getting the folder:

[VBA]
Option Explicit

Private Const BIF_RETURNONLYFSDIRS As Long = &H1
Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
Private Const BIF_RETURNFSANCESTORS As Long = &H8
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260

Type BrowseInfo
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszINSTRUCTIONS As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Type SHFILEOPSTRUCT
hwnd As Long
wFunc As Long
pFrom As String
pTo As String
fFlags As Integer
fAnyOperationsAborted As Boolean
hNameMappings As Long
lpszProgressTitle As String
End Type

Declare Function SHGetPathFromIDListA Lib "shell32.dll" ( _
ByVal pidl As Long, _
ByVal pszBuffer As String) As Long
Declare Function SHBrowseForFolderA Lib "shell32.dll" ( _
lpBrowseInfo As BrowseInfo) As Long

Function BrowseFolder(Optional Caption As String = "") As String

Dim BrowseInfo As BrowseInfo
Dim FolderName As String
Dim ID As Long
Dim Res As Long

With BrowseInfo
.hOwner = 0
.pidlRoot = 0
.pszDisplayName = String$(MAX_PATH, vbNullChar)
.lpszINSTRUCTIONS = Caption
.ulFlags = BIF_RETURNONLYFSDIRS
.lpfn = 0
End With
FolderName = String$(MAX_PATH, vbNullChar)
ID = SHBrowseForFolderA(BrowseInfo)
If ID Then
Res = SHGetPathFromIDListA(ID, FolderName)
If Res Then
BrowseFolder = Left$(FolderName, InStr(FolderName, _
vbNullChar) - 1)
End If
End If

End Function

Private Sub PtintAllFiles()

Dim WB As Workbook
Dim WS As Worksheet
Dim FileName As String
Dim Path As String
Dim Prompt As String
Dim Title As String
Dim MyResponse As VbMsgBoxResult

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False

'*** Get folder from user ***

Prompt = "Select the folder with the files that you want to print."
Title = "Folder Selection"
MsgBox Prompt, vbInformation, Title
Path = BrowseFolder("Select A Folder")

'*** Confirm the procedure before continuing ***

Prompt = "Are you sure that you want to print all the files in the folder:" & _
vbCrLf & Path & " ?"
Title = "Confirm Procedure"
MyResponse = MsgBox(Prompt, vbQuestion + vbYesNo, Title)
If MyResponse = vbNo Then GoTo Canceled:

'*** Print all Excel files in the specified directory ***

FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
Workbooks.Open Path & "\" & FileName
Set WB = ActiveWorkbook
For Each WS In WB.Worksheets
WS.PrintOut
Next
WB.Close
FileName = Dir()
Loop

Canceled:

Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub
[/VBA]

[shickles]

OKay, I am lost. Which one of these would I use for Excel 2003? How do I call the macro?

[lucas]

Shickles,

You are very lucky to have Jacob helping you. I admire his skills. What I did to be able to call the macro was change:

[VBA]Private Sub PtintAllFiles()
[/VBA]
to

[VBA]Sub PtintAllFiles()
[/VBA]

then you can call it normally or assign it to a button.
The last code that Jacob posted worked for me in Excel 2000. I also noticed that it does not print files in sub directories which is probably what you want. Another thing, I found that you should not try to print the directory that the file with the print macro is in.

I also commented out

[VBA] 'WS.PrintOut[/VBA]
and put in:

[VBA]WS.PrintPreview[/VBA]
for testing the script. You can change it back when you get it working.