Jump to content
Sign in to follow this  
rosanchez

Run a macro of Excel

Recommended Posts

rosanchez

Hi, I am a beginner in using Autoit, however so far it has been very helpful in my work.

Now I have a problem in trying to run a macro of Excel. The macro is used to download datasets using a VPN connection.

I can do it manually, but I want to download the information automatically.

So I tried to write a code that opens a new Excel document and the requests the data for each country (after I solve the problem I will do the loop for each country).

But the problem is that I can't fill the camps to request the information. I tried to write a code that simulates the press of buttons, but no success.

I send the example of the code (as well as the screenshot), so if someone can help me with this, I would really appreciate.

I don't know what help file I should read, and where to find some information about this problem

Thank you very much!!!

#include <Misc.au3>
#include <EditConstants.au3>
#include <WindowsConstants.au3>
#include <GUIConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <SliderConstants.au3>
#include <TabConstants.au3>
#include <ButtonConstants.au3>
#include <GuiSlider.au3>
#include <ExcelCOM_UDF.au3>
#include <array.au3>
#include <string.au3>


$sAddIn = @ScriptDir & "\" & "AdvanceOffice.xla"
$pBook = _ExcelBookNew()

$oExcel = ObjCreate("Excel.Application")
$oExcel.WorkBooks.Add
$oAddIn = $oExcel.AddIns.Add($sAddIn)
$oAddIn.Installed = True
;HotKeySet("!xa{ENTER}", "ShowMessage")

HotKeySet("!xa", "ShowMessage")

;$objXL.Run($sAddIn.Name & "!xa")
;$oExcel.Quit

Doc1.doc

Share this post


Link to post
Share on other sites
PsaltyDS

;HotKeySet("!xa{ENTER}", "ShowMessage")

HotKeySet("!xa", "ShowMessage")
Your hot key will ignore the 'a' and trigger on '!x' (which is Alt-x). You can't use HotKeySet() on multiple typing keys at once.

You might look at the _Excel* functions in the help file to get more ideas on what can be done.

:P


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
rosanchez

I can't find any help in the _Excel* functions, I really don't know what to do!!!

Please Help!!!!!!!!!!!!!!!!

Share this post


Link to post
Share on other sites
PsaltyDS

I can't find any help in the _Excel* functions, I really don't know what to do!!!

Please Help!!!!!!!!!!!!!!!!

What version of AutoIt do you have?

The current production version is 3.3.0.0, which includes the Excel.au3 UDF containing many useful Excel functions. They are all listed in the help file (AutoIt3.chm) which is linked in the start menu under AutoIt.

If you don't have the current version, or the dog ate your help file, you can't be helped much.

:P


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
reb

I can't find any help in the _Excel* functions, I really don't know what to do!!!

Please Help!!!!!!!!!!!!!!!!

I am not sure if I understand your problem completely but here is how I deal with excel

WinWait("Microsoft Excel - Putnam daily balances Revised","")

If Not WinActive("Microsoft Excel - Putnam daily balances Revised","") Then WinActivate("Microsoft Excel - Putnam daily balances Revised","")

WinWaitActive("Microsoft Excel - Putnam daily balances Revised","")

MouseMove(798,192)

MouseDown("left")

MouseUp("left")

Exit

I have a macro button located at these co-ordinates

Hope this helps

REB


MEASURE TWICE - CUT ONCE

Share this post


Link to post
Share on other sites
ToyBoi

I am not sure if I understand your problem completely but here is how I deal with excel

WinWait("Microsoft Excel - Putnam daily balances Revised","")

If Not WinActive("Microsoft Excel - Putnam daily balances Revised","") Then WinActivate("Microsoft Excel - Putnam daily balances Revised","")

WinWaitActive("Microsoft Excel - Putnam daily balances Revised","")

MouseMove(798,192)

MouseDown("left")

MouseUp("left")

Exit

I have a macro button located at these co-ordinates

Hope this helps

REB

i usually just create a new excel file, use keypress to record a new macro and paste the macro in it (from a saved txt file) and then run it

Share this post


Link to post
Share on other sites
PsaltyDS

I am not sure if I understand your problem completely but here is how I deal with excel

WinWait("Microsoft Excel - Putnam daily balances Revised","")

If Not WinActive("Microsoft Excel - Putnam daily balances Revised","") Then WinActivate("Microsoft Excel - Putnam daily balances Revised","")

WinWaitActive("Microsoft Excel - Putnam daily balances Revised","")

