k1ng87
asked on
script to open and run macro in excel
I'm trying to write a script to open and run an excel macro but can't get it to work....it seems to open but nothing happens....
Sub Button1_OnClick()
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("Y:\North America\Scorecarding\APD\APD_Template0910.xlsm")
objWorkbook.Application.run "APD_Template0910.xlsm!Macro2.Macro"
End Sub
Just change the following line
From: objWorkbook.Application.ru n "APD_Template0910.xlsm!Mac ro2.Macro"
To:objWorkbook.Application .run "APD_Template0910.xlsm!Mac ro2"
It should work....Good Luck !!!
From: objWorkbook.Application.ru
To:objWorkbook.Application
It should work....Good Luck !!!
k1ng87,
I took the line:
objWorkbook.Application.ru n "APD_Template0910.xlsm!Mac ro2.Macro"
to mean that you had a module named Macro2, and in that module was a sub or function named Macro.
Is this the case?
Patrick
I took the line:
objWorkbook.Application.ru
to mean that you had a module named Macro2, and in that module was a sub or function named Macro.
Is this the case?
Patrick
ASKER
no...the macro is named Macro2....
I tried this....objWorkbook.Applic ation.run "APD_Template0910.xlsm!Mac ro2.Macro"
but no luck...
when I run the script I just get the "hourglass" for a split second and then nothing....
I tried this....objWorkbook.Applic
but no luck...
when I run the script I just get the "hourglass" for a split second and then nothing....
k1ng87,
Then have you tried hitsdoshi1's suggestion?
Patrick
Then have you tried hitsdoshi1's suggestion?
Patrick
Try just this.......objWorkbook.App lication.r un "APD_Template0910.xlsm!Mac ro2" without ".Macro" at the end......and what exactly does your "Macro2" do ? Can you just make it a simple macro for test.....just display a msgbox or something...just to figure out whether its script error or macro error....
ASKER
tried this with no luck...I don't get a msg box, error, or anything...
Sub Button1_OnClick()
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("Y:\North America\Scorecarding\APD\APD_Template0910.xlsm")
objWorkbook.Application.run "APD_Template0910.xlsm!Macro2"
End Sub
can you attach a sample file ?
Replace
objWorkbook.Application.ru n "APD_Template0910.xlsm!Mac ro2"
with
objWorkbook.Run "APD_Template0910.xlsm!Mac ro2"
If that doesn't work, please answer (as I asked a while ago) what the macro security setting is.
objWorkbook.Application.ru
with
objWorkbook.Run "APD_Template0910.xlsm!Mac
If that doesn't work, please answer (as I asked a while ago) what the macro security setting is.
ASKER
Matt,
The code still does not work....as for the security settings....all macro's is enabled and my network drive is a trusted location as well.
The code still does not work....as for the security settings....all macro's is enabled and my network drive is a trusted location as well.
Sub Button1_OnClick()
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("Y:\North America\Scorecarding\APD\APD_Template0910.xlsm")
objWorkbook.Run "APD_Template0910.xlsm!Macro2"
End Sub
ASKER
I dont' think I can attached the xlsm file because of financial numbers that are in there already.
ASKER
do I need to "Dim" anything?
ASKER
I think my answer lies in here somewhere.....http://www.sqlservercentral.com/Forums/Topic413593-148-2.aspx
I tihnk i need to re-write it in vb.net
I tihnk i need to re-write it in vb.net
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I couldn't figure out what was wrong with that code.....anyway.....glad to hear that it worked...:)
What is the macro security setting on the computer you tried that on?
Is the VB Project for that workbook digitally signed?
Regards,
Patrick