Little help with excel file.

[Juvigy]

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.

[SRAutomater]

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.

[SRAutomater]

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.

[ResNullius]

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

[Juvigy]

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

$oModules = $oExcel.ActiveWorkbook.VBProject.VBComponents

Any idea why?

[flyingboz]

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.

[Juvigy]

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?

[ResNullius]

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.

[Juvigy]

Great help. 10x

This is for 2007 ;

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

"AccessVBOM"=dword:00000001

Edited October 29, 2008 by Juvigy