watsdm4 Posted March 6, 2007 Share Posted March 6, 2007 Hi, nOOb to the AutoIt scene here. I'd like to run, or at least find a way to parse my original Excel macros from AutoIt. I've downloaded the latest ExcelCOM UDF and I don't see any functions related to my problem. Preferrably, I want to stay away from sending keystrokes, unless there is no other option. In searching through the archives, I've come up empty-handed. So, please help! This part of the code works perfectly.....I just don't have good experiences with end-users and keystroke automation. $Modules = "\\orf-fs\sys\EI\Test\Canon Script Files\filestemp.xls" $CSVPath = "\\orf-fs\sys\EI\Test\Canon Script Files\files.csv" $oExcel = _ExcelBookOpen($Modules,1,False) ;This is where macro automation would be nice Send("{ALT}tmm") Send("cleansheet{ENTER}") $answer = MsgBox(1, "Expeditors: Line Item Assistant", "Add your files to the Excel Spreadsheet, then click OK to continue.") If $answer = 2 Then _ExcelBookClose($oExcel,2,-1) Exit EndIf If Not WinActive("filestemp.xls") Then WinActivate("filestemp.xls") ;This is where macro automation would be nice Send("{ALT}tmm") Send("addzeros{ENTER}") _ExcelBookSaveAs($oExcel,$CSVPath,"txt",-1,2) _ExcelBookClose($oExcel,-1,-1) Any suggestions would be greatly appreciated. Link to comment Share on other sites More sharing options...
Locodarwin Posted March 7, 2007 Share Posted March 7, 2007 You won't believe how easy it actually is. Try this up there in place of your keystrokes: $oExcel.Application.Run("Macro_Name", "Parameter1", "Parameter2") oÝ÷ Ù©§¶ªº^v+^Ì"¶¨ºº0Ü¢êìx÷«ªljëh×6 $oExcel.Application.Run("cleansheet") -S (Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent] Link to comment Share on other sites More sharing options...
watsdm4 Posted March 7, 2007 Author Share Posted March 7, 2007 Thanks Locodarwin! Worked like a charm. Your UDF is a godsend for me and my colleagues. BTW, I commented out the last DisplayAlerts line on your _ExcelBookClose function to work with CSV's. I'm running Office 2000 at work and the default setting for comma-delimited files is set to ask the user to save upon close, regardless of the save status. I haven't noticed any adverse effects by doing this......just thought you should know. Func _ExcelBookClose($oExcel, $fSave = 1, $fAlerts = 0) If NOT IsObj($oExcel) Then Return SetError(1, 0, 0) If $fSave > 1 Then $fSave = 1 If $fSave < 0 Then $fSave = 0 If $fAlerts > 1 Then $fAlerts = 1 If $fAlerts < 0 Then $fAlerts = 0 $oExcel.Application.DisplayAlerts = $fAlerts $oExcel.Application.ScreenUpdating = $fAlerts If $fSave Then $oExcel.ActiveWorkBook.Save EndIf ; $oExcel.Application.DisplayAlerts = True $oExcel.Application.ScreenUpdating = True $oExcel.Quit Return 1 EndFunc ;==>_ExcelBookClose Link to comment Share on other sites More sharing options...
Locodarwin Posted March 7, 2007 Share Posted March 7, 2007 Thanks for the positive feedback! I'm glad you find the collection useful. I set both DisplayAlerts and ScreenUpdating back to True just to reset Excel back to default for both properties. I do this because whatever you set these properties to gets saved either on the application level or the workbook level - I'm unsure which. They remain what they were set to until specifically set otherwise, like a state machine. What I should do at some point in the future is make turning on/off alerts and screen updating a separate function. Unfortunately there is some weirdness that occurs for a number of users where setting the saved property to True and turning off DisplayAlerts still does not suppress the save alert - even for standard .XLS files. I'm still trying to bang up a solution for that as well. -S (Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent] Link to comment Share on other sites More sharing options...
JohnBailey Posted March 21, 2007 Share Posted March 21, 2007 Gang, I want to make sure I understand this correctly and it's not just a glitch-in-my-matrix When I use _ExcelBookOpen the "do you want to run the macro" window does not come up. Is this normal? A decision is a powerful thing 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