locked
Printing selected pages of word document from Excel VBA RRS feed

  • Question

  • Hi,

    I am trying to create macro to process an existing list of documents (table DocList in excel, in worksheet List), which comes with following structure:

    Document name Pages to print File path
    Doc 1.docx 1,3-5,10 C:\Users\DefaultUser\Documents\WordToPrint\Doc1.docx
    Doc 2.doc 12,15 C:\Users\DefaultUser\Documents\WordToPrint\Doc2.doc


    Using late binding, I am facing following issues:

    1) File in use - for some reason, the documents (even if no owner file exists) at opening return "File in use by different user" warning, suggesting read-only opening. (Although local file with no other users around except me). Workaround, I open the file read-open by default. Nevertheless, I am wondering if there is a proper way to get rid of this warning, if the opened document is in exclusive use by the macro.

    2) Run-time error 5141 - This is not a valid print range. I am trying to print the document(s) with following code:

    Sub PrintAll()
    
    Dim objWord As Object
    Dim objDoc As Object
    Dim rngCell As Range
    Dim rngFilePath As Range
    Dim lr As ListRow
    Dim loWork As ListObject
    
    Set loWork = Sheets("List").ListObjects("DocList")
    Set rngFilePath = loWork.ListColumns("File path").Range
    If loWork.ListRows.Count > 0 Then
        Set objWord = CreateObject("Word.Application")
        objWord.Visible = False
        objWord.DisplayAlerts = False
        Application.Dialogs(xlDialogPrinterSetup).Show
        objWord.ActivePrinter = Application.ActivePrinter
        For Each lr In loWork.ListRows
            If lr.Range(3) <> "" And lr.Range(2) <> "" Then
                Set objDoc = objWord.Documents.Open(lr.Range(3).Value, ReadOnly:=True)
                objDoc.PrintOut Range:=4, Pages:=(Chr(34) & lr.Range(2).Value & Chr(34))
                objDoc.Close
            End If
        Next lr
        objWord.Quit
    End If
    End Sub

    Range:= 4 stands for wdPrintRangeOfPages (taken from macro recorder in Word) 

    When tested with parameter Range:=1, no error 5141, but the PrintOut ignored the Pages argument, printing either only one or all pages in documents. My uneducated guess is the Range value is not interpreted as the numeric value of a member of WdPrintOutRange, but tried to be used as a direct definition of the print range...? 

    3) I do not like using lr.Range(nfor setting the reference to a certain column in the table - as the line Set rngFilePath... suggests, I was trying to find a more elegant way to reference the value from the respective column of the table by using the header instead of absolute reference, but failed with that idea and had to resort to the absolute reference. Any ideas here...?

    Thanks in advance for any hints and tips.

    Jozef

    Tuesday, June 11, 2019 1:45 PM

Answers

  • 3) Add declarations

        Dim i As Long
        Dim rngFile As Range
        Dim rngPage As Range
        Dim rngPath As Range

    and use code like this:

        Set rngFile = loWork.ListColumns("Document Name").DataBodyRange
        Set rngPage = loWork.ListColumns("Pages to Print").DataBodyRange
        Set rngPath = loWork.ListColumns("File Path").DataBodyRange
        For i = 1 To loWork.DataBodyRange.Rows.Count
            If rngPage(i) <> "" And rngPath(i) <> "" Then
                Set objDoc = objWord.Documents.Open(rngPath(i), ReadOnly:=True)
                objDoc.PrintOut Range:=4, Pages:=rngPage(i)
                objDoc.Close
            End If
        Next i


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by JozHol Friday, July 19, 2019 1:59 PM
    Tuesday, June 11, 2019 2:44 PM
  • OK, figured it out finally.

    The only thing needed was to not to pass the value of the Pages argument directly as lr.Range(2).Value, but in form of a variable, here is the full code:

    Sub PrintAll()
    
    Dim objWord As Object
    Dim objDoc As Object
    Dim rngCell As Range
    Dim rngFilePath As Range
    Dim lr As ListRow
    Dim loWork As ListObject
    Dim strPages As String
    Dim var
    
    
    Set loWork = Sheets("List").ListObjects("DocList")
    Set rngFilePath = loWork.ListColumns("File path").Range
    If loWork.ListRows.Count > 0 Then
        Set objWord = CreateObject("Word.Application")
        objWord.Visible = False
        objWord.DisplayAlerts = False
        Application.Dialogs(xlDialogPrinterSetup).Show
        objWord.ActivePrinter = Application.ActivePrinter
        For Each lr In loWork.ListRows
            If lr.Range(3).Value <> "" And lr.Range(2).Value <> "" Then
                Set objDoc = objWord.Documents.Open(lr.Range(3).Value, ReadOnly:=True)
                strPages = lr.Range(2).Value
                 var = objWord.PrintOut(Range:=4, Pages:=strPages)
                objDoc.Close
            End If
        Next lr
        objWord.Quit
    End If
    End Sub

    Formatting the cells with pages as text - negative

    Trying to pass the argument with/without extra Chr(34) quote characters - negative

    the extra 

    Dim strPages As String
    ...
        strPages = lr.Range(2).Value
        var = objWord.PrintOut(Range:=4, Pages:=strPages)
    

    did the trick for me at the end of the day.

    Do not know why, if you happen to know, feel free to share.

    Hope I could save some debugging for you guys reading this thread.

    • Marked as answer by JozHol Friday, July 19, 2019 1:57 PM
    Friday, July 19, 2019 1:57 PM

