Jump to content
robertocm

ADODB: UPDATE Excel from Access database

Recommended Posts

An example of updating an excel file with a join between excel range and access tables.

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

;Permitir unha única instancia da aplicación
#include <Misc.au3>
;_Singleton("ADO_Update_Excel_From_Access", 0)
If _Singleton("ADO_Update_Excel_From_Access", 1) = 0 Then
   MsgBox($MB_SYSTEMMODAL, "Warning", "An occurrence of test is already running")
   Exit
EndIf

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

#===== CONFIG =====
Global $sFilePath = @ScriptDir & "\test.xlsx"
Global $sFilePath2 =  @ScriptDir & "\test.mdb"
Global $testmdb = "[;Database=" & $sFilePath2 & ";PWD=123]"
;~ Global $excel = "[Excel 12.0 Xml;HDR=NO;IMEX=1;DATABASE=" & $sFilePath& "]"
;Global $testsqlserver = "[odbc;Driver={SQL Server};SERVER=10.0.0.99;DATABASE=MyDatabaseName;UID=MyUser;PWD=MyPassword]"

#===== ADODB =====
Global $cn, $rst, $sSQL, $SubSQL

;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=" & $sFilePath2 & ";Jet OLEDB:Database Password=123"
;Global $sADOConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $sFilePath2 & ";Jet OLEDB:Database Password=123"
;Global $sADOConnectionString = 'Driver={Microsoft Access Driver (*.mdb)};Dbq=' & $sFilePath2 & ';uid=;pwd=MyPassword;'
;~ ;Global $sADOConnectionString = 'Provider=SQLOLEDB;Data Source=10.0.0.99;Initial Catalog=MyDatabaseName;User Id=MyUser;Password=MyPassword;'
;~ ;Or if you’re using native client:
;~ ;stConnect = "Provider=SQLNCLI10;Data Source=...

;http://www.connectionstrings.com/
;Xlsx files: Excel 2007 (and later) files with the Xlsx file extension
;[Also valid for] Using the Office 2007 OLEDB driver (ACE 12.0) to connect to older 97-2003 Excel workbooks
;cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & RutaXls & ";Extended Properties=Excel 12.0 Xml;"
Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilePath & ";Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
;Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilePath & ";Extended Properties=""Excel 12.0;HDR=NO;IMEX=1"";"
;Global $sADOConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & $sFilePath & ";Extended Properties=Excel 8.0;"
;Global $sADOConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & $sFilePath & ";ReadOnly=0;"

;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 = 30

;https://stackoverflow.com/questions/31941487/open-adodb-connection-to-excel-file-in-read-only-mode
;try Mode = adModeRead instead
;By the way, do not put adModeRead in the connections string, but just before openning your connection, add this line: rsConn.Mode = adModeRead
;I tried your suggestion, however since in VBA we do not have direct access to the ADODB built-in constants, I set rsCon.Mode = 1
;as defined in the file adovbs.inc located in the folder "C:\Program Files\Common Files\System\ado"
;and although I watched the rsCon.Mode value being set to adModeRead while debugging, I still have the same problem and the application tries to access the file in Write/Edit 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)

$sSQL = "UPDATE (([Sheet1$A2:C11] a" _
  & " INNER JOIN " & $testmdb & ".[Order_Details] b ON a.F1 = b.ID)" _
  & " INNER JOIN " & $testmdb & ".[Orders] c ON b.ID = c.ID)" _
  & " INNER JOIN " & $testmdb & ".[Customers] d ON c.CustomerID = d.ID" _
  & " SET a.F2 = c.OrderDate, a.F3 = d.CompanyName;"
$cn.Execute($sSQL, Default, 1 + 0x80)  ;adCmdText = 1 , adExecuteNoRecords = 0x80

$sSQL = "SELECT F2, F3, Sum(Quantity * UnitPrice) As Amount" _
   & " FROM [Sheet1$A2:C11] AS a INNER JOIN " & $testmdb & ".[Order_Details] b ON a.F1 = b.ID" _
   & " GROUP BY F2, F3" _
   & " ORDER BY F2;"
$rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query

If Not $rst.EOF = True Then
   Local $rstArray = $rst.GetRows()
   _ArrayDisplay($rstArray, "Test", "", $ARRAYDISPLAY_NOROW, "", "F1|F2|F3")
   $rst.Close
   $rst = 0 ;Release the recordset object
   ;$cmd = 0
   $cn.Close ;Close the connection
   $cn = 0  ;Release the connection object

   Global $RecCount = UBound($rstArray)

   #===== 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.

   ;_DebugSetup()
   ;_DebugCOMError()
   ;water: force the Excel UDF to always start up a new instance by using: _Excel_Open(False, Default, Default, Default, True)
   ;Global $oAppl = _Excel_Open(True, False, False, Default, True)
   Global $oAppl = _Excel_Open() ;_Excel_Open(Default, Default, False, Default, Default)
   ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Open" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
   ;https://www.autoitscript.com/forum/topic/185789-solved-excel_bookopen-without-wait/?do=findComment&comment=1334509

   ;Restaurar en cada arquivo (algún Application.Run pudo cambiar)
   ;$oAppl.EnableEvents = False
   $oAppl.DisplayAlerts = False

   ;~ ;Arquivo non bloqueado
   ;~ Global $iFileExists
   ;~ For $j = 0 To 60
   ;~    $iFileExists = FileExists($sFilePath2)
   ;~    If $iFileExists Then
   ;~     While _WinAPI_FileInUse($sFilePath2)
   ;~        Sleep(1000)
   ;~     WEnd
   ;~     ExitLoop
   ;~    Else
   ;~     Sleep(1000)
   ;~    EndIf
   ;~ Next

   ;Create a new workbook with only 1 worksheet
   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 $oWorkbook = _Excel_BookOpen($oAppl, $sFilePath5, False, True)
   ;~ ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookOpen: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended)
   ;~ ;Create a new workbook with only 1 worksheet
   ;~ ;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.")

   ;Sleep(3000)
   ;~ Global $sMessage, $sMessage2
   ;~ SplashTextOn("TitleFoo", $sMessage, 580, 60, 900, 840, 1 + 4, "", 16)
   ;~ For $i = 1 To 10
   ;~    $sMessage = $sMessage & "."
   ;~    $sMessage2 = @TAB & "Pausa " & $sMessage
   ;~    ControlSetText("TitleFoo", "", "Static1", $sMessage2)
   ;~    Sleep(1000)
   ;~ Next

   $oWorkbook.UpdateLinks = 2 ;xlUpdateLinksNever

   ;Global $oSheets = $oWorkbook.Sheets
   Global $oSheet = $oWorkbook.ActiveSheet
   ;Global $oSheet = $oWorkbook.Sheets("Sheet1")
   ;MsgBox(0, "", $oSheet.Name)

   $oSheet.Range("A1:C1").Font.Bold = True
   $oSheet.Range("A1:A" & $RecCount + 1).NumberFormat = "dd/mm"
   $oSheet.Range("B1:B" & $RecCount + 1).NumberFormat = "@"

   Global $oPageSetup = $oSheet.PageSetup
   With $oPageSetup
      .PrintTitleRows = "$1:$1"
      .PrintTitleColumns = ""
      .PrintArea = ""
      .LeftHeader = "&D"
      .CenterHeader = "Report"
      .RightHeader = "&P of &N"
      ;.LeftFooter = "&F {&A}"
      .CenterFooter = ""
      .RightFooter = ""
      .LeftMargin =  28
      .RightMargin =  28
      .TopMargin =  28
      .BottomMargin =  28
      .HeaderMargin =  15
      .FooterMargin =  15
      .PrintHeadings = False
      .PrintGridlines = True
      .PrintComments = -4142
      .CenterHorizontally = False
      .CenterVertically = False
      .Orientation = 1 ;2
      .Draft = False
      .FirstPageNumber = -4105
      .Order = 1
      .BlackAndWhite = True
      .Zoom = 100
   EndWith

   ;https://www.autoitscript.com/forum/topic/195252-_excel_rangewrite-doesnt-write-array-from-adodb-getrows/
   Global $TrstArray = $rstArray
   _ArrayTranspose($TrstArray)
   $oSheet.Range("A2:C" & $RecCount + 1).Value = $TrstArray

   Global $aArray2D[1][4] = [["Date", "Client", "Amount"]]
   _Excel_RangeWrite($oWorkbook, $oSheet, $aArray2D, $oSheet.Cells(1, 1))
   ;Global $aArray1D[11] = ["ID", "Udes", "Descrip", "Matricula", "Kilos", "Proveedor", "Corredor", "Fecha", "Contrato", "Restan", "Tanque"]
   ;$oSheet.Range("A1:K1").value = $aArray1D
   ;Global $aArray2D[1][6] = [[$rstArray[$i][1], $rstArray[$i][2], $rstArray[$i][3], $rstArray[$i][4], $rstArray[$i][5], $rstArray[$i][6]]]
   ;_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray2D, $oSheet.Cells($UltimaFila, 1).Resize(1, 6))
   ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_RangeWrite: " & @CRLF & "@error = " & @error & ", @extended = " & @extended)
   ;Global $aArray2D[3][5] = [[11, 12, 13, 14, 15], [21, 22, 23, 24, 25], [31, 32, 33, 34, 35]]
   ;_Excel_RangeWrite($oWorkbook, Default, $aArray2D, "B1")
   ;Local $aArray1D[13] = ["Ped", "Archivo", "Abono", "NomCli", "H+I", "ACIDEZ", "CERAS", "E+U", "aa", "aa", "aa", "aa", "aa"]
   ;$oSheet.Range("A1:M1").value = $aArray1D
   ;$oSheet.Cells(1, 1).Resize(1, 13).value = $aArray1D

   ;_Excel_BookSaveAs($oWorkbook, $sFilePath, $xlOpenXMLWorkbook, True) ;$xlOpenXMLWorkbook  51  ;$xlExcel8  56
   ;_Excel_BookClose($oWorkbook, False)
   ;_Excel_BookClose($oWorkbook, True)
   ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookClose: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended)

   ;~ While _WinAPI_FileInUse($sFilePath5)
   ;~    Sleep(1000)
   ;~ Wend
   ;~ Sleep(3000)

   ;$oAppl.EnableEvents = True
   $oAppl.DisplayAlerts = True

   ;https://www.autoitscript.com/forum/topic/136414-excel-close-problem/?do=findComment&comment=953433
   ;$oAppl.Application.Quit
   ;$oAppl = ""
   ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/
   ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1262478
   ;Run(@ComSpec & " /c " & 'taskkill /im excel.exe /f /t', "", @SW_HIDE)
   ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1262830
   ;water / Ok. Let's see if the problem is caused by open/close or by working with a workbook. Could you please try:
   ;#include <Excel.au3>
   ;$oExcel = _Excel_Open(False, False, False, False, True)
   ;$oExcelClose = _Excel_Close($oExcel, False, True)
   ;~ _Excel_Close($oAppl, False, Default)
   ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Close" & @CRLF & "@error = " & @error & ", @extended = " & @extended)

   ;~ Sleep(1000)

   ;~ ;Check excel closed
   ;~ Local $aProcesses = ProcessList("Excel.exe")
   ;~ ;_ArrayDisplay($aProcesses)
   ;~ If $aProcesses[0][0] > 0 Then
   ;~    ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1263191
   ;~    ;@water, thanks for your help so far, at least we pinned down that it's not a UDF bug. :)
   ;~    ;For now I will use a crude workaround by closing the most recent Excel.exe instance:
   ;~    ProcessClose($aProcesses[$aProcesses[0][0]][1])
   ;~    Sleep(100) ;just to allow some time for the process to definitely close (if it does close)
   ;~ EndIf
Else
   $rst.Close
   $rst = 0 ; Release the recordset object
   $cn.Close ; Close the connection
   $cn = 0 ; Release the connection object
   ;Disconnect

   MsgBox(262144, "", "Empty Recordset", 5)
EndIf

;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 _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

   ;Devolver datos error
   Local $sFilePath = @DesktopDir &  "\error.txt"
   ;Open the file for write access.
   Local $hFileOpen = FileOpen($sFilePath, 2)
   ;If $hFileOpen = -1 Then
     ;MsgBox(0, "", "An error occurred when reading/writing the file.")
   ;EndIf

   FileWrite($hFileOpen, "ADODB COM Error" & Chr(1) & _
          "err.description is: "    & @TAB & $errADODB.description    & Chr(1) & _
          "err.windescription:"     & @TAB & $errADODB.windescription & Chr(1) & _
          "err.number is: "         & @TAB & hex($errADODB.number,8)  & Chr(1) & _
          "err.lastdllerror is: "   & @TAB & $errADODB.lastdllerror   & Chr(1) & _
          "err.scriptline is: "     & @TAB & $errADODB.scriptline     & Chr(1) & _
          "err.source is: "         & @TAB & $errADODB.source         & Chr(1) & _
          "err.helpfile is: "       & @TAB & $errADODB.helpfile       & Chr(1) & _
          "err.helpcontext is: "    & @TAB & $errADODB.helpcontext _
         )

   ;Close the handle returned by FileOpen.
   FileClose($hFileOpen)

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

   Exit
