How to access ACCESS from Excel!

[Gordon Cartwright]

Hi

I'm trying to run a macro from Excel that must open
Access, open a table within access, copy the contents of
said table, close Access and paste the information back
into Excel in exactly the same numerical and text format.
But its not recording the bit before the copy command...

A cool beer to the guys who helps me...

Gordon.

 

[merjet]

There are examples for importing from Access at:
http://www.erlandsendata.no/english/vba/adodao/index.htm

HTH,
Merjet

 

[Gordon Cartwright]

-----Original Message-----
There are examples for importing from Access at:
http://www.erlandsendata.no/english/vba/adodao/index.htm

HTH,
Merjet

Hi...

The examples on that site are too specialist and look at
importing just 1 field from an access database into 1
column into Excel, when I have 20 to do. I'm also not
skilled enough to adapt that code. Any further help would
be grateful...

GC

 

[Chip Pearson]

Gordon,

Set a reference to the Microsoft ActiveX Data Objects library and use code
like the following:


Dim CN As ADODB.Connection
Dim RecSet As ADODB.Recordset
Dim DBName As String
Dim TableName As String
Dim StartCell As Range
'
' Change these there lines to the appropriate values.
'
DBName = "C:\Path\Database.mdb"
TableName = "TableName"
Set StartCell = Range("A1")

Set CN = New ADODB.Connection
CN.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBName & ";"
Set RecSet = New ADODB.Recordset
RecSet.Open "SELECT * FROM " & TableName, CN
StartCell.CopyFromRecordset RecSet
RecSet.Close
CN.Close



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)