rosanchez Posted March 8, 2009 Share Posted March 8, 2009 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.QuitDoc1.doc Link to comment Share on other sites More sharing options...
PsaltyDS Posted March 8, 2009 Share Posted March 8, 2009 ;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. 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 Link to comment Share on other sites More sharing options...
rosanchez Posted March 9, 2009 Author Share Posted March 9, 2009 I can't find any help in the _Excel* functions, I really don't know what to do!!! Please Help!!!!!!!!!!!!!!!! Link to comment Share on other sites More sharing options...
PsaltyDS Posted March 9, 2009 Share Posted March 9, 2009 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. 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 Link to comment Share on other sites More sharing options...
reb Posted March 9, 2009 Share Posted March 9, 2009 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 excelWinWait("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")ExitI have a macro button located at these co-ordinatesHope this helpsREB MEASURE TWICE - CUT ONCE Link to comment Share on other sites More sharing options...
ToyBoi Posted March 9, 2009 Share Posted March 9, 2009 I am not sure if I understand your problem completely but here is how I deal with excelWinWait("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")ExitI have a macro button located at these co-ordinatesHope this helpsREBi 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 Link to comment Share on other sites More sharing options...
PsaltyDS Posted March 9, 2009 Share Posted March 9, 2009 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") 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 Link to comment Share on other sites More sharing options...
reb Posted March 9, 2009 Share Posted March 9, 2009 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") 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 Link to comment Share on other sites More sharing options...
PsaltyDS Posted March 9, 2009 Share Posted March 9, 2009 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 againYou're welcome, and welcome to the wider world of AutoIt! 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 Link to comment Share on other sites More sharing options...
rosanchez Posted March 11, 2009 Author Share Posted March 11, 2009 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!!!! expandcollapse popup#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 Link to comment Share on other sites More sharing options...
PsaltyDS Posted March 11, 2009 Share Posted March 11, 2009 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?! Did you just want to send the year in $i? That would just be: Send($i) 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now