Jump to content

Recommended Posts

This is my "template"

#include <Array.au3>
#include <File.au3>
#include <Excel.au3>
#include <WinAPIFiles.au3>
;#include "..\..\..\SmtpMailer_mLipok\SmtpMailer.au3"

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

;If MsgBox(324, "", "Do you want to proceed?") <> 6 Then Exit

#===== CONFIG =====
Global $sTxt
Global $CleanLog = True
Global $sFilePath2 =  @DesktopDir & "\Test.xlsx"
Global $hFile = FileOpen(@DesktopDir & "\Events_Test.log", 2)

#===== ADODB =====
;Evitar errores en OnAutoItExit
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 = 1 ;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
;~ ;https://accessexperts.com/blog/2011/03/24/sql-server-connections-strings-for-microsoft-access/
;~ ;You should bypass the ODBC layer altogether when connecting to SQL Sever by using a connection string similar to this one in your code:
;~ ;stConnect = "Provider=SQLOLEDB;Data Source=...
;~ ;Or if you’re using native client:
;~ ;stConnect = "Provider=SQLNCLI10;Data Source=...
Global $sADOConnectionString = 'Provider=SQLOLEDB;Data Source=10.0.0.99;Initial Catalog=MyDatabaseName;User Id=sa;Password=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 = 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)

;https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2007/bb221186(v=office.12)
;UPDATE does not generate a result set. Also, after you update records using an update query, you cannot undo the operation.
;If you want to know which records were updated, first examine the results of a select query that uses the same criteria, and then run the update query.
;~ $sSQL = "UPDATE MYTABLE SET MYFIELD = ' ' WHERE REFERENCIA IS MYFIELD"
;~ $cn.Execute($sSQL, Default, 1 + 0x80)  ;adCmdText = 1 , adExecuteNoRecords = 0x80
;~ ;$rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions)

;~ $cn.Close ;Close the connection
;~ $cn = 0  ;Release the connection object

$sSQL = "SELECT ..." _
    & " FROM MYTABLE a" _
    & " WHERE ..." _
    & " ORDER BY ...;"
$rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query

If Not $rst.EOF = True Then
    Global $rstArray = $rst.GetRows()
    ;_ArrayDisplay ($rstArray, "", "", $ARRAYDISPLAY_NOROW, "", "NomProv|Contrato|Fecha|Aceite|Eur|Kg")
    $rst.Close
    Global $RecCount = UBound($rstArray)

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

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

    $oAppl.EnableEvents = False
    $oAppl.DisplayAlerts = False

    ;Global $oWorkbook = _Excel_BookOpen($oAppl, $sFilePath2, 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.")

    ;Mellor facer unha pausa para asegurar que cargue Ribbon, etc
    Sleep(3000)
    $oWorkbook.UpdateLinks = 2 ;xlUpdateLinksNever


    ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1263179
    ;water
    ;Seems Excel doesn't like double dot notation. "chaining" object references seems to be a problem.
    ;So I changed the code to only have single dot notation. Could you please test this version?
        ;https://www.add-in-express.com/creating-addins-blog/2013/11/05/release-excel-com-objects/

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

    ;http://www.siddharthrout.com/index.php/2018/02/10/find-last-row-and-last-column/
    ;Find Last Row and Last Column. Siddharth Rout, Feb 2018
    ;~ Global $LastRow = $oSheet.Cells(5000, 1).End(-4162).Row
    ;$UltimaFila = $oSheet.Cells(23, 1).End(-4121).Row
    ;MsgBox(0, "", $UltimaFila)
    ;$oWorkbook.Sheets.Item(2).Activate

    ;$oSheet.Range("I4").value = "ABC"
    ;$oSheet.Range("J25:K25").Value = 100
    ;$oSheet.PrintOut

    ;~ Global $aResult = _Excel_RangeRead($oWorkbook, Default, $oSheet.Range("A9:E" & $LastRow), 1)
    ;If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

    $oAppl.ActiveWindow.DisplayZeros = False
    $oAppl.ActiveWindow.DisplayGridlines = False

    $oSheet.Columns("A:B").ColumnWidth = 8
    $oSheet.Columns(3).ColumnWidth = 10
    If $iEventError Then
        Consolewrite("+--> Error ColumnWidth" & @CRLF)
        $iEventError = 0 ; Reset after displaying a COM Error occurred
    EndIf

    Global $Rows = $RecCount + 1
    $oSheet.Range("A2:B" & $Rows).HorizontalAlignment = -4108
    $oSheet.Range("B2:B" & $Rows).NumberFormat = "dd/mm;@"
    $oSheet.Range("C2:C" & $Rows).NumberFormat = "#.##0,00"

    Global $aArray1D[3] = ["Fra", "Fecha", "Total"]
    Global $oRange = $oSheet.Range("A1:C1")
    If $iEventError Then
        Consolewrite("+--> Error: $oSheet.Range('A1:C1')" & @CRLF)
        $iEventError = 0 ; Reset after displaying a COM Error occurred
    EndIf
    With $oRange
        .HorizontalAlignment = -4108
        .value = $aArray1D
        ;.Font.Bold = True
        ;.AutoFilter
    EndWith

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

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

    ;water, Aug 2016
    ;https://www.autoitscript.com/forum/topic/184041-excel-formatting-cellsborders/?do=findComment&comment=1321855
    ;XlListObjectSourceType Enumeration - https://msdn.microsoft.com/en-us/library/ff820815(v=office.14).aspx
    Global $xlSrcRange = 1
    With $oSheet
        .ListObjects.Add($xlSrcRange, .Range("A1:C" & $Rows), Default, $xlYes).Name = "TB_Datos"
        .ListObjects("TB_Datos").TableStyle = "TableStyleMedium6"
    EndWith

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

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

    While _WinAPI_FileInUse($sFilePath2)
        Sleep(1000)
    Wend
    Sleep(3000)

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

    ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Close" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    ;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(5000) ;deixar tempo para cerrar proceso

    ;~    ;Check if excel is closed
    ;~    Local $aProcesses = ProcessList("Excel.exe")
    ;~    ;_ArrayDisplay($aProcesses)
    ;~    If $aProcesses[0][0] > 0 Then
    ;~    Local $sFilePath = @DesktopDir &  "\error_Excel_Close_COMPRAS.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, "Error:" & @CRLF & _
    ;~       ;~    "Excel_Close COMPRAS" _
    ;~       ;~    )
    ;~       ;~ ;Close the handle returned by FileOpen.
    ;~       ;~ FileClose($hFileOpen)
    ;~    ;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

    Global $oStream = ObjCreate("ADODB.Stream")
    $oStream.Type = 2 ; adTypeText
    $oStream.Charset = "utf-8"
    $oStream.LineSeparator = -1 ; adCRLF
    $oStream.Open
    ;https://msdn.microsoft.com/en-us/library/ms678072(v=vs.85).aspx
    ;StreamWriteEnum: 1 adWriteLine
    $oStream.WriteText("<html>", 1)
    $oStream.WriteText("<head>", 1)
    $oStream.WriteText("<style>", 1)
    $oStream.WriteText(".table-PedProd {table-layout: fixed; border-collapse: separate; border-spacing: 1px;}", 1)
    ;$oStream.WriteText(".table-PedProd {border: 1px solid #ddd; table-layout: fixed; border-collapse: collapse;}", 1)
    ;border: 1px solid #999
    $oStream.WriteText(".table-PedProd th {border: 1px solid #e7e7e7; text-align:center; background-color: #f3f3f3; padding: 0.5rem 0.5rem; word-wrap: break-word;}", 1)
    ;$oStream.WriteText(".table-PedProd th, .table-PedProd td {border: 1px solid #ddd; padding: 0.5rem 0.5rem; word-wrap: break-word;}", 1)
    ;text-align: left; padding-top: 0.1rem; padding-bottom: 0.1rem;
    ;$oStream.WriteText(".table-PedProd tr {vertical-align: top;}", 1)
    $oStream.WriteText(".table-PedProd tr:nth-child(even){background-color: #f5f5f0}", 1)
    ;$oStream.WriteText("#Pedidos tr:nth-child(even){background-color: #f2f2f2}", 1)
    ;$oStream.WriteText("tr td:nth-child(1) {text-align:left;}", 1)
    ;$oStream.WriteText("tr table td:nth-child(2) {text-align:right;}", 1)
    ;$oStream.WriteText("tr.r1r {background-color:#F5F5F0}", 1)
    ;$oStream.WriteText("tr.r2r {background-color:#FFFFFF}", 1)
    ;$oStream.WriteText("tr.r3r {background-color:#f2f2f2}", 1)
    $oStream.WriteText(".highlight {background: yellow;}", 1)
    $oStream.WriteText("</style>", 1)
    $oStream.WriteText("</head>", 1)
    $oStream.WriteText("<body>", 1)

    $oStream.WriteText("<table class='table-PedProd' style='width: 300px;'>", 1)
    $oStream.WriteText("<thead><tr>" _
        & "<th style='width: 30%'>Factura</th>" _
        & "<th style='width: 30%'>Fecha</th>" _
        & "<th style='width: 40%'>Importe</th>" _
        & "</tr></thead>", 1)
    $oStream.WriteText("<tbody>", 1)

    For $i = 0 To UBound($rstArray) - 1
        $oStream.WriteText("<tr><td style='text-align:center; word-wrap: break-word;'>" & $rstArray[$i][0] & "</td>", 1)
        $oStream.WriteText("<td style='text-align:center; word-wrap: break-word;'>" & StringLeft($rstArray[$i][1], 5) & "</td>", 1)
        $oStream.WriteText("<td style='text-align:right; word-wrap: break-word; padding-right: 4px;'>" & $rstArray[$i][2] & "</td></tr>", 1)
    Next

    $oStream.WriteText("</tbody>", 1)
    $oStream.WriteText("</table>", 1)

    $oStream.WriteText("</body>", 1)
    $oStream.WriteText("</html>", 1)
    ;Local $sFilePathHTML = @DesktopDir & "\temp.html"
    ;$oStream.SaveToFile($sFilePathHTML, 2) ;adSaveCreateOverWrite
    ;After a call to this method, the current position in the stream is set to the beginning of the stream (Position=0).
    ;Noutro caso sería necesario esto para ReadText
    $oStream.Position = 0

