How do I set a MySQL/ODBC connection in Excel from Delphi?

Question

I want to create a new Excel workbook from Delphi, but before I open it from Delphi, I first want to set up an ODBC connection to an existing MySQL database, and set the query. Then as it opens, it will extract the data straight away.

What I tried is first recording the macro in Excel, and seeing how it set up the connection in VBA. I then attempted to replicate the VBA in Delphi.

This is what I have tried:

var
xls, wb : OLEVariant;
begin
  xls := CreateOLEObject('Excel.Application'); {initialize an instance of Excel}
  wb := xls.Workbooks.Add; {create workbook}

  //xls.ActiveSheet.ListObjects.Add(SourceType:=0, Source:='ODBC;DSN=MySQL;', Destination:=Range('$A$1')).QueryTable; //this throws a missing operator or semicolon error
  xls.Worksheets[1].ListObjects.Add(); //add a listobject
  xls.WorkBooks[1].Worksheets[1].ListObjects.Item[1].QueryTable[1].CommandText := 'SELECT 1';
...
end;

On trying to set the CommandText though, I get an OLE error 800A03EC

This below is the VBA code. I get it by recording the Macro from a new workbook. On the very first line I have tried so many combinations on how to enter the connection string, eventually I figured out that I could add a ListObject (as in my Delphi code) without any errors, after which I then tried to set each parametr separately, but without success.

Sub Macro1()
'
' Macro1 Macro
'

'
    Application.CutCopyMode = False
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=MySQL;", _
        Destination:=Range("$A$1")).QueryTable
        .CommandType = 0
        .CommandText = Array("SELECT 1")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_MySQL"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Answer 1

Here is a solution which is almost a direct copy of VBA code:

procedure TForm1.Button1Click(Sender: TObject);
var
    ExcelApplication       : Variant;
    WorkBook               : Variant;
    ActiveSheet            : Variant;
    ListObject             : Variant;
    Range                  : Variant;
    QueryTable             : Variant;
begin
    ExcelApplication                    := CreateOLEObject('Excel.Application');
    WorkBook                            := ExcelApplication.Workbooks.Add;
    ActiveSheet                         := ExcelApplication.ActiveSheet;
    Range                               := ActiveSheet.Range['A1', 'A1'];
    ListObject                          := ActiveSheet.ListObjects.Add(
                                  0,                 // SourceType,
                                  'ODBC;DSN=MySQL;', // Source,
                                  TRUE,              // LinkSource,
                                  xlGuess,           //XlListObjectHasHeaders,
                                  Range);            // Destination
    QueryTable                          := ListObject.QueryTable;
    QueryTable.CommandType              := xlCmdSql;
    QueryTable.CommandText              := 'SELECT 1';
    QueryTable.RowNumbers               := FALSE;
    QueryTable.FillAdjacentFormulas     := FALSE;
    QueryTable.PreserveFormatting       := TRUE;
    QueryTable.RefreshOnFileOpen        := FALSE;
    QueryTable.BackgroundQuery          := TRUE;
    QueryTable.RefreshStyle             := xlInsertDeleteCells;
    QueryTable.SavePassword             := FALSE;
    QueryTable.SaveData                 := TRUE;
    QueryTable.AdjustColumnWidth        := TRUE;
    QueryTable.RefreshPeriod            := 0;
    QueryTable.PreserveColumnInfo       := TRUE;
    QueryTable.ListObject.DisplayName   := 'Table_Query_from_MySQL';
    QueryTable.Refresh(False);  // BackgroundQuery
end;

As you see, I have used intermediate variables for code readability. You could omit some of them.