setting a dynamic path in an excel query
 

Good morning. I have been at this for a few hours already and I can't
seem to get this to work properly. I continue to get the following
error at this point: "Not a valid file name". Below are two sets of
code, the first set works fine. The second set is the one giving me an
error. I am attempting to include a dynamic path for the query table
and to accomplish this I have tried to replace the hard-coded path with
the dynamic path "pathname" (Ingenious naming convention, I know).
The program works like this, the access DB and an Excel SS (XL1) with
the called macro (get_data) are in the same folder. Access creates a
new SS (XL2) and calls get_data from open SS (XL1). The macro
basically calls for data retrieval from an Access query in the same
folder. XL2 is saved by the user with a drop down box after the
spreadsheet has been formatted and populated. I want to keep
everything in one folder to make it easily portable. I just need to
know how to get the ODBC to work and I will have it. Any help is
greatly appreciated.

CODE1:
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and
Settings\gwelsh\Desktop\Bill of
Material\MatCompile.mdb;DefaultDir=C:\Documents and" _
), Array( _
" Settings\gwelsh\Desktop\Bill of Material;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("A1"))
.CommandText = "SELECT * FROM XL_SS_Query"

CODE 2:
Set wb2 = Workbooks(Fname)
wb2.Activate
pathname = ActiveWorkbook.Path
wb1.Activate
'
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ='" & pathname &
"'\MatCompile.mdb;DefaultDir='" & pathname & "'" _
), Array( _
" ;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
_
)), Destination:=Range("A1"))
.CommandText = "SELECT * FROM XL_SS_Query"

setting a dynamic path in an excel query
 

A wild guess, ActiveWorkbook.Path returns an empty string if the workbook has
never been saved.

Regards,
Vic Eldridge



"Glen" wrote:

Good morning. I have been at this for a few hours already and I can't
seem to get this to work properly. I continue to get the following
error at this point: "Not a valid file name". Below are two sets of
code, the first set works fine. The second set is the one giving me an
error. I am attempting to include a dynamic path for the query table
and to accomplish this I have tried to replace the hard-coded path with
the dynamic path "pathname" (Ingenious naming convention, I know).
The program works like this, the access DB and an Excel SS (XL1) with
the called macro (get_data) are in the same folder. Access creates a
new SS (XL2) and calls get_data from open SS (XL1). The macro
basically calls for data retrieval from an Access query in the same
folder. XL2 is saved by the user with a drop down box after the
spreadsheet has been formatted and populated. I want to keep
everything in one folder to make it easily portable. I just need to
know how to get the ODBC to work and I will have it. Any help is
greatly appreciated.

CODE1:
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and
Settings\gwelsh\Desktop\Bill of
Material\MatCompile.mdb;DefaultDir=C:\Documents and" _
), Array( _
" Settings\gwelsh\Desktop\Bill of Material;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("A1"))
.CommandText = "SELECT * FROM XL_SS_Query"

CODE 2:
Set wb2 = Workbooks(Fname)
wb2.Activate
pathname = ActiveWorkbook.Path
wb1.Activate
'
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ='" & pathname &
"'\MatCompile.mdb;DefaultDir='" & pathname & "'" _
), Array( _
" ;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
_
)), Destination:=Range("A1"))
.CommandText = "SELECT * FROM XL_SS_Query"

setting a dynamic path in an excel query
 

Thanks Vic, and yes you are right. If the workbook hasn't been saved
you get an empty string. I am activating a workbook to get the correct
path before returning the focus to the workbook I have just created
from Access. The string variable 'pathname' holds a valid path - but I
appreciate your suggestion.

setting a dynamic path in an excel query
 

Tom Ogilvy provided me with the solution to his problem.

remove the single qutoes from this section of code to make it work.
DBQ='" & pathname &
"'\MatCompile.mdb;DefaultDir='" & pathname & "'" _

Thanks again Tom and Vic.