Help with Copy/Paste Excel to Word Intelligently

[ViciousXUSMC]

I am going to start doing my best to write a script to copy data out of excel and then paste it into word.

I was hoping that somebody has experience with the UDF and can help me get me pointed in the right direction with a basic skeleton of code.

My goal is to take a sheet of information that contains many columns with a header at the top and paste it into a word document in this order.

Assume Row 1 all the way across is my Column Headers

Row 2 Thru X is my Data

In Word I would want to paste this format:  

Header (A1)

Data (A2)

Header (B1)

Data (B2)

 

Once every Column is pasted it would continue

Header (A1)

Data (A3)

Header (B1)

Data (B3)

So in other words I need the header to be on top of each data entry and the columns to read left to right until the last header column, then move down to the next data row and repeat until there are no more data rows.

I have the challenge of learning all the UDF for Excel and to find a good way to interact with Word, but also how to implement the right logic to make this work.

I appreciate the help.

Edit: To be really over the top each Data Row could save as a separate document but if it all pushes into one document I will make due.

Edited August 14, 2014 by ViciousXUSMC

[JohnOne]

I have the challenge of learning all the UDF for Excel and to find a good way to interact with Word,

I'd advise the standard Word.au3 UDF.

[water]

With the latest version of AutoIt I would suggest to use something like this to read the Excel sheet into an array.

#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $oAppl = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $sWorkbook = @ScriptDir & "\Test.xls"
Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error opening workbook '" & $sWorkbook & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oAppl)
    Exit
EndIf
; Read all cells of the active worksheet into an array
Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange)

Next step would then be to copy the data into the Word document.

[ViciousXUSMC]

so $aResult will be a 3 dimensional array?

$aResult[x][y]

I have only used limited array functions, but I do need to learn them better.

I tried to work up an idea but as I typed it out I realized how flawed it was... 

[water]

No, just a 2D array with [x] as index for the rows and [y] as index for the columns.

You can check what you get by calling function _ArrayDisplay at the end.

#include <Excel.au3>
#include <Array.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $oAppl = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $sWorkbook = @ScriptDir & "\Test.xls"
Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF", "Error opening workbook '" & $sWorkbook & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oAppl)
    Exit
EndIf
; Read all cells of the active worksheet into an array
Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange)
_ArrayDisplay($aResult)

[ViciousXUSMC]

So far so good

Was getting @Error = 4 but found out its because I had the file open on another monitor behind a window :/

Been toying with $aResult and MsgBox just to get a feel for pulling specific cells.

Now time to figure out how to create that pattern I want, after I get that down probably can change my MsgBox into a Word.UDF function or a basic copy/paste to word.

The test sheet has 3 headers and 3 rows of data forming a 3 column 4 row total worksheet.

The pattern runs like this:

MsgBox(0, "", $aResult[0][0])
MsgBox(0, "", $aResult[1][0])
MsgBox(0, "", $aResult[0][1])
MsgBox(0, "", $aResult[1][1])
MsgBox(0, "", $aResult[0][2])
MsgBox(0, "", $aResult[1][2])
MsgBox(0, "", $aResult[0][0])
MsgBox(0, "", $aResult[2][0])
MsgBox(0, "", $aResult[0][1])
MsgBox(0, "", $aResult[2][1])
MsgBox(0, "", $aResult[0][2])
MsgBox(0, "", $aResult[2][2])
MsgBox(0, "", $aResult[0][0])
MsgBox(0, "", $aResult[3][0])
MsgBox(0, "", $aResult[0][1])
MsgBox(0, "", $aResult[3][1])
MsgBox(0, "", $aResult[0][2])
MsgBox(0, "", $aResult[3][2])

The challenge right now is to find a way to dynamically determine the count of rows and columns so that I can assign that to a variable of sorts, and then implement that variable into a loop that would produce this pattern. 

[water]

The number of rows and columns in an array can be determined by function UBound.

Rows: UBound($aResult, 1)

Columns: UBound($aResult, 2)

[water]

Before playing with Word. I suggest to design the layout of the array in Word. Your design posted in #1 seems a bit complex.

Wouldn't writing the whole array to Word suffice? Check the example in function _Word_DocTableWrite.

[ViciousXUSMC]

Hmm wouldn't that function just create a table in word (aka excel like grid) the reason I am trying to create this is because my actual spreadsheet is going to have paragraphs of text entered into each cell on the spreadsheet and the readability in excel is quite horrid.

So I am trying to convert it into a much more appropriate word file that contains the results as if somebody typed up responses to questions rather than it all being shoved into a database. 

[water]

I see.

If any of the Excel cells contains >255 characters you need to set parameter $bForceFunc to True. The internally used transposed method is limited to 255 characters.

Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange, Default, True)

