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....
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....
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.
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
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.
Try something like this:
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
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 ...
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.WB.Close False
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
Nice Jake...
Just one question
Application.FileDialog(msoFileDialogFolderPicker).Show
Am I missing a reference or is this only for XP ->
(running win2000 and Office2000 at work)
Br
Tommy Bak
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.
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
Copy/Paste all of the code into a module and run the macro Sub GetFolder()
Now I suppose I should combine my original code with this API method for getting the folder:
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
OKay, I am lost. Which one of these would I use for Excel 2003? How do I call the macro?
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:
Private Sub PtintAllFiles()
to
Sub PtintAllFiles()
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
and put in:'WS.PrintOut
for testing the script. You can change it back when you get it working.WS.PrintPreview
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln