MVP help please - dynamic pathname in ODBC arrays

[Glen]

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(Array( _
"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(Array( _
"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"

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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(Array( _
> "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(Array( _
> "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"
>

[Glen]

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!