Merge word documents

[water]

Seems dataload2.xlsx contains a lot of empty (but already "touched") rows and columns.
I deleted 100 empty rows after the last row and 20 columns after the rightmost column.

I stripped down to the first 3 input files (C:\temp\Word_Merge\NWoW-Deliverable-Template-Division-WS.doc, C:\temp\Word_Merge\WesInptAdminAdmiss.doc and C:\temp\Word_Merge\WesInptAdminPatien.doc) because I had to manually create those input files.

The script just works fine now.

 

Edited November 30, 2016 by water

[MrCheese]

i see i see..

okay, so the input matrix needs to be completely "clean" aside from blank fields.

and if it doesn't work, then there are fields that have been touched.

- excellent. Thanks for your help!!!

Re ubound, and the values in the same line: For $i = 0 To UBound($aDocuments, 1) - 1

are you able to explain how that operates? as it really eliminates a lot of unnecessary while loops and do until etc. I understand it obtains the size of the array, but why the -1 at the end vs -2 in relation to for $j?

 

Thanks!

 

[water]

Because UBound returns the number of elements in the array (rows or columns) e.g. 4 rows. But when you access elemens of an array you use an offset starting with 0 e.g from 0 to 3. Hence you need to subtract 1 to get the maximum index.

-2 is used when processing all elements in a row because the last element contains the name of the output file.

Edited November 30, 2016 by water

[water]

If needed we could add some lines of code to the script to eliminate empty rows/columns.

[MrCheese]

1 hour ago, water said:

If needed we could add some lines of code to the script to eliminate empty rows/columns.

 

That would be very helpful, how do you differentiate between blanks used and not used?

[MrCheese]

*double post*

Edited November 30, 2016 by MrCheese

[water]

This script now jumps to the last non empty cell and ignores all empty but previously "touched" cells.

#include <Word.au3>
#include <Excel.au3>

Global Const $xlUp = -4162 ; Up
Global Const $xlToLeft = -4159 ; To left
Global $oExcel = _Excel_Open(False)
Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\dataload2.xlsx")
; Get the last used row
Global $oCells = $oWorkbook.ActiveSheet.Cells
Global $iLastRow = $oCells($oCells.Rows.Count, 1).End($xlUp).Row
; Get the last used column in row 1
Global $iLastColumn = $oWorkbook.ActiveSheet.Cells(1, $oCells.Columns.Count).End($xlToLeft).Column
; Read Excel Range
Global $aDocuments = _Excel_RangeRead($oWorkbook, Default, "A1:" & _Excel_ColumnToLetter($iLastColumn) & $iLastRow)
_Excel_Close($oExcel, False)
; Create Word documents
Global $oWord = _Word_Create()
Global $oDocMaster, $oDoc, $i, $j, $oRange
For $i = 0 To UBound($aDocuments, 1) - 1
    $oDocMaster = _Word_DocAdd($oWord) ; Start a new document for every row
    $oRange = _Word_DocRangeSet($oDocMaster, -1)
    For $j = 0 To UBound($aDocuments, 2) - 2 ; Last element of the row holds the path of $oDocMaster
        If $aDocuments[$i][$j] <> "" And FileExists($aDocuments[$i][$j]) Then
            $oRange.InsertFile($aDocuments[$i][$j])
            $oRange = _Word_DocRangeSet($oDocMaster, -2)
            $oRange.InsertBreak($WdPageBreak)
        EndIf
    Next
    ; Go to the end of the document and delete the empty page
    $oRange = _Word_DocRangeSet($oDocMaster, -2, $wdCharacter, -3)
    $oRange.Delete
    ; Save and close master document
    _Word_DocSaveAs($oDocMaster, $aDocuments[$i][UBound($aDocuments, 2) - 1], $WdFormatDocumentDefault)
    _Word_DocClose($oDocMaster)
Next
_Word_Quit($oWord)