Run Excel Macro from Access Runtime Error

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

Hi,

I am tryng to run a macro in excel from within access using the following
code

Public Sub Update_SHDS_KCI_XL()

Dim xlx As Object, xlw As Object

Set xlx = CreateObject("excel.application")

Set xlw = xlx.workbooks.Open("C:\SHDS KCI.xls")

xlx.Run "Update_Report"

xlw.Close False

xlx.Quit

Set xlw = Nothing

Set xlx = Nothing

MsgBox "completed"

End Sub



However the debug message "Runtime error 440 Automation Error" appears at
the "xlx.Run "Update_Report"" line. The macro does actually run ok in XL
but the access module fails to progress to the next line.



here is the XL code



Sub Update_Report()



Range("c2").Select

ActiveCell.FormulaR1C1 = Date - 1

Range("B9").Select

Selection.QueryTable.Refresh BackgroundQuery:=False

ActiveWorkbook.Save

ActiveWorkbook.Close



End Sub



I have removed all code in the Xl macro in case there was a line in there
that was causing the problem, but that made no difference.



Any one got any ideas



Oh and I'm using access 2003 with a 2000 database and excel 2002



Cheers

Pete
 
Pete,
You can program the function code into the Access code.

This will put the formula into a cell:
xlw.Worksheets.Range("B9").Formula = "your formula"

This will put the result into a cell
xlw.Worksheets.Range("B9").value = xlw.Worksheets.Range
("C2").value - 1.

See my sample codes I posted yesterday for Ross under
subject: Copy Excel Sheet From Access
 
Back
Top