Link to home
Start Free TrialLog in
Avatar of k1ng87
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

Open in new window

Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Hello k1ng87,

What is the macro security setting on the computer you tried that on?

Is the VB Project for that workbook digitally signed?

Regards,

Patrick
Avatar of hitsdoshi1
hitsdoshi1

Just change the following line
From: objWorkbook.Application.run "APD_Template0910.xlsm!Macro2.Macro"

To:objWorkbook.Application.run "APD_Template0910.xlsm!Macro2"



It should work....Good Luck !!!
k1ng87,

I took the line:

objWorkbook.Application.run "APD_Template0910.xlsm!Macro2.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
Avatar of k1ng87

ASKER

no...the macro is named Macro2....

I tried this....objWorkbook.Application.run "APD_Template0910.xlsm!Macro2.Macro"  

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
Try just this.......objWorkbook.Application.run "APD_Template0910.xlsm!Macro2" 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....

Avatar of k1ng87

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

Open in new window

can you attach a sample file ?
Replace

objWorkbook.Application.run "APD_Template0910.xlsm!Macro2"

with

objWorkbook.Run "APD_Template0910.xlsm!Macro2"

If that doesn't work, please answer (as I asked a while ago) what the macro security setting is.
Avatar of k1ng87

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.
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

Open in new window

Avatar of k1ng87

ASKER

I dont' think I can attached the xlsm file because of financial numbers that are in there already.
Avatar of k1ng87

ASKER

do I need to "Dim" anything?
Avatar of k1ng87

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
ASKER CERTIFIED SOLUTION
Avatar of k1ng87
k1ng87

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I couldn't figure out what was wrong with that code.....anyway.....glad to hear that it worked...:)