setting a dynamic path in an excel query

  • Thread starter Thread starter Glen
  • Start date Start date
G

Glen

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(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"
 
A wild guess, ActiveWorkbook.Path returns an empty string if the workbook has
never been saved.

Regards,
Vic Eldridge
 
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.
 
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.
 
Back
Top