All replies

  • 2) Use

    Pages:=lr.Range(2).Value


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, June 11, 2019 2:29 PM
  • 3) Add declarations

        Dim i As Long
        Dim rngFile As Range
        Dim rngPage As Range
        Dim rngPath As Range

    and use code like this:

        Set rngFile = loWork.ListColumns("Document Name").DataBodyRange
        Set rngPage = loWork.ListColumns("Pages to Print").DataBodyRange
        Set rngPath = loWork.ListColumns("File Path").DataBodyRange
        For i = 1 To loWork.DataBodyRange.Rows.Count
            If rngPage(i) <> "" And rngPath(i) <> "" Then
                Set objDoc = objWord.Documents.Open(rngPath(i), ReadOnly:=True)
                objDoc.PrintOut Range:=4, Pages:=rngPage(i)
                objDoc.Close
            End If
        Next i


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by JozHol Friday, July 19, 2019 1:59 PM
    Tuesday, June 11, 2019 2:44 PM
  • J,
    Re:  Excel and Word code to print

    The Word application is not visible, so "Range" in the PrintOut line, could (does) refer to the Excel application worksheet.
    You might try not using the argument names.

    Also, the column reference (pages to print) refers to pages on multiple documents.
    You will have to implement a counter and use lr.Cells(2, lngRow) possibly.
    Tuesday, June 11, 2019 3:14 PM
  • Hi Hans,

    thank you for your feedback.

    I started there, but using Pages argument without quotes (Chr(34)) results into runtime error 13 - Type mismatch

    Regards

    Jozef

    Tuesday, June 11, 2019 3:15 PM
  • this seems really good, thank you, will use it as proposed.

    will mark as an answer after the other two points are addressed.

    Tuesday, June 11, 2019 3:17 PM
  • J,
    Re:  Excel and Word code to print

    The Word application is not visible, so "Range" in the PrintOut line, could (does) refer to the Excel application worksheet.
    You might try not using the argument names.

    Also, the column reference (pages to print) refers to pages on multiple documents.
    You will have to implement a counter and use lr.Cells(2, lngRow) possibly.

    Thank you,

    as for the first part of your answer, I am afraid I do not understand what do you mean with "not using argument names" - how can I avoid using those? do you mean listing Printout parameters in brackets in a given sequence? Will this approach work with late binding, too? ie, does particular version of word not influence the sequence and the number of arguments?

    Edit: Tried the syntax with unnamed arguments in brackets

    var = objDoc.PrintOut(, , 4, , , , lr.Range(2).Value)

    For some reason vbe required me to have equal sign (=) in the statement, therefore I assigned the value to a single purpose variable.

    However, still no cigar, keep getting the 5141 Error - this is not a valid print range...

    As for the second part of your answer, please notice lr is declared as ListRow and I am cycling through the Listrows collection - hence range(2) is the second column within lr. I do not think that a two-dimensional cell reference is necessary, please correct me if my understanding is wrong.

    • Edited by JozHol Wednesday, June 12, 2019 1:22 PM updated information
    Wednesday, June 12, 2019 8:26 AM
  • J,
    Re:  printingWord from Excel

    (1) Your statement is correct:   "hence range(2) is the second column within lr. "
    I was offbase.

    (2) In the case of "Tried the syntax with unnamed arguments"...
    The following is the PrintOut definition from an older version of Word.

    Expression.PrintOut(Background, Append, Range, OutputFileName, From, To, Item, Copies, Pages, PageType, PrintToFile, Collate, FileName, ActivePrinterMacGX, ManualDuplexPrint, PrintZoomColumn, PrintZoomRow, PrintZoomPaperWidth, PrintZoomPaperHeight)

    Note that the Pages argument is the 9th item not the 7th.
    Generally, new arguments are added to the end of the list.  I don't have the latest Word version to check.


    Wednesday, June 12, 2019 3:21 PM
  • Hi,

    Thank you, corrected the syntax, now the line is

    var = objDoc.PrintOut(, , 4, , , , , , Chr(34) & lr.Range(2).Value & Chr(34))

    (after receiving Type mismatch because of missing quotes)

    BUT, back at good old 5141 - this is not a valid print range... :-(

    interesting enough, Excel does not offer Printout Method for Application object, nor the Range is available as argument for the Printout method of the ActiveWorkbook object. Which leads me to the believe, that it really is the Word Application, that cannot cope with value 4 for the Range argument... although used by Word itself (in the named version)...

    Don't know what to doubt... me..? Excel..? Word...? Chuck Norris*...?

    Would be grateful for any further ideas...

    *Sorry Chuck, of course I would never doubt you, you could sure print the thing out with a half-hearted roundhouse kick

    Wednesday, June 12, 2019 3:46 PM
  • J,
    re:  further ideas

    The well is almost dry.

    For your info...
    wdPrintAllDocument   = 0
    wdPrintCurrentPage    = 2
    wdPrintFromTo          = 3
    wdPrintRangeOfPages = 4
    wdPrintSelection        = 1

    Using constant values (numbers) is recommended when using multiple applications.
    The only suggestion, I have left, is to clean the Pages column in the Excel ListObject.
    Use the Clean and Trim functions.  Also don't center, align, or WordWrap the data.

    Another thought, check the return value for the first lr.Range(2).Value.
    It might be returning "Pages to Print"

    Wednesday, June 12, 2019 4:24 PM
  • J,
    re:  further ideas

    The well is almost dry.

    For your info...
    wdPrintAllDocument   = 0
    wdPrintCurrentPage    = 2
    wdPrintFromTo          = 3
    wdPrintRangeOfPages = 4
    wdPrintSelection        = 1

    Using constant values (numbers) is recommended when using multiple applications.
    The only suggestion, I have left, is to clean the Pages column in the Excel ListObject.
    Use the Clean and Trim functions.  Also don't center, align, or WordWrap the data.

    Another thought, check the return value for the first lr.Range(2).Value.
    It might be returning "Pages to Print"

    Thank you,

    I've found all the numeric values of Word arguments under Word Enumerated Constants

    During debugging I've tested the values of the lr.Range(2).Value a few times, that's definitely not the source of the troubles. I even managed to pass the pages argument to word, but it always only printed one page, or printed one empty page, not all those defined by the argument, therefore I've started to experiment with the printing range.

    Yet, I appreciate a lot your effort, leaving the topic open, probably someone figures it out or I'll post the solution if I manage eventually.

    Wednesday, June 12, 2019 9:42 PM
  • OK, figured it out finally.

    The only thing needed was to not to pass the value of the Pages argument directly as lr.Range(2).Value, but in form of a variable, here is the full code:

    Sub PrintAll()
    
    Dim objWord As Object
    Dim objDoc As Object
    Dim rngCell As Range
    Dim rngFilePath As Range
    Dim lr As ListRow
    Dim loWork As ListObject
    Dim strPages As String
    Dim var
    
    
    Set loWork = Sheets("List").ListObjects("DocList")
    Set rngFilePath = loWork.ListColumns("File path").Range
    If loWork.ListRows.Count > 0 Then
        Set objWord = CreateObject("Word.Application")
        objWord.Visible = False
        objWord.DisplayAlerts = False
        Application.Dialogs(xlDialogPrinterSetup).Show
        objWord.ActivePrinter = Application.ActivePrinter
        For Each lr In loWork.ListRows
            If lr.Range(3).Value <> "" And lr.Range(2).Value <> "" Then
                Set objDoc = objWord.Documents.Open(lr.Range(3).Value, ReadOnly:=True)
                strPages = lr.Range(2).Value
                 var = objWord.PrintOut(Range:=4, Pages:=strPages)
                objDoc.Close
            End If
        Next lr
        objWord.Quit
    End If
    End Sub

    Formatting the cells with pages as text - negative

    Trying to pass the argument with/without extra Chr(34) quote characters - negative

    the extra 

    Dim strPages As String
    ...
        strPages = lr.Range(2).Value
        var = objWord.PrintOut(Range:=4, Pages:=strPages)
    

    did the trick for me at the end of the day.

    Do not know why, if you happen to know, feel free to share.

    Hope I could save some debugging for you guys reading this thread.

    • Marked as answer by JozHol Friday, July 19, 2019 1:57 PM
    Friday, July 19, 2019 1:57 PM