set up data source of excel spreadsheet

[philcud]

hi all
i have a macrpothat queries a SQL database and this works fine.
i would like to use a similar macro, but to query a table within the
spreadsheet. i do not want to use ms query.

the relevant bits of code from the code i have
:
Public Const ConnectionString = "ODBC;DRIVER=SQL
Server;SERVER=CW0SQL01;DATABASE=MattTest;Trusted_Connection=Yes;SQL_TXN_ISOLATION=SQL_TXN_READ_UNCOMMITED;SQL_ASYNC_ENABLE=SQL_ASYNC_ENABLE_ON"

--snip--
gsSQLQuery = "This is where query text goes"

With .QueryTables.Add(Connection:=ConnectionString, _
Sql:=gsSQLQuery, Destination:=lWorksheet.Range("A2"))

.Name = "queryoutput1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

---snip-----

i presume i need to set up a connection string within the vb code to
connect to the excel spreadsheet rather than the SQL database - could
someone please give me a clue?
Thanks

 

[Guest]

Why not try a pivot table?
HTH,

 

[philcud]

i believe there will be too many variables (5 fields, each of whiuch
has approx 5 potential values, so this gives 5 to the power 5
combinations (3125 columns).
i would like to be able to build an sql query within a vba code as this
will be very fast and keep workbook size very small.