ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Query to look in default directory (https://www.excelbanter.com/excel-programming/411091-query-look-default-directory.html)

CLR
Query to look in default directory
 
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

Norman Jones[_2_]
Query to look in default directory
 
Hi Chuck,

Perhaps, try:

Application.DefaultFilePath



---
Regards.
Norman


"CLR" wrote in message
...
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

CLR
Query to look in default directory
 
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" wrote in message
...
Hi Chuck,

Perhaps, try:

Application.DefaultFilePath



---
Regards.
Norman


"CLR" wrote in message
...
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

Norman Jones[_2_]
Query to look in default directory
 
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.




---
Regards.
Norman

"CLR" wrote in message
...
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" wrote in message
...
Hi Chuck,

Perhaps, try:

Application.DefaultFilePath



---
Regards.
Norman


"CLR" wrote in message
...
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

CLR
Query to look in default directory
 
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(Arra y( _
"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




"Norman Jones" wrote in message
...
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.




---
Regards.
Norman

"CLR" wrote in message
...
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" wrote in message
...
Hi Chuck,

Perhaps, try:

Application.DefaultFilePath



---
Regards.
Norman


"CLR" wrote in message
...
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


All times are GMT +1. The time now is 01:36 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com