Query to look in default directory

[CLR]

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(Array( _
"ODBC;DSN=MS Access
Database;DBQ=F:\LinvatecPrograms\_MichaelLosey\WorkingAPRIL08\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\April 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

 

[Norman Jones]

Hi Chuck,

Perhaps, try:

Application.DefaultFilePath

 

[CLR]

Thanks Norman..........

But I get the error "Object doesn't support this property or method", and
the code freezes on that section.

Vaya con Dios,
Chuck, CABGx3

 

[Norman Jones]

Hi Chuck,

I was insufficiently explicit!

My suggestion was intended as something like:

Dim sPath As String

sPath = Application.DefaultFilePath

Then, replace your hardcoded path with
the string variable sPath.

 

[CLR]

Hi Norman.........

Thanks for the comeback...........I had tried something similar to that
declared variable sort of thing earlier today, only using a fixed path
instead of your suggested term, and the Query code would not accept
it........even tho I tried to remote the exact same path that works fine
when hardcoded in........

I did get lucky just a minute ago though........I kinda sorta blundered upon
something that appears to work........I just actually REMOVED the path from
the Query code and all seems to be well........only problem is, I must
make sure the Tools > Options > GeneralTab > Default file location: window
is empty........

Here's the same code from my original post, except modified by removing the
path.......
Sub GetAccessFile()
Sheets("MainMenu").Select
Sheets.Add
ActiveSheet.Name = "NPRdatabase"

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=April NPRs.mdb;DefaultDir= " _
), Array( _
";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 `" _
, _
"April 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

This seems to work for me for now...........

Thanks much for your suggestions tho, they got me thinking, and since I've
never seen them before, the technique may be usefull another time.

Vaya con Dios,
Chuck, CABGx3