;~  ;Send mail
;~  Global $sSmtpServer = "mail.mydomain.com"
;~  Global $sUsername = "myname@mydomain.com"
;~  Global $sPassword = "MyPassword"
;~  Global $sFromName = "My Name"
;~  Global $sFromAddress = "myname@mydomain.com"
;~  Global $sToAddress, $sSubject, $sBody, $sAttachFiles, $sCcAddress, $sBccAddress
;~  Global $sImportance = "High" ;Send message priority: "High", "Normal", "Low"
;~  Global $iIPPort = 25
;~  Global $bSSL = False
;~  ; Local $iIPPort = 465 ; GMAIL port used for sending the mail
;~  ; Local $bSSL = True ; GMAIL enables/disables secure socket layer sending - set to True if using https
;~  Global $bIsHTMLBody = True
;~  Global $iDSNOptions = $g__cdoDSNDefault
;~  Global $sResultDescription

;~  $sToAddress = "toaddress@somedomain.com"
;~  $sSubject = "Report"
;~  $sBody = $oStream.ReadText
;~  $sAttachFiles = $sFilePath2 ;seperated with a ; (Semicolon) - leave blank if not needed
;~  $sCcAddress = "myaddress@mydomain.com"
;~  $sBccAddress = ""
;~  $sResultDescription = _SMTP_SendEmail($sSmtpServer, $sUsername, $sPassword, $sFromName, $sFromAddress, $sToAddress, $sSubject, $sBody, $sAttachFiles, $sCcAddress, $sBccAddress, $sImportance, $iIPPort, $bSSL, $bIsHTMLBody, $iDSNOptions)
;~  If Not @error Then
;~      MsgBox(262144, "Sent", "Sent", 2)
;~      SoundPlay(@WindowsDir & "\media\tada.wav", 0) ; Chimes.wav
;~      $sTxt = "OK"
;~      Consolewrite($sTxt & @CRLF)
;~      _FileWriteLog($hFile, $sTxt)
;~      $sSms = $sTxt
;~  ElseIf @error = $SMTP_ERR_SEND Then
;~      ConsoleWrite("! Number: " & _SMTP_COMErrorHexNumber() & "  UDF Script Line: " & _SMTP_ComErrorScriptLine() & "   Description:" & _SMTP_COMErrorDescription() & @LF)
;~      MsgBox(262144, "Error sending email", "_SMTP_SendEmail()" & @CRLF & "Error code: $SMTP_ERR_SEND" & @CRLF & "Description:" & $sResultDescription & @CRLF & "COM Error Number: "& _SMTP_COMErrorHexNumber())
;~      $sTxt = "Err email"
;~      Consolewrite($sTxt & @CRLF)
;~      _FileWriteLog($hFile, $sTxt)
;~      $sSms = $sTxt
;~      $CleanLog = False
;~  EndIf

    $oStream.Close
    $oStream = 0

    FileRecycle($sFilePath2)
