lundi 30 mars 2015

Excel VBA - Return SQL Data To Excel Table

I am having a little trouble returning SQL data into an Excel table, instead of dropping the data into the cell I specify (when not using a table) it pushes the whole table to the right.


Does anyone know how I can get it to populate the table as I want to make use of splicers etc. to filter the data. I know I could import the data then turn it into a table but if there's a quicker, simpler way then I'd rather that.


Right now the code I'm using is:



With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=<dbname>", Destination:=ActiveSheet.Range("A13"))
.CommandText = strSQL
.Name = "Query"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

ActiveSheet.QueryTables(1).Delete


Many thanks in advance.


Dan


Aucun commentaire:

Enregistrer un commentaire