edl Posted February 5, 2014 Share Posted February 5, 2014 Hello, I am having a tricky issue with Excel. In short, I have to automate opening, do something, and close excel thousands of time. I have a VBScript that does this core of this and works fine. The challenge is that there are some 'pop-ups' (such as the 'Save as') that can't be controlled/removed via VBScript (nor managed code such as VB.NET). AutoIT works great if I manually open the file, then press the X. (Excel save-as opens and AutoIT closes it perfectly) But if the file is opened via the script, I can not get AutoIT to work. I think this is because the main excel window is minimized, and only the pop-ups show. The very basic script is below (pls. note the comments to try different methods to click the Save as dialogue, none have worked). You will see some titles differ below between "Microsoft Excel" and "Microsoft Office Excel". The latter is the title of the save as box, the former the title of the Excel app itself. I've tried numerous permutations of trying to show the excel window, send keys to the master window, press buttons etc., all with no luck. The program is detecting the window fine, just not getting the key strokes to the dialogue window. I'm sure this problem must have been solved, but I've not come across it in numerous searches. Thanks in advance. - Ed Au3 Script #include <File.au3> MsgBox(0,"Monkey Click","Wait for Excel Windows - Press q to quit") Local $hFile HotKeySet ("q", "quitme") While 1 WinWaitActive("Microsoft Office Excel", "Do you want to save the changes you made") ;MsgBox(0,"found Excel Save", "Pressing ALT+N") ;Send("{RIGHT}") ;Send("{ENTER}") ;Local $hWnd = WinGetHandle("[CLASS:Microsoft Office Excel]") ;WinSetState("Microsoft Excel", "", @SW_SHOW) ; ControlSend("Microsoft Excel", "", "", "!n") ;MouseClick("left", 446, 345, 1) ;ControlClick("Microsoft Office Excel","","Button2") Send("!n") $hFile = FileOpen("f:devetlautoitExample.log", 1) _FileWriteLog($hFile, "Closed Excel Window") FileClose($hFile) sleep(5000) Wend Func quitme() Exit EndFunc Excerpt of VBScript Set objExcel = CreateObject("Excel.Application") objExcel.DisplayAlerts = 0 objExcel.Workbooks.open currentFile, false, true 'Excel Version is 2003 Link to comment Share on other sites More sharing options...
water Posted February 5, 2014 Share Posted February 5, 2014 Welcome to AutoIt and the forum! Don't automate the GUI - use the Excel UDF that comes with AutoIt. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Bert Posted February 5, 2014 Share Posted February 5, 2014 I've worked with excel.au3 on several occasions and it will do exactly what you need. Look in the helpfile. For example, from reading your post you will need to use _ExcelBookAttach($s_string [, $s_mode = "FilePath"]) The Vollatran project My blog: http://www.vollysinterestingshit.com/ Link to comment Share on other sites More sharing options...
edl Posted February 5, 2014 Author Share Posted February 5, 2014 (edited) Hello, I gave this method a shot. To simply any potential nuances on my code, I've run script #3, direct from the example The Temp.xls is created in my %temp% directory just fine. Excel opens, the title looks good, but It doesn't write to the cell, or close the workbook/Excel. Are there some OS/Excel version dependencies? http://www.autoitscript.com/autoit3/docs/libfunctions/_ExcelBookAttach.htm Out of curiosity, is there something 'special' with Excel (I'm sure there is as it's MS stuff) that would prevent AutoIT from just pressing the correct button on the Save box vs. the UDF method? It recognizes the box properly, and works when opening excel manually and thus in visible mode. Thank you. Edited February 5, 2014 by edl Link to comment Share on other sites More sharing options...
water Posted February 5, 2014 Share Posted February 5, 2014 Automating the GUI (pressing a button) is never as reliable as using the Excel UDF. Can you post the script you use? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
edl Posted February 5, 2014 Author Share Posted February 5, 2014 #include <Excel.au3> #include <File.au3> #include <MsgBoxConstants.au3> Local $sFilePath = @TempDir & "Temp.xls" $sFilePath = @TempDir & "Temp.xls" If Not _FileCreate($sFilePath) Then ;Create an .XLS file to attach to MsgBox($MB_SYSTEMMODAL, "Error", " Error Creating File - " & @error) EndIf _ExcelBookOpen($sFilePath) Local $oExcel = _ExcelBookAttach("Microsoft Excel - Temp.xls", "Title") ;with $s_mode = "Title" ==> Title of the Excel window _ExcelWriteCell($oExcel, "If you can read this, then Success!", 1, 1) ;Write to the Cell MsgBox($MB_SYSTEMMODAL, "Exiting", "Press OK to Save File and Exit") _ExcelBookClose($oExcel, 1, 0) ;This method will save then Close the file, without any of the normal prompts, regardless of changes Link to comment Share on other sites More sharing options...
Bert Posted February 5, 2014 Share Posted February 5, 2014 Look at _ExcelBookSave to save the file Don't use _FileCreate to make the file. Just use _ExcelBookNew then save it with the name you want. The Vollatran project My blog: http://www.vollysinterestingshit.com/ Link to comment Share on other sites More sharing options...
water Posted February 5, 2014 Share Posted February 5, 2014 (edited) _FileCreate does not create a valid Excel file. It creates an empty file. I would do the following: Manually create a Temp.xls file in @TempDir. The following script should then do what you need: #include <Excel.au3> Global $sFile = @TempDir & "\Temp.xls" $oExcel = _ExcelBookOpen($sFile) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Could not open Workbook " & $sFile & ". @error = " & @error) _ExcelWriteCell($oExcel, "If you can read this, then Success!", 1, 1) ;Write to the Cell If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Could not write to cell. @error = " & @error) _ExcelBookClose($oExcel, 1, 0) ;This method will save then Close the file, without any of the normal prompts, regardless of changes If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error", "Could not save Workbook " & $sFile & ". @error = " & @error) Edited February 5, 2014 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
edl Posted February 5, 2014 Author Share Posted February 5, 2014 This worked. Since my VBScript is currently opening the workbook, and you advise on how to attach to the running Excel and wait for the SaveAs dialogue? Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted February 5, 2014 Moderators Share Posted February 5, 2014 Why use the vbscript at all? Water shows you how to open the excel file in post #8. "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
water Posted February 5, 2014 Share Posted February 5, 2014 I wouldn't mix VBScript and AutoIt in your case. Either tell VBScript to suppress the pop ups or do everything in AutoIt. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
edl Posted February 5, 2014 Author Share Posted February 5, 2014 I wish I could tell VBScript to suppress the pop-up. I've looked into this for weeks, including an MS Premier Support Case and after speaking with two MS Engineers, one who was also wrote a C++ program in VStudio, verified that the hooks/options are not available for these windows. Considering my VBScript is about 1K lines of code (not including helper functions), I'll have to consider the rewrite option. I also recorded the keystrokes with the Au3Record and it was interesting that it didn't capture any of theses pop-up windows. I may try to keep Excel visible and see if that works. It's currently invisible because the workbooks are 10-20MB in size with 100+ tabs (pretty intense) and the extract slows down when visibly opening the books. Thanks for all the help! Donation incoming. Cheers. Link to comment Share on other sites More sharing options...
DicatoroftheUSA Posted February 5, 2014 Share Posted February 5, 2014 (edited) Try this I have had very good, but not perfect luck with it in the past. But I would bet using the excel UDF a thousand line code could be much much smaller to do the same thing. Edited February 5, 2014 by DicatoroftheUSA Statism is violence, Taxation is theft. Autoit Wiki Link to comment Share on other sites More sharing options...
water Posted February 5, 2014 Share Posted February 5, 2014 Do you know how the VBScript automates Excel? Using COM or by automating the GUI? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
edl Posted February 5, 2014 Author Share Posted February 5, 2014 Hello, I believe it's COM. @Universalist, I started coding this in some Excel bridges in Python and Perl before having to resort to the MS tools (lesson learned - I've been off windows for a long time). Because the sheets are so complex, macros, very long forumlas etc., all of the other tools would bomb at some point, sometimes running fine once, then hitting an exception the next time, with no clear answers. As for shorter code, I'm not sure. Basically I get access to all the workbooks and cells with the Excel VB Object. The length is due to the complexity of the logic to extract the data. Another method is via the OLEDB, but because of the wide range of versions of Excel, again, it wasn't robust enough. Thanks for the link and I may be new to AutoIT, but I've been using tools like Expect for eons. Link to comment Share on other sites More sharing options...
water Posted February 5, 2014 Share Posted February 5, 2014 If it is COM you should be able to suppress the pop ups. You can either provide the answer when calling the Excel method (example. Save or SaveAs) or by setting the Excel application property DisplayAlerts to False so Excel chooses the default answer. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
jdelaney Posted February 5, 2014 Share Posted February 5, 2014 (edited) redundant to use VBS to manipulate excel AND autoit. But you can 'jump' to the _excel udf function, to see the basics of what you must do for your VBS: Func _ExcelBookSave($oExcel, $fAlerts = 0) If Not IsObj($oExcel) Then Return SetError(1, 0, 0) If $fAlerts > 1 Then $fAlerts = 1 If $fAlerts < 0 Then $fAlerts = 0 With $oExcel .Application.DisplayAlerts = $fAlerts .Application.ScreenUpdating = $fAlerts .ActiveWorkBook.Save() If Not $fAlerts Then .Application.DisplayAlerts = 1 .Application.ScreenUpdating = 1 EndIf EndWith Return 1 EndFunc ;==>_ExcelBookSave above shows how to suppress alerts. The syntax doesn't cross over, generally, 100%, but it's 90-95% there. Side note, this is an excel vbs question, not an autoit one Edited February 5, 2014 by jdelaney IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window. Link to comment Share on other sites More sharing options...
edl Posted February 5, 2014 Author Share Posted February 5, 2014 Agree, and I've suppressed most of these pop-ups already via the COM parameters. I'm going to dig into the following settings a little deeper this evening. Set objExcel = CreateObject("Excel.Application") objExcel.DisplayAlerts = 0 objExcel.EnableEvents = False Thanks for the quick responses! Cheers, - Ed 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