Else
    $rst.Close
    $rst = 0 ; Release the recordset object
    $cn.Close ; Close the connection
    $cn = 0 ; Release the connection object

    $sTxt = "Not found"
    Consolewrite($sTxt & @CRLF)
    _FileWriteLog($hFile, $sTxt)
    $CleanLog = False
    MsgBox(262144, "", $sTxt & @CRLF & "script line: " & @ScriptLineNumber , 5)
EndIf

;Melba23, Jul 2014, https://www.autoitscript.com/forum/topic/162514-how-to-add-the-thousand-separator/?do=findComment&comment=1181643
Func _AddPoint($iNumber)
    Local $iLen = StringLen($iNumber)
    If $iLen < 7 Then
        Return StringMid($iNumber, 1, $iLen - 3) & "." & StringMid($iNumber, $iLen - 2)
    Else
        Return StringMid($iNumber, 1, $iLen - 6) & "." & StringMid($iNumber, $iLen - 5, 3) & "." & StringRight($iNumber, 3)
    EndIf
EndFunc

Func _ErrADODB()
    $sTxt = "Err ADO: " & $errADODB.description
    Consolewrite($sTxt & @CRLF)
    _FileWriteLog($hFile, $sTxt)
    $CleanLog = False

    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

    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

    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

    $sTxt = "Err Excel"
    Consolewrite($sTxt & @CRLF)
    _FileWriteLog($hFile, $sTxt)
    $CleanLog = False
EndFunc   ;==>ErrFunc

Func OnAutoItExit()
   FileClose($hFile)
   If $CleanLog Then FileRecycle(@DesktopDir & "\Events_Test.log")

    $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

 

Link to post
Share on other sites
23 hours ago, robertocm said:

This is my "template"

#include <Array.au3>
#include <File.au3>
#include <Excel.au3>
#include <WinAPIFiles.au3>
;#include "..\..\..\SmtpMailer_mLipok\SmtpMailer.au3"

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

;If MsgBox(324, "", "Do you want to proceed?") <> 6 Then Exit

#===== CONFIG =====
Global $sTxt
Global $CleanLog = True
Global $sFilePath2 =  @DesktopDir & "\Test.xlsx"
Global $hFile = FileOpen(@DesktopDir & "\Events_Test.log", 2)

#===== ADODB =====
;Evitar errores en OnAutoItExit
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 = 1 ;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
;~ ;https://accessexperts.com/blog/2011/03/24/sql-server-connections-strings-for-microsoft-access/
;~ ;You should bypass the ODBC layer altogether when connecting to SQL Sever by using a connection string similar to this one in your code:
;~ ;stConnect = "Provider=SQLOLEDB;Data Source=...
;~ ;Or if you’re using native client:
;~ ;stConnect = "Provider=SQLNCLI10;Data Source=...
Global $sADOConnectionString = 'Provider=SQLOLEDB;Data Source=10.0.0.99;Initial Catalog=MyDatabaseName;User Id=sa;Password=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 = 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)

;https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2007/bb221186(v=office.12)
;UPDATE does not generate a result set. Also, after you update records using an update query, you cannot undo the operation.
;If you want to know which records were updated, first examine the results of a select query that uses the same criteria, and then run the update query.
;~ $sSQL = "UPDATE MYTABLE SET MYFIELD = ' ' WHERE REFERENCIA IS MYFIELD"
;~ $cn.Execute($sSQL, Default, 1 + 0x80)  ;adCmdText = 1 , adExecuteNoRecords = 0x80
;~ ;$rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions)

