L
Lazzaroni
I have a small app that allows me to run SQL queries to grab data through a
DSN (MySQL ODBC Connector 3.51) and dump it into a spreadsheet in Excel.
The app works great in Excel 2003. In Excel 2007, however, using “SHOW
COLUMNS FROM `table_name` “ or “DESCRIBE `table_name`†results in run-time
error 1004:
“No columns that Microsoft Office Excel can use were returned from this
query.â€
This is the code. It runs through a user form. I type the SQL into TextBox1
and click CommandButton1.
Private Sub CommandButton1_Click()
Dim oQueryName As String
oQueryName = Format(Date, "yyyy-mm-dd") & Chr(95) & Format(Time, "hh-mm-ss")
With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=MyODBC",
Destination:=Selection)
.CommandText = RunQueryForm.TextBox1.Value
.Name = oQueryName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
.EnableRefresh = True
End With
If Not CheckBox1.Value Then
'delete the query table. this is equivalent to unchecking "Save query
definition."
ActiveSheet.QueryTables.Item(oQueryName).Delete
End If
Unload Me
End Sub
So far there hasn’t been a lot of information on Excel 2007, so any help you
can offer is greatly appreciated.
Thanks.
DSN (MySQL ODBC Connector 3.51) and dump it into a spreadsheet in Excel.
The app works great in Excel 2003. In Excel 2007, however, using “SHOW
COLUMNS FROM `table_name` “ or “DESCRIBE `table_name`†results in run-time
error 1004:
“No columns that Microsoft Office Excel can use were returned from this
query.â€
This is the code. It runs through a user form. I type the SQL into TextBox1
and click CommandButton1.
Private Sub CommandButton1_Click()
Dim oQueryName As String
oQueryName = Format(Date, "yyyy-mm-dd") & Chr(95) & Format(Time, "hh-mm-ss")
With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=MyODBC",
Destination:=Selection)
.CommandText = RunQueryForm.TextBox1.Value
.Name = oQueryName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
.EnableRefresh = True
End With
If Not CheckBox1.Value Then
'delete the query table. this is equivalent to unchecking "Save query
definition."
ActiveSheet.QueryTables.Item(oQueryName).Delete
End If
Unload Me
End Sub
So far there hasn’t been a lot of information on Excel 2007, so any help you
can offer is greatly appreciated.
Thanks.