MouseMove(798,192)

MouseDown("left")

MouseUp("left")

Exit

I have a macro button located at these co-ordinates

Hope this helps

REB

You might try the _Excel* functions of Excel.au3 UDF:
#include <Excel.au3>

Global $sXLSFile = "C:\Temp\PutnamDailyBalanceRev.xls"

; Attach if open already, else open file
Global $oExcel = _ExcelBookAttach($sXLSFile)
If @error Then $oExcel = _ExcelBookOpen($sXLSFile)
$oExcel.Run("Your_Macro_Name")

:P


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
reb

You might try the _Excel* functions of Excel.au3 UDF:

#include <Excel.au3>

Global $sXLSFile = "C:\Temp\PutnamDailyBalanceRev.xls"

; Attach if open already, else open file
Global $oExcel = _ExcelBookAttach($sXLSFile)
If @error Then $oExcel = _ExcelBookOpen($sXLSFile)
$oExcel.Run("Your_Macro_Name")

:P

Thank you. I will defiantly try this out. I have been working with AutoIt for a while in my own little world and have been backward about posting.

I din't want to appearstupid I guess, but your kind reply makes me feel good about jumping in now Thank you again


MEASURE TWICE - CUT ONCE

Share this post


Link to post
Share on other sites
PsaltyDS

Thank you. I will defiantly try this out. I have been working with AutoIt for a while in my own little world and have been backward about posting.

I din't want to appearstupid I guess, but your kind reply makes me feel good about jumping in now Thank you again

You're welcome, and welcome to the wider world of AutoIt!

:P


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
rosanchez

I am almost done, I wrote an script that makes a loop to download information with excel for many years

there is only one problem.

I want to to the loop for a number of years (1980-2008) and for a list of countries (Arg, Bra, Col, etc...)

the only problem is that the function send($) doesn't recognize the global variables, I mean, the name of the country and the year in the loop

if I can solve this problem, I will complete my job,

in the help file i found the following

If you with to use a variable for the count, try

$n = 4

Send("+{TAB " & $n & "}")

but doesn't work

thanks!!!!

#include <Misc.au3>
#include <EditConstants.au3>
#include <WindowsConstants.au3>
#include <GUIConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <SliderConstants.au3>
#include <TabConstants.au3>
#include <ButtonConstants.au3>
#include <GuiSlider.au3>
#include <ExcelCOM_UDF.au3>
#include <array.au3>
#include <string.au3>

$i = 1980
While $i<=2008
    $pBook = _ExcelBookNew()
    $File = "FVENZ"
    $FileNameOut = $File & $i
    $sAddIn = @ScriptDir & "\" & "AdvanceOffice.xla"


    $oExcel = ObjCreate("Excel.Application")
    $oExcel.WorkBooks.Add
    $oAddIn = $oExcel.AddIns.Add($sAddIn)
    $oAddIn.Installed = True

    Send("!x")
    Send("{TAB}")
    Send("{ENTER}")
    Send("{ENTER}")
    Send("FVENZ")
    Send("{TAB}")
    Send("{TAB}")
    Send("{TAB}")
    Send("MNEM, GEOGC, NAME, WC05601, WC06004, EXCODE")
    Send("{TAB}")
    Send("{TAB}")
    Send("{TAB}")
    
    Send("+{TAB " & $i & "}")
    ;Send("1994")
    
    Send("{ENTER}")
    _ExcelBookSaveAs($pbook, @ScriptDir & "\" & $FileNameOut, "csv", 0, 1)
    $i=$i+1
WEnd

Share this post


Link to post
Share on other sites
PsaltyDS

I am almost done, I wrote an script that makes a loop to download information with excel for many years

there is only one problem.

I want to to the loop for a number of years (1980-2008) and for a list of countries (Arg, Bra, Col, etc...)

the only problem is that the function send($) doesn't recognize the global variables, I mean, the name of the country and the year in the loop

if I can solve this problem, I will complete my job,

in the help file i found the following

If you with to use a variable for the count, try

$n = 4

Send("+{TAB " & $n & "}")

but doesn't work

That sends Shift+TAB four times. What did you want it to do?

$i = 1980
While $i<=2008
    
    <snip>

    Send("+{TAB " & $i & "}")

    <snip>

WEnd
You want to send Shift-TAB one thousand nine hundred and eighty times?! :unsure:

Did you just want to send the year in $i? That would just be:

Send($i)

:P


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

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  

×