Excel write array

[HighlanderSword]

Hello,

 

When I go to use excel to write out an array there were no errors, but nothing

was written to Excel. I have validated there is data in the array as I display the array

Below is my code what am I missing

 

 

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

#include <d:\ouex\OutlookEX.au3>
Global $oOutlook = _OL_Open()

$aReturn = _OL_FolderSelectionGet($oOutlook)
                    $aReturn = _OL_ItemGet($oOutlook, $aReturn[1][1]) 
                    _ArrayDisplay($aReturn)

; Create application object and create a new workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookNew($oExcel)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf



_ArrayDisplay($aReturn)



_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aReturn, "a1:e90")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Error ", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

 

[water]

Try

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aReturn, "A1")

 

[HighlanderSword]

same issue, no results written

[water]

The problem is caused by the data returned from Outlook. The array hold some objects (Outlook internal data) which can't be processed by Excel.
Remove the object references and your script will run as expected:

#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <d:\ouex\OutlookEX.au3>

Global $oOutlook = _OL_Open()

$aReturn = _OL_FolderSelectionGet($oOutlook)
$aReturn = _OL_ItemGet($oOutlook, $aReturn[1][1]) 

; Create application object and create a new workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookNew($oExcel)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

For $i = 1 To UBound($aReturn) - 1
    If $aReturn[$i][2] = 0 And VarGetType($aReturn[$i][1]) = "Object" Then
        $aReturn[$i][1] = "** REMOVED **"
    EndIf
Next
_ArrayDisplay($aReturn)

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aReturn, "a1:e90")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Error ", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

 

Edited July 13, 2019 by water

[HighlanderSword]

Water,

 

Are there other objects that need to be Removed ?

 

Running the above generates no Excel Output

[water]

Modify the _Excel_RangeWrite statement to

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aReturn, "a1", Default, True)

 

Edited July 13, 2019 by water

[water]

You need to use the transpose function of the Excel UDF because there are cells with > 255 characters in your array. Only 255 characters can be handled by the Excel internal function.

Body: 6827
HTMLBody: 18886
RTFBody: 101680

 

[Subz]

You can write array item to a single cell, although needs to be less than 32,767 characters (see https://support.office.com/en-ie/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3),  This would require a loop and adding each item individually to the spreadsheet (which will be a lot slower if you want to process multiple email items) I'd personally skip adding HTMLBody and RTFBody and remove any duplicate white space, I would then use StringLeft(..., (some number less than 32767)) to ensure the data is entered correctly(nb you may not be able to read the data in Excel if it's 32767 characters, but you can still reference data from those cells in search field or forumlas).

My 2 cents.

[water]

I clicked together a quick&dirty check routine for your data.
It checks for objects, cells > 255 characters and cells > 32767 characters.
You get a count for each category or a detailed listing so you can identify the cells you need to change.
Or you can set flag $bFix and all or individual categories get fixed.

#include <String.au3>
; Create an array with invalid data
Global $aData[2][2] = [[ObjCreate("Scripting.Dictionary"), _StringRepeat("1", 256)], [_StringRepeat("1", 32768), _StringRepeat("1", 256)]]
Global $bDetail = True  ; If set to True each cell with invalid data is listed with zero based row, column and explanation
Global $bFix = 7        ; If set to <> 0 cells with invalid data will be modified or shortened so, as a result, they hold valid data
                        ; Can be a combination of any of the following values:
                        ; 1 - Set all objects to ""
                        ; 2 - Strip all cells with more than 32767 characters to 32767 characters using StringLeft
                        ; 4 - Strip all cells with more than 255 but less than 32767 characters to 255 characters using StringLeft
Global $aCount[] = [0, 0, 0]
For $i = 0 To UBound($aData, 1) - 1
    For $j = 0 To UBound($aData, 2) - 1
        Select
            Case IsObj($aData[$i][$j])
                If $bDetail Then ConsoleWrite("Row " & $i & ", Col " & $j & ": IsObj" & @CRLF)
                If BitAND($bFix, 1) = 1 Then $aData[$i][$j] = ""
                $aCount[0] += 1
            Case StringLen($aData[$i][$j]) > 32767
                If $bDetail Then ConsoleWrite("Row " & $i & ", Col " & $j & ": Length > 32767" & @CRLF)
                If BitAND($bFix, 2) = 2 Then $aData[$i][$j] = StringLeft($aData[$i][$j], 32767)
                $aCount[1] += 1
            Case StringLen($aData[$i][$j]) > 255
                If $bDetail Then ConsoleWrite("Row " & $i & ", Col " & $j & ": Length > 255" & @CRLF)
                If BitAND($bFix, 4) = 4 Then $aData[$i][$j] = StringLeft($aData[$i][$j], 255)
                $aCount[2] += 1
        EndSelect
    Next
Next
ConsoleWrite("SUMMARY" & @CRLF)
ConsoleWrite("Cells containing data type object        : " & $aCount[0] & ". Fixed: " & (BitAND($bFix, 1) = 1) & @CRLF)
ConsoleWrite("Cells containing data > 32.767 characters: " & $aCount[1] & ". Fixed: " & (BitAND($bFix, 2) = 2) & @CRLF)
ConsoleWrite("Cells containing data > 255 characters   : " & $aCount[2] & ". Fixed: " & (BitAND($bFix, 4) = 4))
If (BitAND($bFix, 4) <> 4) Then ConsoleWrite(". Use $bForceFunc = True in _Excel_RangeWrite.")
ConsoleWrite(@CRLF)

Example console output:

Row 0, Col 0: IsObj
Row 0, Col 1: Length > 255
Row 1, Col 0: Length > 32767
Row 1, Col 1: Length > 255
SUMMARY
Cells containing data type object        : 1. Fixed: True
Cells containing data > 32.767 characters: 1. Fixed: True
Cells containing data > 255 characters   : 2. Fixed: True

 

Edited July 13, 2019 by water

[water]

If an array holds cells with up to 32,767 characters they still can be written in one go when setting parameter $bForceFunc to True.