Run Excel Macro from Access

[adnancanada]

I have created a macro file abc.xlsm and save it on my C drive . I want open access form and click on command button to run excel macro.



What actually I want, I want to open excel file xyz.xlsx and run macro to format my file and close it. End result my xyz.xlsx file should be formatted and close. Below this code just working on abc.xlsm and not on xyz.xlsx.

Private Sub Command0_Click()
Dim xl As Object

Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open ("C:Workabc.xlsm")
xl.Visible = True
xl.Run ("Macro1")
xl.activeworkbook.Close (True)
xl.Quit
Set xl = Nothing
End Sub

Help please.

[June7]

I am surprised it works at all because the path is missing \ character.

xlsx workbook cannot have macros. Only xlsm can have macro code.

[adnancanada]

I am surprised it works at all because the path is missing \ character.

xlsx workbook cannot have macros. Only xlsm can have macro code.

Thank you for your response. Sorry I made a mistake in above.
Here is code

Private Sub Command6_Click()
Dim xl As Object
Dim xp As Object


Set xl = CreateObject("Excel.Application" )
Set xp = CreateObject("Excel.Application" )
xl.Workbooks.Open (" C:\work\abc.xlsm")
xp.Workbooks.Open ("C:\work\xyz.xlsx"}



xl.Visible = True
xp.Visible = True


xp.Application.Run ("PERSONAL.XLSB!Macro1")


xl.ActiveWorkbook.Close (True)
xp.ActiveWorkbook.close (True)
xl.Quit
xp.Quit

Set xl = Nothing
Set xp= Nothing

End Sub

When I run this code it opens both files abc which contain macro and xyz which i have to format but macro does not run on excel file in fact it does not show macro.

[June7]

This is something I've never tried to do. So I am somewhat confused.

If xp is set to xyz.xlsx, why do you reference PERSONAL.XLSB? I have never seen xlsb extension.

Macro in abc.xlsm is supposed to do something with xyz.xlsx?

[adnancanada]

This is something I've never tried to do. So I am somewhat confused.

If xp is set to xyz.xlsx, why do you reference PERSONAL.XLSB? I have never seen xlsb extension.

Macro in abx.xlsm is supposed to do something with xyz.xlsx?

What will be simple code If I run it from microsoft access form.

I have created a personal macro on excel workbook to format my end result according to my company format. I save my end result as excel workbook which I export from Access to Excel.
I want to click on form button to open my excel file which i export every time to be format it as I setup in macro. Simple is that I want to run excel macro from access. Please help me. I hope you understand.

[June7]

Bing: Access VBA format Excel

http://www.mrexcel.com/forum/microso...-workbook.html