Jump to content
Sign in to follow this  
Chromwell

Excel automation - New sheet with ODBC (MySQL) connection

Recommended Posts

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

Share this post


Link to post
Share on other sites
water

At which statement does your script crash?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

#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

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

Share this post


Link to post
Share on other sites
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 by water
  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Chromwell

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!

Share this post


Link to post
Share on other sites
water
:D

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Chromwell

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

Share this post


Link to post
Share on other sites
water

MSDN is always your friend ;)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

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
Sign in to follow this  

×