Automation Error while Running Excel Macro from Access

[JLeo]

Hi,

I am trying to run an Excel Macro "Plot_OnAirDate". However i keep getting the error message of:

Error 440
Automation Error

Below is my code, can anyone point out where i've gone wrong? Thanks for any advice rendered.

Dim xlApp1 As Object
Dim xlWrkBook1 As Workbook
Set xlApp1 = CreateObject("Excel.Application&quot
Set xlWrkBook1 = xlApp1.Workbooks.Open("L:\TCH Traffic Chart.xls&quot

xlApp1.Application.Run "Module1.Plot_OnAirDate"

 

[Lightning]

Have you set a reference to the Excel Object library?

Open any code module, and then click TOOLS|REFERENCES on the menu bar.

Set a reference to the Microsoft Excel xx.x Object Library where xx.x is your version of Excel

10.0 = Excel XP
9.0 = Excel 2000
8.0 = Excel 97

HTH
Lightning

 

[JLeo]

Hi,

Thanks 4 the reply. I had that reference enabled prior to posting but it still returns the same error.