;~ $cn.Close ;Close the connection
;~ $cn = 0  ;Release the connection object

$sSQL = "SELECT ..." _
    & " FROM MYTABLE a" _
    & " WHERE ..." _
    & " ORDER BY ...;"
$rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query

If Not $rst.EOF = True Then
    Global $rstArray = $rst.GetRows()
    ;_ArrayDisplay ($rstArray, "", "", $ARRAYDISPLAY_NOROW, "", "NomProv|Contrato|Fecha|Aceite|Eur|Kg")
    $rst.Close
    Global $RecCount = UBound($rstArray)

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

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

    $oAppl.EnableEvents = False
    $oAppl.DisplayAlerts = False

    ;Global $oWorkbook = _Excel_BookOpen($oAppl, $sFilePath2, 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.")

    ;Mellor facer unha pausa para asegurar que cargue Ribbon, etc
    Sleep(3000)
    $oWorkbook.UpdateLinks = 2 ;xlUpdateLinksNever


    ;https://www.autoitscript.com/forum/topic/166043-close-the-entire-application-of-excel/?do=findComment&comment=1263179
    ;water
    ;Seems Excel doesn't like double dot notation. "chaining" object references seems to be a problem.
    ;So I changed the code to only have single dot notation. Could you please test this version?
        ;https://www.add-in-express.com/creating-addins-blog/2013/11/05/release-excel-com-objects/

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

    ;http://www.siddharthrout.com/index.php/2018/02/10/find-last-row-and-last-column/
    ;Find Last Row and Last Column. Siddharth Rout, Feb 2018
    ;~ Global $LastRow = $oSheet.Cells(5000, 1).End(-4162).Row
    ;$UltimaFila = $oSheet.Cells(23, 1).End(-4121).Row
    ;MsgBox(0, "", $UltimaFila)
    ;$oWorkbook.Sheets.Item(2).Activate

    ;$oSheet.Range("I4").value = "ABC"
    ;$oSheet.Range("J25:K25").Value = 100
    ;$oSheet.PrintOut

    ;~ Global $aResult = _Excel_RangeRead($oWorkbook, Default, $oSheet.Range("A9:E" & $LastRow), 1)
    ;If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

    $oAppl.ActiveWindow.DisplayZeros = False
    $oAppl.ActiveWindow.DisplayGridlines = False

    $oSheet.Columns("A:B").ColumnWidth = 8
    $oSheet.Columns(3).ColumnWidth = 10
    If $iEventError Then
        Consolewrite("+--> Error ColumnWidth" & @CRLF)
        $iEventError = 0 ; Reset after displaying a COM Error occurred
    EndIf

    Global $Rows = $RecCount + 1
    $oSheet.Range("A2:B" & $Rows).HorizontalAlignment = -4108
    $oSheet.Range("B2:B" & $Rows).NumberFormat = "dd/mm;@"
    $oSheet.Range("C2:C" & $Rows).NumberFormat = "#.##0,00"

    Global $aArray1D[3] = ["Fra", "Fecha", "Total"]
    Global $oRange = $oSheet.Range("A1:C1")
    If $iEventError Then
        Consolewrite("+--> Error: $oSheet.Range('A1:C1')" & @CRLF)
        $iEventError = 0 ; Reset after displaying a COM Error occurred
    EndIf
    With $oRange
        .HorizontalAlignment = -4108
        .value = $aArray1D
        ;.Font.Bold = True
        ;.AutoFilter
    EndWith

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

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

    ;water, Aug 2016
    ;https://www.autoitscript.com/forum/topic/184041-excel-formatting-cellsborders/?do=findComment&comment=1321855
    ;XlListObjectSourceType Enumeration - https://msdn.microsoft.com/en-us/library/ff820815(v=office.14).aspx
    Global $xlSrcRange = 1
    With $oSheet
        .ListObjects.Add($xlSrcRange, .Range("A1:C" & $Rows), Default, $xlYes).Name = "TB_Datos"
        .ListObjects("TB_Datos").TableStyle = "TableStyleMedium6"
    EndWith

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

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

    While _WinAPI_FileInUse($sFilePath2)
        Sleep(1000)
    Wend
    Sleep(3000)

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

    ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Close" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    ;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(5000) ;deixar tempo para cerrar proceso

    ;~    ;Check if excel is closed
    ;~    Local $aProcesses = ProcessList("Excel.exe")
    ;~    ;_ArrayDisplay($aProcesses)
    ;~    If $aProcesses[0][0] > 0 Then
    ;~    Local $sFilePath = @DesktopDir &  "\error_Excel_Close_COMPRAS.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, "Error:" & @CRLF & _
    ;~       ;~    "Excel_Close COMPRAS" _
    ;~       ;~    )
    ;~       ;~ ;Close the handle returned by FileOpen.
    ;~       ;~ FileClose($hFileOpen)
    ;~    ;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

    Global $oStream = ObjCreate("ADODB.Stream")
    $oStream.Type = 2 ; adTypeText
    $oStream.Charset = "utf-8"
    $oStream.LineSeparator = -1 ; adCRLF
    $oStream.Open
    ;https://msdn.microsoft.com/en-us/library/ms678072(v=vs.85).aspx
    ;StreamWriteEnum: 1 adWriteLine
    $oStream.WriteText("<html>", 1)
    $oStream.WriteText("<head>", 1)
    $oStream.WriteText("<style>", 1)
    $oStream.WriteText(".table-PedProd {table-layout: fixed; border-collapse: separate; border-spacing: 1px;}", 1)
    ;$oStream.WriteText(".table-PedProd {border: 1px solid #ddd; table-layout: fixed; border-collapse: collapse;}", 1)
    ;border: 1px solid #999
    $oStream.WriteText(".table-PedProd th {border: 1px solid #e7e7e7; text-align:center; background-color: #f3f3f3; padding: 0.5rem 0.5rem; word-wrap: break-word;}", 1)
    ;$oStream.WriteText(".table-PedProd th, .table-PedProd td {border: 1px solid #ddd; padding: 0.5rem 0.5rem; word-wrap: break-word;}", 1)
    ;text-align: left; padding-top: 0.1rem; padding-bottom: 0.1rem;
    ;$oStream.WriteText(".table-PedProd tr {vertical-align: top;}", 1)
    $oStream.WriteText(".table-PedProd tr:nth-child(even){background-color: #f5f5f0}", 1)
    ;$oStream.WriteText("#Pedidos tr:nth-child(even){background-color: #f2f2f2}", 1)
    ;$oStream.WriteText("tr td:nth-child(1) {text-align:left;}", 1)
    ;$oStream.WriteText("tr table td:nth-child(2) {text-align:right;}", 1)
    ;$oStream.WriteText("tr.r1r {background-color:#F5F5F0}", 1)
    ;$oStream.WriteText("tr.r2r {background-color:#FFFFFF}", 1)
    ;$oStream.WriteText("tr.r3r {background-color:#f2f2f2}", 1)
    $oStream.WriteText(".highlight {background: yellow;}", 1)
    $oStream.WriteText("</style>", 1)
    $oStream.WriteText("</head>", 1)
    $oStream.WriteText("<body>", 1)

    $oStream.WriteText("<table class='table-PedProd' style='width: 300px;'>", 1)
    $oStream.WriteText("<thead><tr>" _
        & "<th style='width: 30%'>Factura</th>" _
        & "<th style='width: 30%'>Fecha</th>" _
        & "<th style='width: 40%'>Importe</th>" _
        & "</tr></thead>", 1)
    $oStream.WriteText("<tbody>", 1)

    For $i = 0 To UBound($rstArray) - 1
        $oStream.WriteText("<tr><td style='text-align:center; word-wrap: break-word;'>" & $rstArray[$i][0] & "</td>", 1)
        $oStream.WriteText("<td style='text-align:center; word-wrap: break-word;'>" & StringLeft($rstArray[$i][1], 5) & "</td>", 1)
        $oStream.WriteText("<td style='text-align:right; word-wrap: break-word; padding-right: 4px;'>" & $rstArray[$i][2] & "</td></tr>", 1)
    Next

    $oStream.WriteText("</tbody>", 1)
    $oStream.WriteText("</table>", 1)

    $oStream.WriteText("</body>", 1)
    $oStream.WriteText("</html>", 1)
    ;Local $sFilePathHTML = @DesktopDir & "\temp.html"
    ;$oStream.SaveToFile($sFilePathHTML, 2) ;adSaveCreateOverWrite
    ;After a call to this method, the current position in the stream is set to the beginning of the stream (Position=0).
    ;Noutro caso sería necesario esto para ReadText
    $oStream.Position = 0

;~  ;Send mail
;~  Global $sSmtpServer = "mail.mydomain.com"
;~  Global $sUsername = "myname@mydomain.com"
;~  Global $sPassword = "MyPassword"
;~  Global $sFromName = "My Name"
;~  Global $sFromAddress = "myname@mydomain.com"
;~  Global $sToAddress, $sSubject, $sBody, $sAttachFiles, $sCcAddress, $sBccAddress
;~  Global $sImportance = "High" ;Send message priority: "High", "Normal", "Low"
;~  Global $iIPPort = 25
;~  Global $bSSL = False
;~  ; Local $iIPPort = 465 ; GMAIL port used for sending the mail
;~  ; Local $bSSL = True ; GMAIL enables/disables secure socket layer sending - set to True if using https
;~  Global $bIsHTMLBody = True
;~  Global $iDSNOptions = $g__cdoDSNDefault
;~  Global $sResultDescription

;~  $sToAddress = "toaddress@somedomain.com"
;~  $sSubject = "Report"
;~  $sBody = $oStream.ReadText
;~  $sAttachFiles = $sFilePath2 ;seperated with a ; (Semicolon) - leave blank if not needed
;~  $sCcAddress = "myaddress@mydomain.com"
;~  $sBccAddress = ""
;~  $sResultDescription = _SMTP_SendEmail($sSmtpServer, $sUsername, $sPassword, $sFromName, $sFromAddress, $sToAddress, $sSubject, $sBody, $sAttachFiles, $sCcAddress, $sBccAddress, $sImportance, $iIPPort, $bSSL, $bIsHTMLBody, $iDSNOptions)
;~  If Not @error Then
;~      MsgBox(262144, "Sent", "Sent", 2)
;~      SoundPlay(@WindowsDir & "\media\tada.wav", 0) ; Chimes.wav
;~      $sTxt = "OK"
;~      Consolewrite($sTxt & @CRLF)
;~      _FileWriteLog($hFile, $sTxt)
;~      $sSms = $sTxt
;~  ElseIf @error = $SMTP_ERR_SEND Then
;~      ConsoleWrite("! Number: " & _SMTP_COMErrorHexNumber() & "  UDF Script Line: " & _SMTP_ComErrorScriptLine() & "   Description:" & _SMTP_COMErrorDescription() & @LF)
;~      MsgBox(262144, "Error sending email", "_SMTP_SendEmail()" & @CRLF & "Error code: $SMTP_ERR_SEND" & @CRLF & "Description:" & $sResultDescription & @CRLF & "COM Error Number: "& _SMTP_COMErrorHexNumber())
;~      $sTxt = "Err email"
;~      Consolewrite($sTxt & @CRLF)
;~      _FileWriteLog($hFile, $sTxt)
;~      $sSms = $sTxt
;~      $CleanLog = False
;~  EndIf

    $oStream.Close
    $oStream = 0

    FileRecycle($sFilePath2)