[ViciousXUSMC]

Yes many cells will contain more than 255 characters.  

Now I am still staring at my desired order of reading the array and trying to find a formula that can produce those results.

Im sure some math genius can do it, but I am probably going to end up stuck at this part.

I may end up just having to manually do the code for each spreadsheet, but then I could probably just copy/paste the stuff directly just as fast :/

[water]

To loop through the table I would use the following code (untested):

For $iRow = 1 to UBound($aResult, 1) - 1 ; Loop through all the rows
    For $iCol = 0 to UBound($aResult, 2) - 1 ; Loop through all the Columns
        ConsoleWrite($aResult[0][$iCol] & @CRLF & $aResult[$iRow][$iCol] & @CRLF & @CRLF)
    Next
Next

Should give:
A1
A2

B1
B2

... Last column

A1
A3

... etc.

Edited August 15, 2014 by water

[ViciousXUSMC]

You sir are a genius!  

Works perfect.

Question for you.  Do you perhaps know why my ConsoleWrite does not seem to work on my work machine but it works for me at home.

I'll see what is running but none of the actual console output.  Never could figure out why.

Maybe my install got botched or something :/

Anyways back to the task at hand, that loop is 100% now I just need to program the right commands into the loop to get that information over into Word. 

[water]

Again untested. Takes all the text, assigns it into a single variable and writes the text to a new Word document.

#include <Word.au3>

; Store all cells in a single variable 
Global $sText
For $iRow = 1 to UBound($aResult, 1) - 1 ; Loop through all the rows
    For $iCol = 0 to UBound($aResult, 2) - 1 ; Loop through all the Columns
        $sText = $sText & $aResult[0][$iCol] & @CRLF & $aResult[$iRow][$iCol] & @CRLF & @CRLF
    Next
Next

; Create application object
Local $oRange, $oWord = _Word_Create()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Word UDF", _
        "Error creating a new Word application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
; Open the test document
Local $oDoc = _Word_DocAdd($oWord)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Word UDF", _
        "Error creating new document." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

; Move the start of the range to the next paragraph and extend the end by 2 words
$oRange = _Word_DocRangeSet($oDoc, -1)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Word UDF", _
        "Error setting range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
; Insert text before the range
$oRange.InsertBefore($sText)

[ViciousXUSMC]

Yep working

I'll put it to the true test later,

I gotta see how your $iCol and $iRow varibles work later too, they I suppose automatically loop back around instead of counting up.

I was thinking For $iCol = 1 to X to determine the number of loops, that with each loop $iCol becomes a higher number and then would start looking for columns that do not exist by the time it looped all the way through the data.  But I am assuming the nested For actually has the loop of 1, 2, 3 running over and over for the number of rows and that is the secret to make the whole thing run properly.

Thanks so much water, your helping me and teaching me at the same time.

[water]

That's it. The outer loop runs thorugh all rows and the inner loop runs through all columns. With every new row the inner loop starts with the first column of this row again.

Glad to be of service

[ViciousXUSMC]

Still tearing this down to learn from it.

Have looked up and understand all the functions that were added via the UDF but now wondering about the parts that show "purple" in SciTE

Notably:

$oWorkbook.ActiveSheet.Usedrange

$oRange.InsertBefore($sText) 

These are Objects for Office correct?

As seen here: http://msdn.microsoft.com/en-us/library/ff837519(v=office.14).aspx

However is it the UDF that gives us the ability to interact with these within AutoIT or is this something you can always do no matter if you have the includes. 

 

Unrelated to this script I have seen this once before when working with the mysql.au3 include

With $var2
While NOT .EOF
FileWriteLine($FO2,.Fields("NAME").value & " -- " & .Fields("SYSTEM_DESCRIPTION").value & " -- " & .Fields("USER_FULLNAME").value & " --  " & .Fields("IP").value & @CRLF)
.MoveNext
WEnd
EndWith 

All the .EOF .Fields .Value etc, I was not sure where those came from as they are not AutoIT functions but worked with my query so I just was using them based on knowing they work but not why they work. 

[water]

$oWorkbook.ActiveSheet.Usedrange ; Working wiht Excel COM
$oRange.InsertBefore($sText) ; Working with Word COM

By using AutoIts ObjCreate or ObjGet functions you access Microsofts Object Model of the specified program. Each of the UDFs is just a wrapper to make things easier by hiding more complex things and by doing some error checking. You could do whatever you want with Word, Excel etc. without an UDF.

The objects, collections, methods, properties and events for Excel 2010 can be found here and those for Word 2010 here.

The same is true for the MySQL UDF.

EOF is a property of the RecordSet object and is set to True when the last record has been reached by the MoveNext method.