Cannot run the Macro "Delete_Sheet". Why am I getting this ?

[ToyFox]

I have an Excel sheet (essentially to be used as a template), I have a macro that deletes the values in the template.

Now, in Access, I have code that fires the Excel from Access, I have a function that calls the macro Delete_Sheet. But I am getting runtime error 1004 cannot run the macro "Delete_Sheet". The macro may not be available in this workbook or all macros may be disabled.

How can I solve this? Thanks

 

[AnotherHiggins]

For future reference, macro/VBA questions best fit into forum707.

Please post the relevant code you're using in Access to try to call the macro in Excel.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ToyFox]

Here it is...

Function Report_With_Requirements()

' step 1 declare the objects
Dim db As Database
Dim qdf As QueryDef
Dim rs As Recordset
Dim i As Integer

Set db = CurrentDb()
Set qdf = db.QueryDefs("Remediation Status Report (Weekly)")

Set rs = qdf.OpenRecordset

' This code fires the Excel from Access.

Dim xl As Excel.Application
Dim xlwkbk As Excel.Workbook

Set xl = New Excel.Application
Set xlwkbk = xl.Workbooks.Open("G:\Ed Rodman\AML Remediation With Gradings Template.xlsx")

xl.Visible = True
xl.Run "Delete_Sheet"

' paste active sheet
xl.Range("A2").CopyFromRecordset rs
'ActiveSheet.Range("A2").CopyFromRecordset rs
xlwkbk.Save
xlwkbk.Close (True)

xl.Quit

Set xl = Nothing
Set xlwkbk = Nothing


End Function

 

[ToyFox]

Looks like I got it. Seems I had to save the sheet as a macro type spreadsheet.