Hi I am trying to use ADO to import data from Access to Excel (Procedure
below) when I run it the Macro Box is activated but is empty i.e. does not
contain the procedureI managed to do it the other way round using ADO to
export data from Excel to Access, that worked fine. Do I have to use a
different Reference ie ActiveX Data Objects x.x Object Library for the
Access to Excel , or do I put the code in the Access Module. Thanks for any
help.Sub ADOImportFromAccessTable(DBFullName As String, _ TableName As
String, TargetRange As Range)' Example: ADOImportFromAccessTable
"C:\FolderName\DataBaseName.mdb", _ "TableName", Range("C1")Dim cn As
ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer Set
TargetRange = TargetRange.Cells(1, 1) ' open the database Set cn = New
ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source= C:\Documents and Settings\Me\Desktop\db4.mdb" & ";" Set rs = New
ADODB.Recordset With rs ' open the recordset .Open
TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable ' all
records .Open "SELECT * FROM " & TableName & _ " WHERE
[FieldName1] = 'Mailing List ID'", cn, , , adCmdText ' filter records
' RS2WS rs, TargetRange ' write data from the recordset to the worksheet
' optional approach for Excel 2000 or later (RS2WS is not necessary)
For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset
data End With rs.Close Set rs = Nothing cn.Close Set cn =
NothingEnd Sub