Xanadin 0 Posted August 21, 2011 I made a script to Refresh Excel Web data query in every minute and to copy+paste the data in a txt file. It is working fine. Then I wanted to add the hide feature in the excel file so that it works without the excel file being visible. The code [ WinSetState("Test", "", @SW_HIDE)] nicely hides the Excel file but it disables the "Refresh All" command. Is there any way to make my script work as usual but in the hide mode? Please help. Here is my script: expandcollapse popup#include <Excel.au3> #include <Date.au3> #include <File.au3> AutoItSetOption("WinTitleMatchMode",2) $sFilePath1 = @ScriptDir & "\Test.xls" $oExcel = _ExcelBookOpen($sFilePath1) WinSetState("Test", "", @SW_HIDE) ; HERE IS THE PROBLEM. THE SCRIPT WORKS NORMALLY FINE, BUT IT DOES NOT REFRESH THE DATA WHEN I INCLUDE THIS LINE. If @error = 1 Then MsgBox(0, "Error!", "Unable to Create the Excel Object") Exit ElseIf @error = 2 Then MsgBox(0, "Error!", "File does not exist - Shame on you!") Exit EndIf Do WinActivate("Test") WinWaitActive("Test") Sleep(100) ControlSend("Test" , "", "MsoCommandBar2", "{ALT}") Sleep(100) ControlSend("Test" , "", "MsoCommandBar2", "^+{TAB}") Sleep(100) ControlSend("Test" , "", "MsoCommandBar2", "{TAB}") Sleep(100) ControlSend("Test" , "", "MsoCommandBar2", "{TAB}") Sleep(100) ControlSend("Test" , "", "MsoCommandBar2", "{TAB}") Sleep(100) ControlSend("Test" , "", "MsoCommandBar2", "{TAB}") Sleep(100) ControlSend("Test" , "", "MsoCommandBar2", "{TAB}") Sleep(100) ControlSend("Test" , "", "MsoCommandBar2", "{ENTER}") Sleep(60000) ControlSend("Test" , "", "MsoCommandBar2", "^c") Sleep (100) ;in notepad $FileName = "intra" & @HOUR & @MIN & ".txt" ; Another sample which automatically creates the directory structure $file = FileOpen($FileName, 10) ; which is similar to 2 + 8 (erase + create dir) Sleep (200) FileWrite($file, ClipGet()) Sleep(2000) FileClose($file) Until @HOUR = 15 and @MIN = 5 WinSetState("Test", "", @SW_SHOW) _ExcelBookClose($oExcel, 1, 0) Share this post Link to post Share on other sites
rcmaehl 50 Posted August 21, 2011 (edited) Try replacing: WinSetState("Test", "", @SW_HIDE) With: WinMove("Test", "", @DesktopWidth+10, @DesktopHeight+10) Or Try replacing 'ControlSend' with 'ControlCommand' with updated Parameters.] I'm not sure how else to do that, but I'll try experimentation with a few scripts to see if I can figure out a better way. Edited August 21, 2011 by rcmaehl My UDFs are generally for me. If they aren't updated for a while, it means I'm not using them myself. As soon as I start using them again, they'll get updated.My ProjectsCisco Finesse, Github, IRC UDF, WindowEx UDF Share this post Link to post Share on other sites
Xanadin 0 Posted August 22, 2011 Try replacing: WinSetState("Test", "", @SW_HIDE) With: WinMove("Test", "", @DesktopWidth+10, @DesktopHeight+10) Or Try replacing 'ControlSend' with 'ControlCommand' with updated Parameters.] I'm not sure how else to do that, but I'll try experimentation with a few scripts to see if I can figure out a better way. Thanks a lot for your reply. I solved the problem by activating the auto refresh feature of Excel. I did not want to do this because there are about 200 web addresses in my Excel file that were needed to fix manually. But, hey!, there is Autoit for it! I changed the parameters of all 200 web addresses by another Autoit script! However, it would be great to learn how to make Excel work in the hide mode. Again, thanks a lot for your time. Share this post Link to post Share on other sites
Juvigy 49 Posted August 22, 2011 (edited) Try it like this: $oExcel = ObjCreate("Excel.Application") $oExcel.Visible = 0 msgbox(0,"","Excel is Hidden") $oExcel.WorkBooks.Open($FilePath) $oExcel.ActiveWorkbook.RefreshAll $oExcel.Visible = 1 msgbox(0,"","Excel is Visible and refreshed") Edited August 22, 2011 by Juvigy Share this post Link to post Share on other sites
water 2,392 Posted August 22, 2011 Please check the help file for _ExcelBookOpen. The second parameters defines whether to show or hide the workbook. In your original post you mixed _Excel* functions - which use the COM interface - and send commands to access the Excel menus. This is a bad idea because you can get a lot of problems. Try to use the _Excel* functions where possible and if they don't offer what you need check the Excel COM reference on MSDN or ask a question here. This will lead to stable and reliable scripts. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - WikiTask Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - WikiTutorials:ADO - Wiki, WebDriver - Wiki Share this post Link to post Share on other sites