Excel 2007 automatically makes my query into a table, and 2003 it is just a data set. My code is sorting the data, and sorting a table and data not in a table use two different methods and are not interchangeable. I am trying to find something that is interchangeable or how to force later versions of excel to not convert my data query into a table.
I am thinking about doing a copy paste special values only into another sheet to make it work from there. I am wondering if that is not efficient and if there is a better way.
Once you have pulled the data into the table a command such as
ActiveSheet.ListObjects("Table_DatabaseName").Unlist
Should convert it into a range that you can manipulate, where Table_DatabaseName is the .DisplayName for the object (assuming you are pulling the data through VBA. Alternatively in the Design tab in the ribbon you can click Convert To Range.
Your alternative would be to pull the data into a recordset and use .CopyFromRecordset to put the data in.
Edited To Add Recordset Example
Sub PullDataTest()
Dim cnn As Object
Dim cmd As Object
Dim rs As Object
Set cnn = CreateObject("ADODB.Connection")
cnn.connectiontimeout = 0
strConn = ' You can just copy the connection string from your Connections.
' Don't forget to escape the quotes:
'eg [Property]="" becomes [Property]=""""
cnn.Open strConn
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnn
cmd.CommandType = 1
cmd.CommandText = "SELECT * FROM tbl_Db_Main"
Set rs = CreateObject("ADODB.Recordset")
With rs
.Open Source:=cmd.Execute
ActiveSheet.Range("A1").CopyFromRecordset rs
.Close
End With
Set cnn = Nothing
Set cmd = Nothing
Set rs = Nothing
End Sub