Jump to content
Sign in to follow this  
HighlanderSword

Excel write array

Recommended Posts

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)

 

Share this post


Link to post
Share on other sites

Try

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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

Modify the _Excel_RangeWrite statement to

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

 

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...