EndFunc

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

 

example_files.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

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Cristin
      Dear all,
      first of all hi.
      I`m in trouble, again 😕
      I can`t find right solution, tried all possible combinations generated by my mind and nothing.
      I need to copy range of values depending by values from two different cells in excel sheet.
      I put an example in attachment.

      I have two values which is never changed (is changed only TEXT). I need to copy all the rows from VALUE 1 to VALUE 2 in new Sheets (Output 1, Output 2, ... , Output X).
      It is possible to do this using Autoit? 🤔
      Also in attachment you can find last example which I have tried and it`s something near, it is copying by _Excel_RangeFind row with needed VALUE 1 but I need to copy whole range from VALUE 1 to VALUE 2 😵 ( see in attachment examples).
      Thank you very much, in advance, for your support, solution really will make my life much easier 🙃
      Have a nice day all of you!
      Best Regards,
      Cristin
      Book1.xlsx something.au3
    • By mLipok
      I want to present BETA Version of my ADO.au3 UDF.
      This is modifed version of _sql.au3 UDF.
       
      For that I want to thanks : ; Chris Lambert, eltorro, Elias Assad Neto, CarlH
       
      This is first public release , and still is as BETA
       
       
      DOWNLOAD LINK (in download section): 
       
       
      Have fun,
      mLipok
       
       
      EDIT: 2016-06-03
      Below some interesting topics about databases:
       
       
      EDIT 2016/07/04:
      For more info about ADO look here:
      https://www.autoitscript.com/wiki/ADO
      FOR EXAMPLE DATABASE use AdventureWorksDW2016_EXT.bak from:
      https://github.com/microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorksDW2016_EXT.bak
      I will relay on this database in my examples.
       
    • By WindIt
      Hi, 
      I have a set of excel docs 1& 2
      1) In Excel 1 i have a fixed column called dimensionId that contains list of dimensionid's with in between empty cells.
      Here, For each dimesionId, i need to check corresponding columns of L,M,N ,O(senior, mid, junior, student) & check If they contain letter Y (Open excel 2) and if empty ignore,check next.
      I need to loop through the entire column of dimensionId one by one, please help me proceed with the logic.
      2) If letter Y exists, open excel 2 and check if same dimesionId exists in column A.
      New to autoit excel automation, Help me with logic to execute this.
      #include <Excel.au3>
      Global $oExcel = _Excel_Open()
      Global $oWorkbook = _Excel_BookOpen($oExcel, "D:\yta\Trial concept dimensions list.xlsx")
      Local $read1 = _Excel_RangeRead ($oWorkbook,Default,"C3")
      MsgBox(0,"",$read1)
      Attached exce 1 doc.
       
       
      exl1.xlsx exl2.xlsx
    • By Zaoka
      Hi,
      is it possible to break link in active/open $oWorkbook0 with excel.udf function ? If some could link some example...
      Data-Edit Links-Break Link

       
    • By chaddro
      First post, be gentle
      I am working on a script that will eventually run via a batch file with Task Scheduler. 
      The purpose of the script is to query SQL Server and save the results as .xlsx files.  However, when I run the script either from the editor or compiled file, the excel windows open and I have to close them for the script to continue. I did not have this issue initially, the excel windows would open and then close on their own.
      Below is code from one section of the script where I am creating my spreadsheets. This code creates a single spreadsheet with a list of invoices for that day:
      ; Query SQL Server for List of Invoices dated $aDocumentDate $iReturnVal1 = _SQL_GetTable2D(-1,$aQuery,$aData,$iRows,$iColumns) If $iReturnVal1 = $SQL_OK Then ; Display Query Results in Array ; _arrayDisplay($aData,"2D (" & $iRows & " Rows) (" & $iColumns & " Columns)" ) ; Create Excel file name based on date of query $excelFileNameList1 = "C:\pride\daily\invoices_" & $aDocumentDate & ".xlsx" ; Open Existing or Create .xlsx file Local $oExcel_1 = _Excel_Open(False) Local $oWorkbook = _Excel_BookNew($oExcel_1, 1) ; Write Query results to Excel Spreadsheet and save _Excel_RangeWrite($oWorkbook,Default, $aData, "A1", True, True) _Excel_BookSaveAs($oWorkbook, $excelFileNameList1, Default, True) ; Close the book and close Excel handle _Excel_BookClose($oWorkbook, True) _Excel_Close($oExcel_1) EndIf This code then creates a separate spreadsheet for each invoice and saves:
      ;============[ $aData holds list of Invoices Local $aInvoiceNumber ; Invoice Number for Query Local $aData2, $iRows2, $iColumns2 ; Read Query from file to save space here.... WORKING ... query must all be on one line! Local $aQueryFromFile = FileRead("c:\pride\invoice.sql",FileGetSize("c:\pride\invoice.sql")) Local $aQuery2 For $i = 1 to $iRows $aInvoiceNumber = $aData[$i][0] ; invoice number to query $aQuery2 = $aQueryFromFile & $aInvoiceNumber & ";" ; Display query to console ; ConsoleWrite(@lf & $aQuery2 & @lf) ; Get Invoice Data $iReturnVal2 = _SQL_GetTable2D(-1,$aQuery2,$aData2,$iRows2,$iColumns2) If $iReturnVal2 = $SQL_OK Then ; show query result ;_arrayDisplay($aData2,"2D (" & $iRows2 & " Rows) (" & $iColumns2 & " Columns)" ) ; check invoice file name ;ConsoleWrite(@LF & "Data for invoice: " & $aInvoiceNumber & @LF) ; Create file name for invoice spreadsheet $excelFileName2 = "C:\pride\invoices\invoice_" & $aInvoiceNumber & ".xlsx" ; Open Existing or Create .xlsx file Local $oExcel_2 = _Excel_Open(False) ; create excel instancee Local $oWorkbook2 = _Excel_BookNew($oExcel_2, 1) ; Create new workbook with 1 sheet ; Write query results to Excel Sheet ; Note: we must use $bforceFunc TRUE to bypass Excel Limitations _Excel_RangeWrite($oWorkbook2, $oWorkbook2.Activesheet, $aData2, "A1", True, True) If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite ", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oExcel_2) Exit EndIf _Excel_BookSaveAs($oWorkbook2, $excelFileName2, Default, True) _Excel_BookClose($oWorkbook2, True) _Excel_Close($oExcel_2) EndIf Next One thing to note: I am using $bForceFunc = True in _Excel_RangeWrite because I may have strings longer than 255 character. Also, I am using Office 2010 if that matters. I am also compiling for command line.
      Pardon the diagnostic code, this is my first autoit script
      Thank you in advance for your help!
×
×
  • Create New...