Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
set up data source of excel spreadsheet
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_C onnection=Yes;SQL_TXN_ISOLATION=SQL_TXN_READ_UNCOM MITED;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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
set up data source of excel spreadsheet
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
set up data source of excel spreadsheet
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locating Source Data in Master Spreadsheet | Excel Discussion (Misc queries) | |||
Use a different spreadsheet as source data | Charts and Charting in Excel | |||
.xlt does not contain new data from source spreadsheet. | Excel Discussion (Misc queries) | |||
Use spreadsheet as data source | Excel Discussion (Misc queries) | |||
Setting Spreadsheet control data source to Excel file | Excel Programming |