Macro help! Running a module

[BLUDEV]

I need help, :banghead: my brain has melted and I hope someone can assist.

I am trying to run the following code as a function in Access 2010:

Public Function OpenAMZOrder()
Dim xl As Object

'Step 1: Start Excel, then open the target workbook.
Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open ("\\MACSERV8 \Company \Customer Service\ Issues FE File\Data\AMZOrder.xlsm")

'Step 2: Make Excel visible
xl.Visible = False

'Step 3: Run the target macro
xl.Run "Data_Refresh"
xl.Run "Clear_Fields"
xl.Run "Insert_Formula"

'Step 4: Close and save the workbook, then close Excel
xl.ActiveWorkbook.Close (True)
xl.Quit

'Step 5: Memory Clean up.
Set xl = Nothing
End Function

​

When I try to call it out, it gives me the following error:
"Run-time error '1004': The macro may not be available in this workbook or all macros may be disabled"

Yet, when I run the code in Excel, it works perfectly. Please help!

 

[BLUDEV]

Update: I tried to go another way and entered a "GoHyperlink" module to open Excel from Access (running 2010) and it opens Excel but now I have hit another wall. :banghead::banghead::banghead:..the "DataLink Properties" window appears and stops my Excel from opening and halts at the first macro in Excel (ThisWorkbook.RefreshAll). It appears it is because Access is open. I can't figure out how to get this to work with Access still open as the macro in access still has a few more steps to run. Can anyone help?

 

[BLUDEV]

I want to thank June7 for all the help and putting up with my questions