"Refresh All" is not working in Excel in Hide mode

[Xanadin]

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)

[rcmaehl]

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

[Xanadin]

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.

[Juvigy]

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

[water]

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.