Open Excel Macro from Access

[erodjob]

If possible can someone please share code that will open a macro in Excel 97, from within a command button in Access 97?

Thanks

 

[mdmilner]

Peek at this two threads below yours.

http://www.mrexcel.com/board2/viewtopic.php?t=70692

 

[Mudface]

This seems to work OK. It will run a procedure called 'TestProc' found in the file referred to below. You will need to set a reference to the Excel object library from within Access (click on Tools-References and find the Microsoft Excel x.0 Object Library).

Public Function OpenMacroFromExcel()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook

Set xlApp = New Excel.Application

With xlApp
    .Visible = True
    Set xlWB = .Workbooks.Open("C:\My Documents\Test.xls")
End With
 
xlWB.Application.Run "TestProc"

Set xlWB = Nothing
Set xlApp = Nothing

End Function

 

[erodjob]

Is "TestProc" the name of the excel macro?

 

[Mudface]

Is "TestProc" the name of the excel macro?

Yes, just change it to the name of the macro you want to run yourself.

 

[erodjob]

Does it make a difference that I am using my personal macro workgroup?

I made a command button in access and placed the code there. I renamed the macro to FedCalc2.

However, I don't have a text.xls in My Documents, should I make one?

Public Function Command7_Click()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook

Set xlApp = New Excel.Application

With xlApp
.Visible = True
Set xlWB = .Workbooks.Open("C:\My Documents\Test.xls")
End With

xlWB.Application.Run "FedCalc2"

Set xlWB = Nothing
Set xlApp = Nothing

End Function

 

[Mudface]

Replace the C:\My Documents\Test.xls bit with the path and name of your orkbook, I just created a workbook called Test in that folder to make sure the code worked. Also, why is your command button's click event a Function? Surely it should be a Sub?

 

[erodjob]

I changed it to:

Public Sub Command7_Click()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook

Set xlApp = New Excel.Application

With xlApp
.Visible = True
Set xlWB = .Workbooks.Open("C:\My Documents\Test.xls")
End With

xlWB.Application.Run "FedCalc2"

Set xlWB = Nothing
Set xlApp = Nothing

End Sub

But i get Complile error
User-defined type not defined.

Why do i need to reference the workbook? The macro is in all workbooks not just one.

 

[Mudface]

Access doesn't know anything about Excel objects unless you tell it where to find the info, so you need to reference the Excel object model. See the second sentence in the first post.