SQL on Excel Table

[pulsar777]

Hi, I'd like to run SQL command on table, like this:



Sub Query()

Dim connString As String
Dim SQL As String

connString = "ODBC;DBQ=C:\My Folder\Query Macro.xlsm;DefaultDir=C:\My Folder;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=;FIL=excel 12.0;MaxBufferSize=2048;MaxScanRows=1;PageTimeout=5;ReadOnly=1;SafeTransact ions=0;Threads=3;UID=admin;UserCommitSync=Yes;"
SQL = "SELECT Current Month, New Price FROM [latest data$]"

With Worksheets("output")
.QueryTables.Add connString, .Range("A1"), SQL
.Range("A1").QueryTable.Refresh
End With


End Sub


I'm not sure about connection string - I have Office 365. The Column Names contain spaces and so does the worksheet ("latest data")
Can you help me solve it ?

[snb]

Why don't you use the macrorecorder ?

[pulsar777]

I wonder how .. Everything I tried ends with Power Query which doesn't offer static data list to import. I'd need to create a querytable property or ADODB recordset. I'm also not sure what is SQL syntax rule for columns that contain spaces in name.

[snb]

See

https://www.w3schools.com/sql/

[SamT]

The Column Names contain spaces and so does the worksheet ("latest data")
Can you help me solve it ?
My stupid workaround

Const MyMacro As String = "C:\My Folder\Query Macro.xlsm"

Dim Column1 As String
Column1Name = Sheet1.Range("A1").Value

connString = "ODBC;DBQ=" & MyMacro & "Etc"
.
.
.
With Worksheets("output")
.QueryTables.Add connString, Column1Name, SQL

[pulsar777]

Hi, the ODBC one doesn't work anymore. I go now with ADO recordset and use another driver:


"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & wb.fullName & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

I solved the referencing columns with space in SQL for Excel, but didn't find how to reference them when they contain dot. (This.Column)