Sign in to follow this  
Followers 0
Juvigy

Little help with excel file.

9 posts in this topic

I need some help. I have a excel file and i have a macro file (.BAS) file. I need to run the macro on the excel file. The whole thing i want to do is :

1.Download a zip file from a network location - shared folder

2.Extract an .xls file form the zip.

3.Apply the macro to the .xls.

4.Save the file.

It will be great if i can manage to do all that with excel COM .The only thing i dont have any idea how to do it is number 3.

Help please.

Share this post


Link to post
Share on other sites



I think the Application.Run method will accomplish what you desire.

The function prototype is Run([Macro], [Arg1], [Arg2], [Arg3], [Arg4], [Arg5], [Arg6], [Arg7], [Arg8], [Arg9], [Arg10], [Arg11], [Arg12], [Arg13], [Arg14], [Arg15], [Arg16], [Arg17], [Arg18], [Arg19], [Arg20], [Arg21], [Arg22], [Arg23], [Arg24], [Arg25], [Arg26], [Arg27], [Arg28], [Arg29], [Arg30])

I think you would need to do something like:

$oExcel.Run("Macro.BAS!My_Func_Name")

Let me know if you can get that to work.

Share this post


Link to post
Share on other sites

As far as I can tell, there is no way to execute a macro from the Excel command line. There is a third-party utility that will allow you to do it though. Check this out.

You can also use the Auto_Open() or Workbook_Open() methods to run a macro as soon as a workbook is opened. So you could maybe use COM to open your workbook, create a one of those macros which will reference you .BAS file. It doesn't sound like you want the macro running every time the workbook is opened though. So you should maybe check out the 3rd party software.

Share this post


Link to post
Share on other sites

I need some help. I have a excel file and i have a macro file (.BAS) file. I need to run the macro on the excel file. The whole thing i want to do is :

1.Download a zip file from a network location - shared folder

2.Extract an .xls file form the zip.

3.Apply the macro to the .xls.

4.Save the file.

It will be great if i can manage to do all that with excel COM .The only thing i dont have any idea how to do it is number 3.

Help please.

The last post in this thread shows how to use the COM object to import a .BAS file: http://www.autoitscript.com/forum/index.php?showtopic=77545

Share this post


Link to post
Share on other sites

I tried to execute the script there but i get error on this line:

$oModules = $oExcel.ActiveWorkbook.VBProject.VBComponents

Any idea why?

Share this post


Link to post
Share on other sites

I tried to execute the script there but i get error on this line:

$oModules = $oExcel.ActiveWorkbook.VBProject.VBComponents

Any idea why?

insufficient info to debug -- post a SMALL SAMPLE SCRIPT and environment info, and someone may be able to help you.

Reading the help file before you post... Not only will it make you look smarter, it will make you smarter.

Share this post


Link to post
Share on other sites

Found out why i got it. Need to enable the excel object model access .Use the bellow instructions to enable that.

Office 2003 and Office XP

;1. Open the Office 2003 or Office XP application in question. On the Tools menu, click Macro, and then click Security to open the Macro Security dialog box.

;2. On the Trusted Sources tab, click to select the Trust access to Visual Basic Project check box to turn on access.

;3. Click OK to apply the setting. You may need to restart the application for the code to run properly if you automate from a Component Object Model (COM) add-in or template.

;Back to the top

;Office 2007

;1. Open the 2007 Microsoft Office system application in question. Click the Microsoft Office button, and then click Application Options.

;2. Click the Trust Center tab, and then click Trust Center Settings.

;3. Click the Macro Settings tab, click to select the Trust access to the VBA project object model check box, and then click OK.

;4. Click OK.}

Now a shot in the dark - is there a way to check and apply that setting with a script?

Share this post


Link to post
Share on other sites

Now a shot in the dark - is there a way to check and apply that setting with a script?

In Excel 2003, this is controlled by the following registry entry:

[HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Security]
"AccessVBOM"=dword:00000001

00000001 is enabled, 00000000 is disabled.

Change is only effective if made before Excel (or its COM object) is started.

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

Great help. 10x

This is for 2007 ;

[HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security]

"AccessVBOM"=dword:00000001

Edited by Juvigy

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0