David Orme
07-13-2007, 07:43 AM
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
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
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
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