Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Query
I have a Research.xls file which has a list of discovered Tech Items in the
Discovery sheet. This information is queried into a file called PopGrp.xls which list the available tech and allows production of those items. I have set up the query and it works the way it's supposed to...that is as long as the file is never moved or renamed. The problem arises when the player has more than 1 empire and/or if they want to save their files into a different directory. That empire needs to have a separate Research.xls file and PopGrp.xls file and the path to that file needs to be adjusted in the query. How can I create a query with programming code in PopGrp.xls with the following: PathName = Worksheets("Start").Cells(1,1) FileName = "Research " & EmpID & ".xls" The Query would grab info from: PathName & FileName Worksheets("Discovery") Fields: T#, TechName The information would be placed in Worksheets("Flow").Range("F2") Overwriting Cells and copying down adjacent formulas |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Query
This is what Excel records when I record the Query as a macro using the Query
Wizard of Excel 2003: With ActiveSheet.QueryTables.Add(Connection:= _ "ODBC;DSN=Excel Files;DBQ=D:\Research 4851.xls;DefaultDir=D:;DriverId=790;MaxBufferSize= 2048;PageTimeout=5;" _ , Destination:=Range("N2")) .CommandText = Array( _ "SELECT `Discover$`.`#`, `Discover$`.`Tech Name`" & Chr(13) & "" & Chr(10) & "FROM `D:\Research 4851`.`Discover$` `Discover$`" & Chr(13) & "" & Chr(10) & "ORDER BY `Discover$`.`#`, `Discover$`.`Tech Name`" _ ) .Name = "Query from Excel Files_7" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = True .PreserveFormatting = True .RefreshOnFileOpen = True .BackgroundQuery = False .RefreshStyle = xlOverwriteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
Query from microsoft query- Excel 2007 | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |