How to CREATE TABLE using sql/VBA in excel with ADO

Question

I'm trying to erase table content and replace it with new content in an excel sheet.

The code doesn't raise any errors however everytime I run it, it inserts some blanck cells before the source table.

The 'base.xlsx' table sheet looks like this :

If target.xlsx is void before running the code I get what I want however if I run it again I get this :

Here is the code :

Sub SQLQUERY()

    Dim Cn As ADODB.Connection
    Dim QUERY_SQL As String
    Dim ExcelCn As ADODB.Connection

    SourcePath = "C:\Path\to\base.xlsx"
    TargetPath = "C:\Path\to\target.xlsx"

    CHAINE_HDR = "[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] "

    Set Cn = New ADODB.Connection

    STRCONNECTION = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source='" & SourcePath & "';" & _
    "Mode=Read;" & _
    "Extended Properties=""Excel 12.0;"";"

    Colonnes = "[Col#1], Col2"

    QUERY_SQL = _
    "SELECT " & Colonnes & " FROM [base$] " & _
    "IN '" & SourcePath & "' " & CHAINE_HDR

    MsgBox (QUERY_SQL)

    Cn.Open STRCONNECTION

    Set ExcelCn = New ADODB.Connection
    ExcelCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                   "Data Source=" & TargetPath & ";" & _
                   "Extended Properties=""Excel 12.0;HDR=YES;"""

    ExcelCn.Execute "DROP TABLE [sheet$]"

    ExcelCn.Execute "CREATE TABLE [sheet$] ([C1] Float, [C2] Float, [C3] Float)"

    Cn.Execute "INSERT INTO [sheet$] (C1, C3) IN '" & TargetPath & "' 'Excel 12.0;' " & QUERY_SQL

    '--- Fermeture connexion ---

    Cn.Close
    ExcelCn.Close


End Sub

Lich4r · Accepted Answer · 2017-05-23 09:39:55Z

1

After some research I discovered what's going on with the code. When Drop Table is executed Excel keep the "active range" and thus INSERT INTO will insert data after the active range.

To counter this we need to specify a range with the table name :

ExcelCn.Execute "DROP TABLE [sheet$]"

ExcelCn.Execute "CREATE TABLE [sheet$] ([C1] Float, [C2] Float, [C3] Float)"

Cn.Execute "INSERT INTO [sheet$A1:ZZ1] (C1, C3) IN '" & TargetPath & "' 'Excel 12.0;' " & QUERY_SQL

'--- Fermeture connexion ---

Note the [sheet$A1:ZZ1] in the sql statement.

answered May 23, 2017 at 9:39

Lich4r

1,4333 gold badges15 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to CREATE TABLE using sql/VBA in excel with ADO

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related