Vba upload excel to access table

[crossy5575]

Hi there,
I run a vba excel macro every morning for about 4 files which produce cash reports from different banks back to a similar format.

I then want to automate appending this information to a table.
the code i have is as follows, however it has an error which is user defined on the line

Dim objAccess As Access.Application

the code is:

Sub Example3()
'the path to create the new access database
Dim strPath As String
'an Access object
Dim objAccess As Access.Application
Dim strExcelPath As String

lr3 = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

strPath = "C:\Users\Simon\Documents\5. Intranet\recs1.accdb"
dbWb = Application.ActiveWorkbook.FullName
dbWs = Application.ActiveSheet.Name

strExcelPath = dbWb


Set objAccess = New Access.Application
Call objAccess.OpenCurrentDatabase(strPath)
objAccess.Visible = True
Call objAccess.DoCmd.TransferSpreadsheet(acImport, _
acSpreadsheetTypeExcel8, "bankcash", strExcelPath, _
True, "A1:m" & lr3)
End Sub


does anyone have any simple VBA code to run a macro from excel to upload to access?

Thanks

 

[crossy5575]

I have just realized i didn't have the correct addin on excel! i am a fool!!!!