Jump to content

Recommended Posts

Posted

Anyone can explain/help me why this code fails?

I get an "==> The requested action with this object has failed.:" Error and the COM Error states that only object type variables can be used in with statement.

How can i convert the $DBString so that this connection/update works?

The same code in Excel VB does work, and there the connection "String" is working.

#include <Excel.au3>
$oExcel = _ExcelBookNew()
Dim $DBString = "ODBC;DRIVER={MySQL ODBC 5.2w Driver};UID=USER;Pwd=PASS;SERVER=SERVER.IP;DATABASE=DB;PORT=3306;DefaultTable=TABLE"
Dim $Query = "SELECT * FROM `TABLE`"
Dim $TableName = "TABLE"
ConsoleWrite($oExcel.ActiveSheet.Name & @CRLF)
With $oExcel.ActiveSheet.ListObjects.Add(0, $DBString, $oExcel.Range("$A$1") ).QueryTable
.CommandText = $Query
.PreserveFormatting = True
.ListObject.DisplayName = $TableName
.SavePassword = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = 1
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.RowNumbers = False
.FillAdjacentFormulas = False
EndWith
Posted

At which statement does your script crash?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted

With $oExcel.ActiveSheet.ListObjects.Add(0, $DBString, $oExcel.Range("$A$1") ).QueryTable

I think it´s the $DBString that causes the error, i tried it directly as "String" and as an variable declared above like in this example code.

Some Microsoft help states that it needs an array type var for the connection string, but Excel VBA does it fine with it declared as a simple connection string.

Chromwell

Posted

Can you please add a COM error handler to your script so we get more information?

How to do is described in the help file for ObjEvent.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted

The Error is:

: ==> Only Object-type variables allowed in a "With" statement.:

No further information from Error handler... or did i do anything wrong in implementing it?

#include<IE.au3>
#include<Excel.au3>
_IEErrorHandlerRegister ("MyErrFunc")
$oExcel = _ExcelBookNew()
Dim $DBString = "ODBC;DRIVER={MySQL ODBC 5.2w Driver};UID=USER;Pwd=PASS;SERVER=SERVER.IP;DATABASE=DB;PORT=3306;DefaultTable=TABLE"
Dim $Query = "SELECT * FROM `TABLE`"
Dim $TableName = "TABLE"
ConsoleWrite($oExcel.ActiveSheet.Name & @CRLF)
With $oExcel.ActiveSheet.ListObjects.Add(0, $DBString, $oExcel.Range("$A$1") ).QueryTable
.CommandText = $Query
.PreserveFormatting = True
.ListObject.DisplayName = $TableName
.SavePassword = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = 1
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.RowNumbers = False
.FillAdjacentFormulas = False
EndWith
Func MyErrFunc()
    ; Important: the error object variable MUST be named $oIEErrorHandler
    $ErrorScriptline = $oIEErrorHandler.scriptline
    $ErrorNumber = $oIEErrorHandler.number
    $ErrorNumberHex = Hex($oIEErrorHandler.number, 8)
    $ErrorDescription = StringStripWS($oIEErrorHandler.description, 2)
    $ErrorWinDescription = StringStripWS($oIEErrorHandler.WinDescription, 2)
    $ErrorSource = $oIEErrorHandler.Source
    $ErrorHelpFile = $oIEErrorHandler.HelpFile
    $ErrorHelpContext = $oIEErrorHandler.HelpContext
    $ErrorLastDllError = $oIEErrorHandler.LastDllError
    $ErrorOutput = ""
    $ErrorOutput &= "--> COM Error Encountered in " & @ScriptName & @CR
    $ErrorOutput &= "----> $ErrorScriptline = " & $ErrorScriptline & @CR
    $ErrorOutput &= "----> $ErrorNumberHex = " & $ErrorNumberHex & @CR
    $ErrorOutput &= "----> $ErrorNumber = " & $ErrorNumber & @CR
    $ErrorOutput &= "----> $ErrorWinDescription = " & $ErrorWinDescription & @CR
    $ErrorOutput &= "----> $ErrorDescription = " & $ErrorDescription & @CR
    $ErrorOutput &= "----> $ErrorSource = " & $ErrorSource & @CR
    $ErrorOutput &= "----> $ErrorHelpFile = " & $ErrorHelpFile & @CR
    $ErrorOutput &= "----> $ErrorHelpContext = " & $ErrorHelpContext & @CR
    $ErrorOutput &= "----> $ErrorLastDllError = " & $ErrorLastDllError
    MsgBox(0,"COM Error", $ErrorOutput)
    SetError(1)
    Return
EndFunc  ;==>MyErrFunc
Posted

Can you post the Visual Basic script you translated to AutoIt?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted

Executing this inserts the table and updates the contents immediately.

Sub TEST()
Dim DBString As String
DBString = "ODBC;DRIVER={MySQL ODBC 5.2w Driver};UID=USER;Pwd=PASS;SERVER=SERVER.IP;DATABASE=DB;PORT=3306;DefaultTable=TABLE"
With ActiveSheet.ListObjects.Add(0, DBString, Destination:=Range("$A$1")).QueryTable
     .CommandText = "SELECT * FROM `TABLE`"
     .PreserveFormatting = True
     .ListObject.DisplayName = "TABLE"
     .SavePassword = True
     .RefreshOnFileOpen = False
     .BackgroundQuery = True
     .RefreshStyle = xlInsertDeleteCells
     .SaveData = True
     .AdjustColumnWidth = True
     .RefreshPeriod = 0
     .PreserveColumnInfo = True
     .RowNumbers = False
     .FillAdjacentFormulas = False
     .Refresh BackgroundQuery:=False
End With
End Sub
Posted (edited)

The problem is that VB passes parameters by name. But AutoIt passes them in sequence. Means you need to pass parameters 1, 2 and 3 when you want to change parameter 4.This modified line works for me:

With $oExcel.ActiveSheet.ListObjects.Add(0, $DBString, True, 1, $oExcel.Range("$A$1")).QueryTable
Link to MSDN.

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted

The problem is that VB passes parameters by name. But AutoIt passes them in sequence. Means you need to pass parameters 1, 2 and 3 when you want to change parameter 4.This modified line works for me:

With $oExcel.ActiveSheet.ListObjects.Add(0, $DBString, True, 1, $oExcel.Range("$A$1")).QueryTable
Link to MSDN.

THANK YOU!
Posted
:D

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted (edited)

When i get the param ".Refresh BackgroundQuery:=False" also to work over AutoIt then i´m completly happy... :P

But i think i´ll find a solution to this with a bit more research.

Edit:

Ok, a simple "." did the trick... ;-)

.Refresh.BackgroundQuery = False

for Autoit

Edited by Chromwell
Posted

MSDN is always your friend ;)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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