Else
    $rst.Close
    $rst = 0 ; Release the recordset object
    $cn.Close ; Close the connection
    $cn = 0 ; Release the connection object

    $sTxt = "Not found"
    Consolewrite($sTxt & @CRLF)
    _FileWriteLog($hFile, $sTxt)
    $CleanLog = False
    MsgBox(262144, "", $sTxt & @CRLF & "script line: " & @ScriptLineNumber , 5)
EndIf

;Melba23, Jul 2014, https://www.autoitscript.com/forum/topic/162514-how-to-add-the-thousand-separator/?do=findComment&comment=1181643
Func _AddPoint($iNumber)
    Local $iLen = StringLen($iNumber)
    If $iLen < 7 Then
        Return StringMid($iNumber, 1, $iLen - 3) & "." & StringMid($iNumber, $iLen - 2)
    Else
        Return StringMid($iNumber, 1, $iLen - 6) & "." & StringMid($iNumber, $iLen - 5, 3) & "." & StringRight($iNumber, 3)
    EndIf
EndFunc

Func _ErrADODB()
    $sTxt = "Err ADO: " & $errADODB.description
    Consolewrite($sTxt & @CRLF)
    _FileWriteLog($hFile, $sTxt)
    $CleanLog = False

    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

    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

    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

    $sTxt = "Err Excel"
    Consolewrite($sTxt & @CRLF)
    _FileWriteLog($hFile, $sTxt)
    $CleanLog = False
