Jump to content
robertocm

_Excel_RangeWrite doesn't write array from ADODB GetRows

Recommended Posts

robertocm
Posted (edited)

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,

#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 by robertocm

Share this post


Link to post
Share on other sites
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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

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

  • Similar Content

    • MrCheese
      By MrCheese
      Hi guys,
      See attached for an array example.
      to simplify what i want to achieve,  I want to split this array into 9 different csv files.
      the first file would contain the list of "key" and the corresponding "ID1", the second would have "key" and the "ID2", the third would have "key" and "ID3"
      However, I want to remove all the rows that don't have an ID recorded in the respective ID2, ID3 4...5...6 etc, so the file only contains row items with a key and the ID.
      Would be the best way to loop through the rows and delete the row if the array field is blank - would I then need to repeat that row ID to check that the row that its replaced is also empty (ie the one after the one I just deleted)? I see this getting messy.
      or _arraySort, and delete everything below the last filled row? <-- this might be best?
      Or should I use the excel UDF, apply a filter (not selecting the blanks), then create/export to the array->csv?
       
      Super keen to hear your thoughts.
      thanks!
       
       
       
       
      IDArray.csv
    • water
      By water
      My computer has been upgraded from Office 2010 to Office 2016.
      Are there any features of Office 2013 or Office 2016 which you now want to see in the Excel, Word or Outlook UDF?
    • FrancescoDiMuro
      By FrancescoDiMuro
      Hi guys!
      First of all, I want to apologize with @Melba23, @JLogan3o13, and @Jos ( I don't know why the tag sometimes works and sometimes doesn't ), for my bad attitude I have had with
      them. I want to explain why I was so angry, but that's not an excuse to my bad attitude...
      I have health problem, and lastly, I am ever more angry because I'm fighting with these problems for 5 years and I feel worse than 5 years ago, even If I had a lot of investigations about with a lot of Doctors, and there's no cure for my pathology. I don't want to touch anyone with my "story", but I wanted to explain why, sometimes, you can feel my anger in my words.
      I promise I'll try to be as polite as my mom taught me, and to explain all I'd like to develop, posting all you need ( sample codes... ).

      Now, let's talk about my project.
      Let's start from 0! 
      I'd like to implement a database with AutoIt, for the warehouse management.
      First of all, I'd like to set some "goals" which I want to respect, developing my application:
      Efficiency: I'd like to develop an application that is fast as possible; Easy to make changes: I'd like to develop an application that is easy to modify, so, without too much complicated arguments; Noob-proof: As a noob as I am, my AutoIt knowledge starts from June 2016, so, I'm not a blade in this programming language. I studied C++, C, JavaScript, CSS, HTML and PHP, but, all of these programming language has a different approach comparing with AutoIt. I could be wrong in what I just estabilished! I want to tell you, again, that I'm here to listen to your suggestions, so, everything you say, it's something that I could learn, and I want to learn AutoIt, so, don't be scared to share your opinions, or your knowledge! I'm here to learn "How to AutoIt!"  Ok, so, after having set some goals I'd like to hit, I want to ask you some information:
      What's the best way to develop a database with AutoIt? With a .txt file? With an Excel workbook? 
      I was working on Excel workbook, but, I don't know, I could always be wrong in terms of efficiency and "easy to make changes"; I have to create a GUI, in order to let the user do things ( I think it was quite obvious ), so, I have been always used GUI functions like GUICreate and so on; anything about _GDI_Plus or something else. Most, I used _GUI* functions. What's the best way to manage different GUIs? OnEvent Mode or Message Loop "mode"? Now, I'd like to make a list of what I'd like my application would do:
      Reading stocks: I manage mainly 2 manufacturers, so, If I will work with an Excel workbook, I'd prefer to divide first manufacturer with the second; Add product: Simply, If a product does exist, the application prompt to the user an input box, with a sort of summary ( Product name, quantity ) of the product he's adding; ELSE, if the product does not exist, an "Add New Product" GUI should appers; Withdraw product: As a warehouse, I need to add and to withdraw product... This "function" should performs the simply task of reserach the product based on his ID and, prompt an InputBox to the user, with a little summary of the product he's withdrawing and, the quantity of the product after the withdraw; Search product: I'd like to develop a "product reserach", based on the ID of the product(s), or based on the Description of the product(s)... It'd be very nice!  Create DDT: Create a DDT with a pre-formatted Excel sheet. Just insert some information, and then all the products that have been withdrawn from the database ( maximum 14 per DDT ); I have already done some of work I described here, and you can find all files you need attached to this post. 
      I don't want you to do this. Just, don't post your code that I have been looking for, but let me try, with your help, to get an appropriate and efficient solution to what I'm asking.
      The last, but not the less important, the recognition I can give to you is publish your name ( or alias ) on the "developer section", more than a donation for everyone who has take part to this project.

      Please, if you want to know further information ( I think I've been exhaustive enough ), write here or on a PM. 

      Another time, a big thank you to this fantastic community and, a bigger one, to the developers of this amazing and powerful programming language.

      Enjoy your night.

      Francesco 

      PS: I will post the code tomorrow ( the most updated code ), so you can see where I have been arrived and, nothing Warehouse_Management.xlsx
      Gestione_Magazzino_SYS.au3
    • Faedien
      By Faedien
      Hello all, 
      I've lurked around here for a while and learned a ton from everyone, but I can't see this issue anywhere.  I've been running macros through Autoit for a while in a number of programs, but I'm having some issues with this one.
      Func RunMacro() Local $oExcel = _Excel_Open(False, False, False, False) ;Excel Program Local $oWorkbook = _Excel_BookOpen($oExcel, "C:/Equity Helper/Scripts/calendarmacros.xlsm", Default, Default, True) $oExcel.Run("calendarmacros.xlsm!Calendar") EndFunc When I run this bit of code everything works fine as long as the macro gets to finish.  But the actual VBA calls two "Application.UserInput" fields, and if I cancel out of either the macro closes, and then AutoIt throws an error at--
      $oExcel.(<---error throws here)Run("calendarmacros.xlsm!Calendar") I don't want to use autoit for the user inputs if possible, it's just easier to get the data I need for the inputs from within the VBA environment, but I'm afraid that's what's causing the problem.
    • sivaramanm
      By sivaramanm
      From AutoIT script (Pretty much same syntax as VBA), Tried connecting to MySQL Server. While i am able to insert a new row successfully, unable to verify the rowcount (# of inserted row - to verify success or failure).
      Have tried two different methods -
      to use the RecordsAffected variable from Connection Execute function to use the RecordSet and retrieve the rowcount But have been missing something and none of these methods return the actual row count.
      Any help would be appreciated.!!!
      Cross-posted in http://stackoverflow.com/questions/27411599/unable-to-retrieve-inserted-row-count-in-mysql-using-ado-from-autoit
      MySQLConnect() $EVENT_TIME= "2014-12-12 12:12:12" $LSMCName='LSMC1' $NEType='MME0001' $OMTarFile='A_MME0011-60MIN-20141212-12-v.tar' $CSVFile='12-00-S1AP.csv' $KPIType='S1AP_HO' $UpdateStatus='NotUpdated' $ReTries='0' If Not (InsertFileUpdateLog($EVENT_TIME,$LSMCName,$NEType,$OMTarFile,$CSVFile,$KPIType,$UpdateStatus,$ReTries)=1) Then WriteLog("[Error] Record insertion failed for " & $EVENT_TIME & '" ' & $LSMCName & ' ' & $NEType & ' ' & $OMTarFile & ' ' & $CSVFile & ' ' & $KPIType & ' ' & ' NotUpdated 0') EndIf MysqlDisconnect() ;~ ####################### Sub Function Definitions Func MySQLConnect() Local $sDriver="MySQL ODBC 5.3 ANSI Driver" Local $key = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $val = RegRead($key, $sDriver) If @error or $val = "" Then SetError(2) Return -1 EndIf $constrim="DRIVER={MySQL ODBC 5.3 ANSI Driver};SERVER=localhost;DATABASE=pmdemo;uid=rootuser;pwd=rootpass;" $oDBConnect = ObjCreate ("ADODB.Connection") ; <== Create SQL connection $oDBConnect.Open ($constrim) ; <== Connect with required credentials if @error Then WriteLog("[Error] Failed to connect to the database") SetError(2) Return -2 Else ;MsgBox(0, "Success!", "Connection to database successful!") Return 1 EndIf EndFunc Func MySQLDisConnect() $oDBConnect.Close ; ==> Close the database EndFunc Func InsertFileUpdateLog($EVENT_TIME,$LSMCName,$NEType,$OMTarFile,$CSVFile,$KPIType,$UpdateStatus,$ReTries) Local $RowCount = 0 Local $result = ObjCreate("ADODB.Recordset") $sQuery = "INSERT INTO 4gc_fileupdatelog (id,EVENT_TIME,LSMC,NEType,TarFile,CSVFile,KPIFile,UpdateStatus,ReTries) VALUES ('0'," & _ "'" & $EVENT_TIME & "'," & _ "'" & $LSMCName & "'," & _ "'" & $NEType & "'," & _ "'" & $OMTarFile & "'," & _ "'" & $CSVFile & "'," & _ "'" & $KPIType & "'," & _ "'" & $UpdateStatus & "'," & _ "'" & $ReTries & "'" & _ ") ON DUPLICATE KEY UPDATE ReTries=ReTries+1,UpdateStatus='" & $UpdateStatus & "';" $result = $oDBConnect.Execute($sQuery,$RowCount) If @error Then MsgBox(1,1,"Error executing query...") Return -2 EndIf ;# Method-1 : To use records affected from Execute function If $RowCount >= 1 Then MsgBox(1,1,"Success") Else MsgBox(1,1,"Failed, rowcount is:" & $RowCount ) EndIf If Not ($result.bof AND $result.eof) Then WriteLog("[Error] No Rows found") Return 0 EndIf ;# Method-2 : To use recordsset object and retrieve the rows/columns count If IsObj($result) And $result.EOF=False Then $myarray=$result.GetRows() $rows = UBound($myarray,1) $cols = UBound($myarray,2) MsgBox(1,1," rows: " & $rows & " cols: " & $cols) If ($rows = 1) Then WriteLog("[Info] Record inserted successfully") Return 1 ElseIf ($rows = 2) Then WriteLog("[Alert] Record updated successfully. affected row(s) is " & $rows) Return $rows Else > Blockquote WriteLog("[Error] Record insertion failed. affected row(s) is " & $rows) Return 0 EndIf EndIf EndFunc
×