Hi All.....

I have a little Query macro that works just fine on my computer, but
unfortunately has the path to the source file hard coded inside, and
therefore won't work on anothr computer. I would like the Query to always
look to the default directory that the Excel
program is in to find the source file to query......no joy in any of
myefforts.......anybody know how?

Heres the code.........
Sub GetAccessFile()
Sheets("MainMenu").Select
Sheets.Add
ActiveSheet.Name = "NPRdatabase"

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access
Database;DBQ=F:\LinvatecPrograms\_MichaelLosey\Wor kingAPRIL08\April
NPRs.mdb;DefaultDir=F:\LinvatecPrograms\_Mich" _
), Array( _
"aelLosey\WorkingAPRIL08;DriverId=281;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT `NPR Database`.`Disposition Date`, `NPR
Database`.`Inspection Date`, `NPR Database`.`NPR Origin`, `NPR
Database`.`NPR Number`, `NPR Database`.`Part Number`, `NPR Database`.`Serial
Number`, `NPR" _
, _
" Database`.`Vendor Code`, `NPR Database`.`Vendor Name`, `NPR
Database`.`No of Defects`, `NPR Database`.`Qty RTV`, `NPR Database`.`Defect
Description`, `NPR Database`.`Corrective Action`" & Chr(13) & "" & Chr(10) &
"FROM `F:\Linv" _
, _
"atecPrograms\_MichaelLosey\WorkingAPRIL08\Apr il NPRs`.`NPR
Database` `NPR Database`" & Chr(13) & "" & Chr(10) & "ORDER BY `NPR
Database`.`Vendor Code`" _
)
.Name = "Query from MS Access Database"
.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
Range("a1").Select
End Sub

I could even use a Path/filename from a cell, if necessary..............

replacing the path with "thisworkbook.path" doesn't work, nor does replacing
it with "Range("Sheet1!$A$4").value".......any other ideas?


TIA
Vaya con Dios,
Chuck, CABGx3