defining a data source as a variable

Please help me as I am new to this and am completely stuck;

This is an extract form an import macro whereby I am trying to import data from a variable source i.e. different directories depending on month.
The Data Source is written in full below as G:\Roster .....etc but needs to be a variable ( I used DataPath = ActiveWorkbook.Path ) as the variable to address the folder from where this sheet comes however in the import macro it does not recognise the variable when written as Data Source=DataPath\X Watch ATCO.XLS is there any way the path (G:\Roster\Import Development) can be replaced with a variable ?

Thank You


[vba] With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=G:\Roster\Import Development\X Watch ATCO.XLS;Mode=Share " _
, _
"Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password=""""" _
, _
";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactio" _
, _
"ns=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't " _
, _
"Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A231"))
.CommandType = xlCmdTable[/vba]

[vba]

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;" & _
"Data Source=" & DataPath & "\X Watch ATCO.XLS" & ";Mode=Share " _
, _
"Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";" & _
"Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password=""""" _
, _
";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;" & _
"Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactio" _
, _
"ns=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;" & _
"Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't " _
, _
"Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;" & _
"Jet OLEDB:SFP=False" _
), Destination:=Range("A231"))
.CommandType = xlCmdTable
[/vba]

See if this works for you:
[VBA]Data Source = DataPath & "\X Watch ATCO.XLS"[/VBA]

Darn....

Thank You very much - tried this :

ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;" & _
"Data Source=" & DataPath & "\X Watch ATCO.XLS" & ";Mode=Share " _
, _
and I got the reply from Excel of ;

Data file: ?G:\Roster\Import Development\X Watch ATCO.xls? not found

Would you like to connect to G:\Roster\Import Development\X Watch ATCO.xls instead?

which I have had before - odd however as it addresses the same file name exactly twice ! any other suggestions ?

Solved

Thanks again for pointing me in the right direction - I found this works wherever I move the file to it searches for the data inside the 'working' directory


[vba]
dim CurDir as String


CurDir = ThisWorkbook.Path & "\X Watch ATCO.xls"
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source =" & CurDir & " ;Mode=Share " _
, _
"Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password=""""" _
, _
";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactio" _
, _
[/vba]