_Excel_RangeWrite doesn't write array from ADODB GetRows

[robertocm]

In the code below i can write the excel sheet with an array from an ADODB GetRows command

But not using _Excel_RangeWrite function

Thanks for your comments,

expandcollapse popup

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

;Help: COM Error Handling
Global $errADODB = ObjEvent("AutoIt.Error","_ErrADODB")

Local $sFilePath = @ScriptDir & "\db.mdb"

Local Const $iCursorType = 0 ; adOpenForwardOnly, 3 adOpenStatic
Local Const $iLockType = 3 ;1 adLockReadOnly, 3 adLockOptimistic
Local Const $iOptions = 1 ; Options, 1 Evaluates as a textual definition of a command or stored procedure call ; 2 adCmdTable
Global $cn = ObjCreate("ADODB.Connection") ; Create a connection object
Global $rst = ObjCreate("ADODB.Recordset") ; Create a recordset object
;Global $sADOConnectionString = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & $sFilePath
;Global $sADOConnectionString = 'DRIVER={Microsoft Access Driver (*.mdb)};Dbq=' & $sFilePath & ';uid=;pwd=MyPassword;'
Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilePath & ";Jet OLEDB:Database Password=123"

$cn.CursorLocation = 3 ; adUseClient

;https://stackoverflow.com/questions/31941487/open-adodb-connection-to-excel-file-in-read-only-mode
;https://www.w3schools.com/asp/prop_rec_mode.asp
$cn.Mode = 1 ;Read-only

;$cn.CommandTimeout = 0

$cn.Open($sADOConnectionString) ; Open the connection

Local $sSQL = "SELECT * FROM TABLE1"
$rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query

