macro to print all office documents in a directory?

Kyle775

Board Regular
Joined
Feb 16, 2005
Messages
230
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...

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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
Kyle

I think you can set one of the arguments for the FileSearch to look for Office documents.
Code:
.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

Board Regular
Joined
Feb 16, 2005
Messages
230
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

Board Regular
Joined
Dec 11, 2003
Messages
99
Hi

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

Code:
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

Board Regular
Joined
Feb 16, 2005
Messages
230

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
sWord.Visible = True

The only
 

Kyle775

Board Regular
Joined
Feb 16, 2005
Messages
230

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
Yes it does open and close word for each Word document.

Why do you actually want to do this?
 

Kyle775

Board Regular
Joined
Feb 16, 2005
Messages
230
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
Why not just select all the Word documents in Explorer, right click and select Print?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,674
Messages
5,573,598
Members
412,537
Latest member
Mohamed_5966
Top