Excel automation - New sheet with ODBC (MySQL) connection

[Chromwell]

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

[water]

At which statement does your script crash?

[Chromwell]

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

[water]

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.

[Chromwell]

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?

expandcollapse popup

#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

[water]

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

[Chromwell]

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

[water]

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 April 3, 2013 by water

[Chromwell]

  On 4/3/2013 at 12:41 PM, 'water said:

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!

[water]

[Chromwell]

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

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 April 3, 2013 by Chromwell

[water]

MSDN is always your friend