script to open and run macro in excel

k1ng87
k1ng87 used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
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
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 !!!
Top Expert 2010

Commented:
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
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Author

Commented:
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....
Top Expert 2010

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

Author

Commented:
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 ?
Top Expert 2010

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

Author

Commented:
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

Author

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

Author

Commented:
do I need to "Dim" anything?

Author

Commented:
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
Commented:
got it to work....needed to write it in vb.net and not vbs....
	Public Sub Main()

        Dim objexcel As Object, objworkbook As Object

        objexcel = CreateObject("Excel.Application")

        objexcel.visible = True
        objworkbook = objexcel.Workbooks.Open("Y:\North America\Scorecarding\APD\APD_Template0910.xlsm")

        objworkbook.application.Run("APD_Template0910.xlsm!Macro2")
		Dts.TaskResult = Dts.Results.Success
	End Sub

Open in new window

I couldn't figure out what was wrong with that code.....anyway.....glad to hear that it worked...:)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial