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!

 

[Norie]

Where/when are you getting the error?

GetOpenFilename is actually for getting a filename rather than a folder.

I suppose you could use it somehow, but you would need to select at least one file in the folder.

You would then get the folder name from the filename.

By the way are you sure you couldn't find any code for getting a folder? I'm sure I've seen a few posts for it, I'll have a look myself.

 

[Kyle775]

ive found this thread http://www.mrexcel.com/board2/viewtopic.php?t=110450 for gathering the directory... however i have no idea which lines to use and where to use them in the code that has already been provided..

any my code gives me a type mismatch error at

.LookIn = MyFolder

thanks for making me look this up on my own.. so many times ive been stranded for an answer and people simply give it to me... wont learn that way...

 

[Norie]

Kyle

How are you using the GetOpenFilename method?

Are you navigating to the required folder then pressing cancel instead of selecting a file?

You could still use GetOpenFileName but you must select a file. The file won't actually be opened, the filename will be returned to MyInput.

You could then extract the directory from MyInput.

I'll take a look at the link.

 

[Kyle775]

when i ran the code it would bring up a windows broswe dialoge... i would navigate to the directory i wanted to print and then select all the excel files in it. then i would press open and it would give me the type mis match

 

[Norie]

That't because MyInput is an array containing all those files.

If you right click on MyInput, select Add Watch... and then step through the code with F8 you'll see what I mean.

By the way you've actually discovered another way to do what you want.

You could loop through all the workbooks you have selected instead of using FileSearch.

 

[Norie]

The relevant code you want from the link is this.

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

You could use it like this I think.

.LookIn = GetFolder

 

[Kyle775]

i cant even fix this type mixmatch error... before i add more code...
im using the getopenfilename like this

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

and i get the mismatch at

.LookIn = MyFolder

im getting a little confused now...

 

[Norie]

Did you read my posts?

You are returning an array of filenames from GetOpenFilename, hence the type mismatch.

 

[Kyle775]

i see this seems to have worked! ive learned alot! thanks so much...
what did you say about looping instead of searching??

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

 

[Norie]

Well if you were to use the GetOpenFileName method and you selected all the files in the folder you return an array of filenames in MyInput.

Therefore you could just loop through this array.

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)

    For i = LBound(MyFiles) To UBound(MyFiles)
        Set WB = Workbooks.Open(.MyFiles(i))
        For Each ws In WB.Worksheets
            ws.PrintOut
        Next ws
        WB.Close
    Next i
    
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub