macro to print all office documents in a directory?

[Kyle775]

someone provided me code earlier this week on how to print all the xls sheets in a directory... is it possible to alter that code to include word documents?
lemme find the code...

Sub PrintAllWS()
Dim wb As Workbook
Dim ws As Worksheet
Dim MyFolder
Dim I As Long

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

    MyFolder = GetFolder
    If MyFolder = vbNullString Then
        MsgBox "No folder selected. Please select a folder to print.", vbCritical
        Exit Sub
    End If
    
    With Application.FileSearch
        .NewSearch
        .LookIn = MyFolder
        .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

i would like to include subdirectories if that is possible...
choose a single "root" and print all worksheets from all work books and all word documents in all the sub directories in the order they appear in windows

 

[Norie]

Kyle

I think you can set one of the arguments for the FileSearch to look for Office documents.

.FileType = msoFileTypeOfficeFiles

Though you might need to add code to deal with exactly how you are going to print each different type of document.

 

[Kyle775]

THANKS!
ive just tried to change that single like and i get an error after selecting my directory that i have the wrong file type for the first .doc document. i have no idea the code to add to show it how to deal with word files...

please.. help... :D

 

[vbsteve]

Hi

I have amended the code you supplied to allow for word docs. You need to set the reference for word.

Sub PrintAllWS()
Dim wb As Workbook
Dim ws As Worksheet
Dim MyFolder
Dim I As Long
Dim sdoc As Document
Dim sWord As Object

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

    MyFolder = GetFolder
    If MyFolder = vbNullString Then
        MsgBox "No folder selected. Please select a folder to print.", vbCritical
        Exit Sub
    End If
    
    With Application.FileSearch
        .NewSearch
        .LookIn = MyFolder
        .FileType = msoFileTypeOfficeFiles
        .Execute

        For I = 1 To .FoundFiles.Count
            If Right(.FoundFiles(I), 3) = "doc" Then
            Set sWord = CreateObject("Word.Application")
            sWord.Visible = True
            Set sdoc = sWord.Documents.Open(.FoundFiles(I))
            sdoc.PrintOut
            sdoc.Close
            Application.Wait Now + TimeValue("00:00:02") 'ALLOW TIME FOR PRINTING
            sWord.DisplayAlerts = False
            sWord.Quit
            Else
            Set wb = Workbooks.Open(.FoundFiles(I))
            For Each ws In wb.Worksheets
                 ws.PrintOut
            Next ws
            wb.Close
            End If
        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

Steve

 

[Kyle775]

thanks thats great... i havent tried the code but can this be done without opening word? why is there a need for a delay for printing?

 

[Norie]

Kyle

I don't actually think this can be done without opening Word.

If you remove this line then the user shouldn't see anything related to Word, apart from the print popup.

sWord.Visible = True

The only

 

[Kyle775]

im not sure if the code does this or not, i dont want to run it on a large directory of files... but does it use the same process of word, so that it doesnt need to open it each time it wants to print a word document? for example if i have 10 word documents would it open word print the first word document and then close word and open it again. or would it close the document and leave word running to save processing power, time etc.??

 

[Norie]

Yes it does open and close word for each Word document.

Why do you actually want to do this?

 

[Kyle775]

im thinking that trying to use this to print 100 or so documents would be faster on my computer if it didnt reopen the entire word application each time it wanted to print. so to print 100 word documents it would only open word once and then open and close documents within the application.
if that makes sense....

 

[Norie]

Why not just select all the Word documents in Explorer, right click and select Print?