Excel import/start macro

[dpk]

Hi Peeps, Can someone help this noob please.

I have a simple script below which opens a tab delimited text file in Excel. My problem is that I would like to import a macro "Overviewfile.bas" and then run it on the excel file that has just been opened. I have experimented but got nowhere. Can someone help me create these extra lines of code to accomplish this.

#include <Excel.au3>
$file="FView_WO_Dump_31January2012.txt"                         ; tab delimited text file
$macro_location = "MacrosOverviewFile.bas"                         ;Macro I want to run


$oExcel = _ExcelBookOpen($file)
If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object!")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist!")
    Exit
EndIf

Import MACRO
Run MACRO

Edited February 1, 2012 by dpk

[someone]

I can't test these out for you, but maybe this gets you pointed in the right direction-

To run a macro, use Application.Run "'Nameofspreadsheet'!NameOfMacro"

To import a macro, looks like there is an import command... look at these 2 sources

http://www.mrexcel.com/articles/copy-vba-module.php ;for importing

http://www.mrexcel.com/forum/showthread.php?t=65850 ;for running

Please let us know how it goes.

And welcome to the forum!

Edited February 1, 2012 by someone

[dpk]

Thanks for the response - Both those links goto places that talk about using macro's within Excel but what I'm trying to do is use AutoIT and the Excel library to open a text file with excel (so far this bit works) and then import a macro into the excel file (because none of my macro's show when autoit opens the file) and then to run that macro on the currently open file.

The website did cover "VBE.ActiveProject.VBComponents.Export and VBE.ActiveProject.VBComponents..Import commands take care of getting the macros copied to the new books." I tried searching the forums to see if someone had converted to AUTOIT code but no success with some of the scripts that were posted.

[someone]

From your post, this is what I gather your talking about-

#include 
$oExcel = _ExcelBookNew()
$oExcel.VBE.ActiveVBProject.VBComponents.Import(@DesktopDir & "mymacro.bas")
$oExcel.Run("nameofmacrohere")

I tested this out, and it imports the bas file to a new workbook, and runs it.

EDIT to fix the code tags and... just letting you know, a lot of vb code can be pretty easily converted. It can help you when searching google for things, as you don't have to try to search for someone who has done it in autoit only for the vb equivalent.

Edited February 2, 2012 by someone

[dpk]

Hi mate and many thanks - ultimately your link held some info that solved the problem. I had to lift some other info from another message on the forum that talked about changing the trust center settings to get this to work.

1. Open Excel 2007. Click the Microsoft Excel Office button, and then click Exce lOptions.

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

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

4. Click OK

In any event that allows me to import and then run Excel Macros using AutoIT.

[coffeeturtle]

Hi mate and many thanks - ultimately your link held some info that solved the problem. I had to lift some other info from another message on the forum that talked about changing the trust center settings to get this to work. 1. Open Excel 2007. Click the Microsoft Excel Office button, and then click Exce lOptions. 2. Click the Trust Center tab, and then click Trust Center Settings. 3. Click the Macro Settings tab, click to select “Trust access to the VBA project object model” check box, and then click OK. 4. Click OK In any event that allows me to import and then run Excel Macros using AutoIT.

did you use "someone"'s code, or did you have to modify it? My Import is not importing.

Thanks for any help.

Office 2010 (Trust Center settings set as you described)

Edited February 10, 2012 by coffeeturtle

[someone]

I def ran the code posted with a .bas file I have, and excel imports the macro, and the macro pops up a msgbox, so I can definitely say it ran. I believe I have my trust center settings to pretty much allow everything though... is the code I posted not importing or running a macro for you?

Edit - Running MS Office 2007 Pro

Edited February 10, 2012 by someone

[coffeeturtle]

$oExcel.VBE.ActiveVBProject.VBComponents.Import(@DesktopDir & "mymacro.bas")

It worked! Thanks!

I may put in some error handling with IsObj to make sure it runs properly each time.

Thanks!

Edited February 10, 2012 by coffeeturtle

[someone]

Cool glad it worked. I may end up needing to use this myself, so good to know it works

[coffeeturtle]

Also see Water's response here: