Excel Query

P

PhaseDragon

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
 
P

PhaseDragon

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
 
Top