EndFunc   ;==>ErrFunc

Func OnAutoItExit()
   FileClose($hFile)
   If $CleanLog Then FileRecycle(@DesktopDir & "\Events_Test.log")

    $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

 

As soon as possible i will try this solution, unfortunatly i've others priority to solve, however thanks!

Link to post
Share on other sites

Take a look on my ADO.au3 UDF in my signature.

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 for other useful stuff click the following button:

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskSchedulerIE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related:How to get reference to PDF object embeded in IE * IE on Windows 11

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

I also encourage you to check awesome @trancexx code:  * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuffOnHungApp handlerAvoid "AutoIt Error" message box in unknown errors  * HTML editor

winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2022-03-07

Link to post
Share on other sites
On 5/19/2022 at 9:23 AM, fulvio_fabiani said:

As soon as possible i will try this solution, unfortunatly i've others priority to solve, however thanks!

Hi, i tried the code but there is this error, what you suggest to do?

 

"C:\AutoIt_prj\Crm_ricerca_pratica\Crm_Ricerca_Pratica.au3" (42) : ==> The requested action with this object has failed.:
$rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions)
$rst^ ERROR

 

The code is:

 

#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3>
#include <File.au3>
#include <WinAPIFiles.au3>
#include <Date.au3>
#RequireAdmin
#include "_ImageSearch_UDF.au3"

Global $cn, $rst, $sSQL, $SubSQL
Global $errADODB = ObjEvent("AutoIt.Error","_ErrADODB")


Global Const $iCursorType = 3 ;0 adOpenForwardOnly, 3 adOpenStatic
Global Const $iLockType = 1 ;1 adLockReadOnly, 3 adLockOptimistic
Global Const $iOptions = 1 ; Options, 1 Evaluates as a textual definition of a command or stored procedure call ; 2 adCmdTable


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

$cn = ObjCreate("ADODB.Connection") ; Create a connection object
$rst = ObjCreate("ADODB.Recordset") ; Create a recordset object
Global $sADOConnectionString = 'Provider=SQLOLEDB;Data Source=192.168.20.244;Initial Catalog=AUTOMI;User Id=swpgm;Password=swpgm;'
$cn.CursorLocation = 2 ;2 adUseServer, 3 adUseClient
$cn.CommandTimeout = 30
$sSQL = "SELECT DES_AUTOMA FROM AUTOMI WHERE ID_AUTOMA = 4"
$rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions)

 

 

 

Link to post
Share on other sites

ADO.au3 UDF * give it a try.

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 for other useful stuff click the following button:

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskSchedulerIE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related:How to get reference to PDF object embeded in IE * IE on Windows 11

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

I also encourage you to check awesome @trancexx code:  * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuffOnHungApp handlerAvoid "AutoIt Error" message box in unknown errors  * HTML editor

winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2022-03-07

Link to post
Share on other sites
Posted (edited)
22 hours ago, fulvio_fabiani said:

what you suggest to do?

Open the connection:

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

$rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions)

 

See also "simplest testing code" from Zedna, Aug 6, 2020:

 

Edited by robertocm
Link to post
Share on other sites
  • 2 weeks later...

Can you be more specific ? How exactly you solved this issue ?

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 for other useful stuff click the following button:

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskSchedulerIE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related:How to get reference to PDF object embeded in IE * IE on Windows 11

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

I also encourage you to check awesome @trancexx code:  * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuffOnHungApp handlerAvoid "AutoIt Error" message box in unknown errors  * HTML editor

winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2022-03-07

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.

×
×
  • Create New...