How to access ACCESS from Excel!
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. |
How to access ACCESS from Excel!
There are examples for importing from Access at:
http://www.erlandsendata.no/english/...odao/index.htm HTH, Merjet |
How to access ACCESS from Excel!
-----Original Message----- There are examples for importing from Access at: http://www.erlandsendata.no/english/...odao/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 |
How to access ACCESS from Excel!
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 "Gordon Cartwright" wrote in message ... -----Original Message----- There are examples for importing from Access at: http://www.erlandsendata.no/english/...odao/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 |
All times are GMT +1. The time now is 02:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com