Hurrmark
09-12-2008, 07:46 AM
Hi,
I am creating an application which extracts data from an Access database and imports it into an Excel spreadsheet. The location of the database is to be inputed into a cell on a spreadsheet, the macro would read the file path of the database, and then open this file. I have it looking like this so far:
Dim FileCell As Range
Workbooks("Market share report_V2.xls").Sheets("template").Activate
Set FileCell = Sheets("template").Range("$D$6")
With Selection.QueryTable
.Connection = Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=FileCell" _
, _
";Mode=ReadWrite;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";" _
, _
"Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions" _
, _
"=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Co" _
, _
"py Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
)
.CommandType = xlCmdTable
.CommandText = Array("Results_alllines")
.Refresh BackgroundQuery:=False
End With
The problem is that the macro doesn't recognize FileCell and therefore, the program doesn't work properly. Any help would be great. Thanks.
I am creating an application which extracts data from an Access database and imports it into an Excel spreadsheet. The location of the database is to be inputed into a cell on a spreadsheet, the macro would read the file path of the database, and then open this file. I have it looking like this so far:
Dim FileCell As Range
Workbooks("Market share report_V2.xls").Sheets("template").Activate
Set FileCell = Sheets("template").Range("$D$6")
With Selection.QueryTable
.Connection = Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=FileCell" _
, _
";Mode=ReadWrite;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";" _
, _
"Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions" _
, _
"=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Co" _
, _
"py Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
)
.CommandType = xlCmdTable
.CommandText = Array("Results_alllines")
.Refresh BackgroundQuery:=False
End With
The problem is that the macro doesn't recognize FileCell and therefore, the program doesn't work properly. Any help would be great. Thanks.