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 ||
Advertise Contact Us Privacy Policy and Terms of Usage FAQ Canadian Desi © 2001 Marg eSolutions Site designed, developed and maintained by Marg eSolutions Inc. |