printing all workbooks in a directory and all sheets in each

[Kyle775]

i need to print all workbooks in a directory and i need all the sheets in each book to be included in the print and to print each sheet with its own pagnation (not 1 - n, where n is the total pages fro all work sheets, 1 - n where n is the total number of pages for the sheet that is being printed.) i have tried doing this with the windows explorer and it misses some workbooks and only prints the active sheet. ive looked around and there are some solutions to this but i havent been able to get them to work. can someone point me in the right direction... thanks a lot!

 

[Kyle775]

that seems like a little bit more effecient way, less code anyways.. i need the automation though... some people dont know how to select multiple files.

i was wondering if there is a way to close the browse window without a runtime error..?? ive just noticed this

 

[Norie]

When/how are you getting the runtime error?

 

[Kyle775]

when i run this

Sub PrintAllWS()
Dim i As Long
Dim WB As Workbook
Dim ws As Worksheet

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

    With Application.FileSearch
        .NewSearch
        .LookIn = GetFolder
        .FileType = msoFileTypeExcelWorkbooks
        .Execute

        For i = 1 To .FoundFiles.Count
            Set WB = Workbooks.Open(.FoundFiles(i))
            For Each ws In WB.Worksheets
                 ws.PrintOut
            Next ws
            WB.Close
        Next i
    End With
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub
Private Function GetFolder() As String
'common UDF for getting a folder name
    Dim ff As Object
    Set ff = CreateObject("Shell.Application"). _
             BrowseForFolder(0, "Please select a folder", 0, "c:\\")
    If Not ff Is Nothing Then
        GetFolder = ff.Items.Item.Path
    Else
        GetFolder = vbNullString
    End If
End Function

and decide that i dont want to chose a directory for printing, instead of open i click cancel, i get Runtime error 5, invaild procedure call or argument.

 

[Norie]

Where are you getting the error?

 

[Kyle775]

in excel

i have another question... can this code be modified to print a directory of word documents??

 

[Norie]

Kyle

Which line of code is causing the error?

 

[vane0326]

in excel

i have another question... can this code be modified to print a directory of word documents??

Try this!

Sub Test3()
Dim myFile$
myFile = InputBox _
("Enter the Word document file name:", _
"What File do you wish to open?", _
"YourFileName")
If myFile = "" Then Exit Sub

Dim myPath$, myDoc$
myPath = "C:\" 'Choose file path
myDoc = myPath & myFile & ".doc"

If Dir(myDoc) = "" Then
MsgBox "The file ''" & myDoc & "'' was not found.", 48, "No Doc"
Exit Sub
End If

Application.ScreenUpdating = False
        Application.PrintOut Filename:=Path & FName
Application.ScreenUpdating = True
End Sub

 

[vane0326]

when i run this
and decide that i dont want to chose a directory for printing, instead of open i click cancel, i get Runtime error 5, invaild procedure call or argument.

Then try using this!

Sub PrintAllWS()
Dim i As Long
Dim WB As Workbook
Dim ws As Worksheet
    
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    MyFiles = Application.GetOpenFilename(FileFilter:="Excel files (*.xls), *.xls", MultiSelect:=True)

    On Error Resume Next
    For i = LBound(MyFiles) To UBound(MyFiles)
    If Err = "53" Then
        MsgBox ""
        Set WB = Workbooks.Open(MyFiles(i))
        For Each ws In WB.Worksheets
            ws.PrintOut
        Next ws
        WB.Close
        End If
    Next i
    
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

 

[Kyle775]

thanks for all youe help... i will have to come back to this... what i have works for the printing i need to do.. i will try the code provided but at a later date.. thanks again i have learned so much so quickly!

 

[Norie]

vane0326

Using On Error will work but I prefer to actually find out what the error is, why it is occuring and then fixing the code.