copy Access 2003 table over to excel 2007 on startup

[grego9]

I am trying to write some code to copy over a Microsoft Access (2003) table to a specific sheet of a Microsoft Excel (2007) workbook

I want the code to reside in the excel workbook and copy the table into excel when the excel file is opened.

I have been struggling at the first hurdle just getting the table copied over. I found the following code but when I copy it into a module I cannot step into the macro to run it - or see the macro in the list of available macros.

I just want the whole table copying over!

Any ideas gratefully received!!! Thanks in advance



Sub ADOImportFromAccessTable(DBFullName As String, _
TableName As String, TargetRange As Range)
ADOImportFromAccessTable "Q:\IT\Database Masters\Master_Data.mdb", _
"FERC Database", Range("A2")
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=" & _
DBFullName & ";"
Set rs = New ADODB.Recordset
With rs
' open the recordset
.Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable
' all records
'.Open "SELECT * FROM " & TableName & _
" WHERE [FieldName] = 'MyCriteria'", 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 = Nothing
End Sub

[MikeTheBike]

Hi

You will not see the macro in the macro window because that sub has arguments, only subs with no argument are listed (because it would not know what to use as an argument!?).

For the code to auto run on opening the workbook you need to run it from the Workbook_Open() event in the ThisWorkbook module.
ie like

Private Sub Workbook_Open()
    ADOImportFromAccessTable "C:\You Folder\YourDB.mdb", "tblName", ActiveSheet.Range("A:1")
End Sub

I am not sure the code will work, obviously, but I think this
RS2WS rs, TargetRange
should changed to
TargetRange.CopyFromRecordset rs

HTH

MTB