If Not $rst.EOF = True Then
   Local $rstArray = $rst.GetRows()
   _ArrayDisplay ($rstArray)
   $rst.Close

   ;Create application object
   Local $oAppl = _Excel_Open()
   If @error Then Exit MsgBox(0, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

   ; Create a new workbook with only 1 worksheet
   Local $oWorkbook = _Excel_BookNew($oAppl, 1)
   If @error Then Exit MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
   ;MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Workbook has been created successfully with only 1 worksheets.")

   Local $sSheet = $oWorkbook.ActiveSheet

   Local $aArray1D[3] = ["Field1", "Field2", "Field3"]
   $sSheet.Range("A1:C1").Font.Bold = True
   $sSheet.Range("A1:C1").value = $aArray1D

   Local $RecCount = UBound($rstArray)
   Local $TrstArray = $rstArray
   _ArrayTranspose($TrstArray)

   ;$sSheet.Range("A2:C" & $RecCount + 1).value = $TrstArray    ;<<<<<<<<<<<<<< This writes the data OK

   ;This fails
   _Excel_RangeWrite($oWorkbook, Default, $TrstArray, "A2:C" & $RecCount + 1)
   If @error Then MsgBox(0, "Excel UDF: _Excel_RangeWrite Example 3", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

   $rst = 0 ;Release the recordset object
   $cn.Close ;Close the connection
   $cn = 0  ;Release the connection object
Else
   $rst.Close
   $rst = 0 ; Release the recordset object
   $cn.Close ; Close the connection
   $cn = 0 ; Release the connection object
   MsgBox(262144, "", "Empty" & @CRLF & "Empty recordset" , 5)
EndIf

Func _ErrADODB()
   Msgbox(0,"ADODB COM Error","We intercepted a COM Error !"      & @CRLF  & @CRLF & _
       "err.description is: "    & @TAB & $errADODB.description    & @CRLF & _
       "err.windescription:"     & @TAB & $errADODB.windescription & @CRLF & _
       "err.number is: "         & @TAB & hex($errADODB.number,8)  & @CRLF & _
       "err.lastdllerror is: "   & @TAB & $errADODB.lastdllerror   & @CRLF & _
       "err.scriptline is: "     & @TAB & $errADODB.scriptline     & @CRLF & _
       "err.source is: "         & @TAB & $errADODB.source         & @CRLF & _
       "err.helpfile is: "       & @TAB & $errADODB.helpfile       & @CRLF & _
       "err.helpcontext is: "    & @TAB & $errADODB.helpcontext, 5)

   Local $err = $errADODB.number
   If $err = 0 Then $err = -1

   $rst = 0
   $cmd = 0
   $cn.Close
   $cn = 0

   Exit

EndFunc

 

Edited August 10, 2018 by robertocm

[water]

How many records do we talk about?
Does the content of any cell exceed 255 characters?
What is the value of @error after _Excel_RangeWrite?

[robertocm]

Only 10 records in the example attached below

very short text fields

Error value is 0 but does not write to the sheet

 

example_with_access_database.zip

[robertocm]

[SOLVED]

The problem was caused by null values in some table fields. Not related with the UDF

Perhaps AutoIt transpose the Recordset GetRows array and the presence of Null values would not be allowed (i remember something similar in Excel VBA).

Here is a version of the code that works OK: see the added WHERE clause in the SQL statement filtering Nulls

expandcollapse popup

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

Opt("MustDeclareVars", 1)
;Opt("TrayIconDebug", 1)
OnAutoItExitRegister("OnAutoItExit")

;Avoid errors on OnAutoItExit
Global $cn, $rst, $sSQL

#===== ADODB =====
Global $sFilePath = @ScriptDir & "\db.mdb"

;Help: COM Error Handling
;_ErrADODB From spudw2k
;https://www.autoitscript.com/forum/topic/105875-adodb-example/
Global $errADODB = ObjEvent("AutoIt.Error","_ErrADODB")

Global Const $iCursorType = 3 ;0 adOpenForwardOnly, 3 adOpenStatic
Global Const $iLockType = 3 ;1 adLockReadOnly, 3 adLockOptimistic
Global Const $iOptions = 1 ; Options, 1 Evaluates as a textual definition of a command or stored procedure call ; 2 adCmdTable
$cn = ObjCreate("ADODB.Connection") ; Create a connection object
$rst = ObjCreate("ADODB.Recordset") ; Create a recordset object
Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilePath & ";Jet OLEDB:Database Password=123"
;Global $sADOConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $sFilePath & ";Jet OLEDB:Database Password=123"
;Global $sADOConnectionString = 'Driver={Microsoft Access Driver (*.mdb)};Dbq=' & $sFilePath & ';uid=;pwd=MyPassword;'

;https://www.w3schools.com/asp/prop_rs_cursorlocation.asp
;A Recordset object inherits this setting from the associated Connection object.
;This property is read-only on an open Recordset object, and read/write on a Connection object or on a closed Recordset object.
$cn.CursorLocation = 2 ;2 adUseServer, 3 adUseClient
$cn.CommandTimeout = 60

;https://stackoverflow.com/questions/31941487/open-adodb-connection-to-excel-file-in-read-only-mode
;https://www.w3schools.com/asp/prop_rec_mode.asp
$cn.Mode = 1 ;Read-only

$cn.Open($sADOConnectionString) ; Open the connection
;MsgBox(0,"",$cn.ConnectionString)

#===== EXCEL =====
Global $oMyError = ObjEvent("AutoIt.Error", "ErrFunc") ;Install a custom error handler
Global $iEventError ; to be checked to know if com error occurs. Must be reset after handling.

Global $oAppl = _Excel_Open()
;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Open" & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Global $oWorkbook = _Excel_BookNew($oAppl, 1)
;If @error Then Exit MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Error creating new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;MsgBox(0, "Excel UDF: _Excel_BookNew Example 1", "Workbook has been created successfully with only 1 worksheets.")

Global $oSheet = $oWorkbook.ActiveSheet

$sSQL = "SELECT * FROM TABLE1" _
   & " WHERE N IS NOT NULL" _
   & " AND T1 IS NOT NULL" _
   & " AND T2 IS NOT NULL" _
   & " AND T3 IS NOT NULL" _
   & " AND T4 IS NOT NULL" _
   & " AND D IS NOT NULL;"
$rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query

If Not $rst.EOF = True Then
   Global $rstArray = $rst.GetRows()
   ;_ArrayDisplay ($rstArray)
   $rst.Close

   ;$rst = 0    ;Release the recordset object
   ;$cmd = 0
   $cn.Close ;Close the connection
   ;$cn = 0 ;Release the connection object

   _Excel_RangeWrite($oWorkbook, $oSheet, $rstArray, $oSheet.Cells(2, 1))

   Global $aArray1D[6] = ["F1", "F2", "F3", "F4", "F5", "F6"]
   $oSheet.Range("A1:F1").value = $aArray1D
   $oSheet.Cells(1, 1).Resize(1, 6).Font.Bold = True
   $oSheet.Cells(1, 1).Resize(1, 6).AutoFilter
Else
   $rst.Close
   ;$rst = 0 ; Release the recordset object
   $cn.Close ; Close the connection
   ;$cn = 0 ; Release the connection object

   ;MsgBox(262144, "", "Non encontrados rexistros" & @CRLF & "Liña script: " & @ScriptLineNumber , 5)
EndIf

Func _ErrADODB()
   Msgbox(0,"ADODB COM Error","We intercepted a COM Error !"      & @CRLF  & @CRLF & _
       "err.description is: "    & @TAB & $errADODB.description    & @CRLF & _
       "err.windescription:"     & @TAB & $errADODB.windescription & @CRLF & _
       "err.number is: "         & @TAB & hex($errADODB.number,8)  & @CRLF & _
       "err.lastdllerror is: "   & @TAB & $errADODB.lastdllerror   & @CRLF & _
       "err.scriptline is: "     & @TAB & $errADODB.scriptline     & @CRLF & _
       "err.source is: "         & @TAB & $errADODB.source         & @CRLF & _
       "err.helpfile is: "       & @TAB & $errADODB.helpfile       & @CRLF & _
       "err.helpcontext is: "    & @TAB & $errADODB.helpcontext, 5)

   Local $err = $errADODB.number
   If $err = 0 Then $err = -1

   $rst = 0
   ;$cmd = 0
   $cn.Close
   ;$cn = 0

   Exit
EndFunc

;This is a custom error handler
Func ErrFunc()
   Local $HexNumber = Hex($oMyError.number, 8)
;~    MsgBox(0, "", "We intercepted a COM Error !" & @CRLF & _
;~       "Number is: " & $HexNumber & @CRLF & _
;~       "WinDescription is: " & $oMyError.windescription)
   ConsoleWrite("->    We intercepted a COM Error !" & @CRLF & _
   "->    err.number is: " & @TAB & $HexNumber & @CRLF & _
   "->    err.source: " & @TAB & $oMyError.source & @CRLF & _
   "->    err.windescription: " & @TAB & $oMyError.windescription & _
   "->    err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF)

   $iEventError = 1 ; Use to check when a COM Error occurs
EndFunc   ;==>ErrFunc

Func OnAutoItExit()
   $rst = 0 ; Release the recordset object
   ;$cmd = 0
   If IsObj($cn) Then
      If $cn.State > 0 Then $cn.Close ;adStateOpen Close the connection
      $cn = 0 ; Release the connection object
   EndIf
EndFunc

[water]

In the wiki you will find a script to test data before using _Excel_RangeWrite.
I will test if this script can be extended to check for NULL values as well.
 

[robertocm]

Dear water,

Many Thanks!!

I'm using your Excel UDF daily.

I have some notes from the forum about testing null values:

;https://www.autoitscript.com/forum/topic/180147-testing-if-variable-is-empty-changed/
;Yes, I get null from SQL - that didn't change between AutoIT Versions and is still the same.
;But testing for null did change as it seems. In 3.3.8.1. a test for null works fine with just = ""
;apparently this changed in new versions where you have to take a look at StringLen or String($var).
;https://www.autoitscript.com/autoit3/docs/autoit_changelog.txt
;3.3.10.0 (23rd December, 2013) (Release) -  Added: Keyword Null.
;If $rstArray[$i][1] = Null Or $rstArray[$i][1] = "" Then
;If String($rstArray[$i][1]) = "" Then
;If (StringLen($rstArray[$i][1]) = 0) Then ...

 

Edited January 28, 2020 by robertocm

[water]

As I understand from the helpfile you now have to use:

If IsKeyword($vVariable) = $KEYWORD_NULL Then ...

 

[water]

I have updated the example in the wiki. It now detecs keyword Null and allows to replace it with "" if $bFix is set accordingly.