Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querytable range name
Hi all,
I have created a query that will gather specific information for a specific account and specific dates. Since the account numbers and date ranges are not sequential I can't just grab a bounded range of data but have to call back to database for each specific piece. The issue comes in that for every callback to the database the query is creating a defined name for the cell. I have tried a number of ways to drop the name but none have worked. What makes it more challenging is that the a number of the cells are named so if I do an explicit name.delete after the query has been populated to that cell naturally it deletes the name I want to keep, not the default name the query gave it. What follows is the code I am using. Do DoEvents: 'setting up and running query. 'designing connection string and table destination With Worksheets("Data Input").QueryTables.Add (Connection:=Array(Array( _ "ODBC;DSN=Visual FoxPro Tables;UID=;PWD=;SourceDB=" & DataBase_Location & ";SourceType=DBF;Exclusive=No;BackgroundFet" _ ), Array ("ch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;" )), Destination:=ActiveCell.Offset(x, 0)) 'SQL string .CommandText = Array( _ "SELECT auttrx.amount" & Chr(13) & "" & Chr(10) & _ "FROM auttrx auttrx" & Chr(13) & "" & Chr(10) & _ "WHERE (auttrx.fund='" & strFundID & "') AND (auttrx.del_time='') AND " & _ "(auttrx.start_date<={d '" & datRptAsOfDate & "'}) AND " & _ "(auttrx.end_date={d '" & datRptAsOfDate & "'}) AND (auttrx.acct_dr='" & _ Account(ActiveCell.Offset(x, -6).Value) & "') AND (auttrx.inactive<'Y')") 'AND (auttrx.del_date Is Not Null) 'setting query properties .FieldNames = False .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = False .RefreshOnFileOpen = False .BackgroundQuery = False .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = False .AdjustColumnWidth = False .RefreshPeriod = 0 .PreserveColumnInfo = False .Refresh BackgroundQuery:=False End With Any ideas? Thanks for the help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use QueryTable with insertion sql | Excel Programming | |||
Bad QueryTable Row Count | Excel Programming | |||
Neet to get range of newly added QueryTable | Excel Programming | |||
Querytable | Excel Programming | |||
MS QUERY w/out querytable | Excel Programming |