MVP help please - dynamic pathname in ODBC arrays
 

Good morning. I have been at this for a couple of days 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"

MVP help please - dynamic pathname in ODBC arrays
 

in this section:

" . . . DBQ='" & pathname &
"'\MatCompile.mdb;DefaultDir='" & pathname & "'"

You have included 4 single quotes. There are no single quotes in the
recorded code, so I would see no need for any here.

--
Regards,
Tom Ogilvy




"Glen" wrote in message
oups.com...
Good morning. I have been at this for a couple of days 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"

MVP help please - dynamic pathname in ODBC arrays
 

THANK YOU! THANK YOU! THANK YOU!
I knew it was something simple that I just wasn't getting. This isn't
the first time you've helped me and I am sure it won't be the last.
You Rock Tom!