Sign in to follow this  
Followers 0
ToyBoi

Autoit to run excel macro

4 posts in this topic

Hi everyone, I have an autoit script which will log into a LAN site and navigate to the correct site, which is needed for the vba web query to work.

here is my simplified version of my code

#include <GuiConstants.au3>
#include <IE.au3>
#include <File.au3>
#include <ExcelCOM_UDF.au3> 

;Creates a folder according to the username and current date on the Desktop
$Address = "C:\Documents and Settings\" & @UserName & "\Desktop\" & @MON & "." & @MDAY & "." & @YEAR
DirCreate($Address ) 

;This is a list of all the options that autoit will have to select
$NumberOfFrequencies = 84
Dim $Freq[$NumberOfFrequencies]
$Freq[0]="ALL"
$Freq[1]="103000000"
$Freq[2]="105000000"
$Freq[3]="109000000"
.
.
.

;creates an excel file and make 84 sheets and name them accordingly
$oExcel = _ExcelBookNew(1)
_ExcelSheetAddNew($oExcel, "NA")
_ExcelSheetAddNew($oExcel, $Freq[82])
_ExcelSheetAddNew($oExcel, $Freq[81])
_ExcelSheetAddNew($oExcel, $Freq[80])
.
.
.

; goes to the site that I want, LAN is a replacement for the actual address which I'm not allowed to disclose
$oIE = _IECreate()
_IENavigate($oIE, "LAN")

; i then have code that will log in and navigate in there

;
;this is the part of code where it will select a drop down and activate a page, i then have autoit to go back to the excel page

For $i = 0 To $NumberOfFrequencies
    
    $oForm = _IEFormGetObjByName ($oIE, "filterBar")
    $oSelect = _IEFormElementGetObjByName ($oForm, "frequency")
    _IEFormElementOptionselect ($oSelect, $Freq[$i], 1, "byValue")
    _IELoadWait ($oIE)
    
;goes to excel, and activates the right page
;goes to excel, and activates the right page
    WinActivate("[TITLE:Microsoft Excel]", "")
    _ExcelSheetActivate($oExcel, $Freq[$i])
    sleep(400)
    WinActivate("[TITLE:SciCare Retriever]", "")
    sleep(1000)

Next

Say if I have a macro called "Run" in the PERSONAL.xls, how can I have autoit to run this "Run" macro ?

Share this post


Link to post
Share on other sites



Excel's macro functionality is much the same as Word's. So if you look at how I designed _WordMacroRun() you should be able to figure out how to modify it to work with Excel.

Share this post


Link to post
Share on other sites

I had the same issue. This worked for me.

Dim $objXL

$objXL = ObjCreate("Excel.Application")

With $objXL.Application

.Visible = True

;Open the Workbook

.Workbooks.Open ("xxxxx.xls")

$x = .Run("MacroName")

EndWith

Share this post


Link to post
Share on other sites

I had the same issue. This worked for me.

Dim $objXL

$objXL = ObjCreate("Excel.Application")

With $objXL.Application

.Visible = True

;Open the Workbook

.Workbooks.Open ("xxxxx.xls")

$x = .Run("MacroName")

EndWith

thanks, another problem that I am having is that everytime my code is run it creates a new excel file, and my PERSONALS.XLS is not opened with it. So basically I need to find a way to install the macro into this new excel file. I found a code some where on the forum which imports a module

$oModules = $oExcel.ActiveWorkbook.VBProject.VBComponents

$oModules.Import(@ScriptDir & "\videogooddncsa.bas")

but when i ran it, i got an error

C:\Documents and Settings\RobertR.Li\Desktop\Retriever\DNCS A.au3 (202) : ==> The requested action with this object has failed.:

$oModules = $oExcel.ActiveWorkbook.VBProject.VBComponents

$oModules = $oExcel.ActiveWorkbook.VBProject^ ERROR

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