Extracting data from a variable source

[Hurrmark]

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.

[Bob Phillips]

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 Copy 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

[Hurrmark]

Looks like it works great, thanks!

I didn't realize you could use tags to get the text looking like that. It definitely looks better.

[Bob Phillips]

Yeah it does, it is just

[ vba]

[/vba]

with that space (I added to show it),. and it also suppresses the smilies, although you can disable smilies anyway

[mdmackillop]

...or click the green VBA button.

[Bob Phillips]

Shows the difference between a keyboard jock and a mouse jock MD!!
:yes