Sign in to follow this  
Followers 0
dpk

Excel import/start macro

10 posts in this topic

#1 ·  Posted (edited)

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 by dpk

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by someone

While ProcessExists('Andrews bad day.exe')
	BlockInput(1)
	SoundPlay('Music.wav')
	SoundSetWaveVolume('Louder')
WEnd

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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 by someone
1 person likes this

While ProcessExists('Andrews bad day.exe')
	BlockInput(1)
	SoundPlay('Music.wav')
	SoundSetWaveVolume('Louder')
WEnd

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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 by coffeeturtle

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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 by someone

While ProcessExists('Andrews bad day.exe')
	BlockInput(1)
	SoundPlay('Music.wav')
	SoundSetWaveVolume('Louder')
WEnd

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

$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 by coffeeturtle

Share this post


Link to post
Share on other sites

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


While ProcessExists('Andrews bad day.exe')
	BlockInput(1)
	SoundPlay('Music.wav')
	SoundSetWaveVolume('Louder')
WEnd

Share this post


Link to post
Share on other sites

Also see Water's response here:

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