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

You are opening the workbook abc.xlsm and not xyz.xlsx in the above code. So macro will run only on that workbook.

You can also get the path of the Excel file from InputBox where you choose the path of the file instead of hard coding it.

Dim Path As String
Path = InputBox( "Enter Path of Excel File" )
xl.Workbooks.Open (Path)

-----------------------------------------------------------------
Give free food http://www.thehungersite.com ||

Hey, wssup.. who is pana sona and nana cana and soona mana are you guys all one and the same??!!

Or just pulling our legs...?

FH.

I do not think there is a need to create two Excel objects. Try the code below
------------------------------------------------------------------------
Private Sub Command6_Click()
Dim xl As Object

Set xl = CreateObject("Excel.Application" )

xl.Workbooks.Open ( " C: work abc.xlsm" )
xl.Workbooks.Open ( "C: work xyz.xlsx" )

xl.Visible = True

xl.Run "abc.xlsm!Macro1"

xl.ActiveWorkbook.Close (True)

xl.Workbooks.Close

xl.Quit

Set xl = Nothing

End Sub

-----------------------------------------------------------------
Give free food http://www.thehungersite.com ||