Sign in to follow this  
Followers 0
Xanadin

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

5 posts in this topic

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:

#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



#2 ·  Posted (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 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 PROJECTS


Active: IRC UDF, WindowEx UDF
Discontinued: GithubBubbleSort UDF

Share this post


Link to post
Share on other sites

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

#4 ·  Posted (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 by Juvigy

Share this post


Link to post
Share on other sites

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0