vba macro to print all files in a folder

[maalleh]

I have been searching for a macro that will print the first two worksheets in all .xls files in a specific folder. Then close files without saving.
I found a macro and tried to edit it to suit my needs without success.
The first line of the actual macro shows "Sub PrintAllWorkbooksInFolder(TargetFolder As String, FileFilter As String)" but if I leave the text between () the macro does not even show up in the macro menu.
--------------------------------------------------------
Sub PrintAllWorkbooksInFolder()
'
' prints all workbooks in a folder that matches the FileFilter
' example: PrintAllWorkbooksInFolder "C:\FolderName", "*.xls"
' example: PrintAllWorkbooksInFolder "C:\FolderName", "Bud*.xls"
'
Dim TargetFolder As String, FileFilter As String
Dim fn As String
Application.ScreenUpdating = False
If Right(TargetFolder, 1) <> Application.PathSeparator Then
TargetFolder = TargetFolder & Application.PathSeparator
End If
If FileFilter = "" Then FileFilter = "*.xls"
fn = Dir(TargetFolder & FileFilter) ' the first file name in the folder
While Len(fn) > 0
If fn <> ThisWorkbook.Name Then
Application.StatusBar = "Printing " & fn & "..."
Workbooks.Open TargetFolder & fn
' print a specific sheet or chart
Worksheets(1).PrintOut ' prints the first worksheet in the workbook
Worksheets(2).PrintOut ' prints the second worksheet in the workbook
ActiveWorkbook.Close False
' close the workbook without saving any changes
End If
fn = Dir ' the next file name in the folder
Wend
Application.StatusBar = False
End Sub
--------------------------------------------------------
As shown above the macro does not run.
Any help would be greatly appreciated.
Thanks
maalleh

 

[John_w]

As it shows in the comments, you're meant to call it from another macro with the required arguments, e.g.:

Sub PrintWorkbooksInMyDocuments()

    PrintAllWorkbooksInFolder "C:\Documents and Settings\user\My Documents", "My*.xls"

End Sub

Sub PrintAllWorkbooksInFolder(TargetFolder As String, FileFilter As String)

' prints all workbooks in a folder that matches the FileFilter
' example: PrintAllWorkbooksInFolder "C:\FolderName", "*.xls"
' example: PrintAllWorkbooksInFolder "C:\FolderName", "Bud*.xls"
'
    Dim fn As String
    Dim wb As Workbook
    Application.ScreenUpdating = False
    If Right(TargetFolder, 1) <> Application.PathSeparator Then
        TargetFolder = TargetFolder & Application.PathSeparator
    End If
    If FileFilter = "" Then FileFilter = "*.xls"
    fn = Dir(TargetFolder & FileFilter) ' the first file name in the folder
    While Len(fn) > 0
        If fn <> ThisWorkbook.Name Then
            Application.StatusBar = "Printing " & fn & "..."
            Workbooks.Open TargetFolder & fn
            ' print a specific sheet or chart
            Worksheets(1).PrintOut ' prints the first worksheet in the workbook
            Worksheets(2).PrintOut ' prints the second worksheet in the workbook
            
            ActiveWorkbook.Close False
            ' close the workbook without saving any changes
        End If
        fn = Dir ' the next file name in the folder
    Wend
    Application.StatusBar = False
    
End Sub

Seems to work for me.

 

[maalleh]

Thanks John.
You are a lifesaver.
Is there a quick and easy way to ask for user input for the file path?
Thanks again.
maalleh

 

[John_w]

Sub PrintWorkbooks()

    Dim folder As String
    
    folder = InputBox("Enter folder")
    PrintAllWorkbooksInFolder folder, "*.xls"

End Sub

If you want the user to be able to browse for the folder rather than type it in, see http://www.cpearson.com/excel/BrowseFolder.aspx