Jump to content
Sign in to follow this  
guackyxxx

Excel Status Bar

Recommended Posts

Does anyone know whether the content of the Excel Status Bar can be tested? By the status bar, I mean the line at the bootm of the Excel screen that display messages such as "Ready", or "Opening (filename)". Its contents do not appear in Window Spy anywhere (as far as I can tell).

My purpose in looking for the information in the status bar is in order to be able to tell when Excel has finished opening or saving a file, or when it is done with loading an Add-In or similar. I am trying to test for that "Ready" condition.

Any thoughts?

Share this post


Link to post
Share on other sites

Have you tried using COM with the Excel property:

Application.Ready

Take a look in the Excel VBA help. This may be what you need.


“Give a man a script; you have helped him for today. Teach a man to script; and you will not have to hear him whine for help.”AutoIt4UE - Custom AutoIt toolbar and wordfile for UltraEdit/UEStudio users.AutoIt Graphical Debugger - A graphical debugger for AutoIt.SimMetrics COM Wrapper - Calculate string similarity.

Share this post


Link to post
Share on other sites

Here is some code to set cell borders. You could hack it to check the $MyObject.Ready property.

$MyObject = ObjCreate("Excel.Application"); Create an Excel Object
If @error Then
    MsgBox(0, "Excel Error:", "Error creating Excel object!")
    Exit
EndIf
If Not IsObj($MyObject) Then
    MsgBox(16, "Excel Error:", "Error creating Excel object!")
    Exit
EndIf

$MyObject.Visible = 1
$MyObject.workbooks.add

$MyObject.Sheets("Sheet1").Select

; xlLineStyle Constants
$xlContinuous = 1
$xlDash = -4115
$xlDashDot = 4
$xlDashDotDot = 5
$xlDot = -4118
$xlDouble = -4119
$xlLineStyleNone = -4142
$xlSlantDashDot = 13

; XlBordersIndex Constants
$xlDiagonalDown = 5
$xlDiagonalUp = 6
$xlEdgeBottom = 9
$xlEdgeLeft = 7
$xlEdgeRight = 10
$xlEdgeTop = 8
$xlInsideHorizontal = 12
$xlInsideVertical = 11

With $MyObject.Sheets("Sheet1")
    .Range("b6:c7").Borders.LineStyle = $xlDouble
    
    .range("b2:c4").Borders($xlEdgeBottom).LineStyle = $xlDash
    .range("b2:c4").Borders($xlEdgetop).LineStyle = $xlDash
    .range("b2:c4").Borders($xlEdgeright).LineStyle = $xlDash
    .range("b2:c4").Borders($xlEdgeLeft).LineStyle = $xlDash
endwith

$MyObject = 0

“Give a man a script; you have helped him for today. Teach a man to script; and you will not have to hear him whine for help.”AutoIt4UE - Custom AutoIt toolbar and wordfile for UltraEdit/UEStudio users.AutoIt Graphical Debugger - A graphical debugger for AutoIt.SimMetrics COM Wrapper - Calculate string similarity.

Share this post


Link to post
Share on other sites

As I said I'm new to this COM-functionality so please bare with me :">

I get an error on the first line :

$MyObject = ObjCreate("Excel.Application"); Create an Excel Object

Error : Unknown function name

Do I have to use an include to be able to use these COM functions ?

I'm using version v3.1.1 from 7th Apr, 2005

Share this post


Link to post
Share on other sites

Here's a possible syntax for "Ready", [and I've added it to my UDF tonight - you don't need it for this script except exit]; no error, but I can't actually tell if it's working?

#include<ExcelCom.au3> ; only needed herer for Exit of Excel

; none of these shorter commands are visible until you say "_XLshow"

dim $FilePath,$var,$LastRow

$FilePath="c:\winword\Excel\Book1.xls"

If Not FileExists($FilePath) Or Not StringInStr($FilePath, "xls") Then

$FilePath = FileOpenDialog("Go - Choose your excel file as inbuilt one not exists", $FilePath, "Worksheet" & " (" & "*.xls" & ")", 1)

EndIf

$oExcel = ObjGet ($FilePath) ; Get an Excel Object from an existing filename

$Sheet=1

If IsObj ($oExcel) Then

If $Sheet > $oExcel.Worksheets.count Then $Sheet = $oExcel.Worksheets.count

with $oExcel

  .Windows (1).Visible = 1; Set the first worksheet in the workbook visible

  .Worksheets ($Sheet).Activate

  .ActiveSheet.Visible = 1

  .Application.Visible = 1

  $var=.Application.Ready

EndWith

Else

MsgBox(0, "Excel File Test", "Error: Could not open " & $sFilePath & " as an Excel Object.")

EndIf

msgbox (0,"A1=",$var)

_XLexit($FilePath); Save changes and exit Excel

Best, Randall

[EDIT] see Stumpy's next post; ? needs $var=.Application.Ready, I guess you'd have tro test a large file load directly from Autoit, not COM, and run XLready, in a loop?

[EDIT] - not returning anything to read at present?.......probably wrong syntax

Edited by randallc

Share this post


Link to post
Share on other sites

Here's a possible syntax for "Ready", [and I've added it to my UDF tonight - you don't need it for this script except exit]; no error, but I can't actually tell if it's working?

Best, Randall

<{POST_SNAPBACK}>

Note that the '.Ready' property is read only. It returns the current status or Excel, it does not set it.

To test this, one could try loading a really big Excel file while monitoring the .Ready property. It maybe that Excel functions wait before returning to the script anyway, meaning that this check would not be required. I think that this would be the case. Happy testing!

Edited by Stumpii

“Give a man a script; you have helped him for today. Teach a man to script; and you will not have to hear him whine for help.”AutoIt4UE - Custom AutoIt toolbar and wordfile for UltraEdit/UEStudio users.AutoIt Graphical Debugger - A graphical debugger for AutoIt.SimMetrics COM Wrapper - Calculate string similarity.

Share this post


Link to post
Share on other sites

Thanks to everyones input I have been able to cook up this working test script :whistle:

WinWait("Microsoft Excel");Excel is being launched elsewhere
$oExcel = ObjGet("","Excel.Application");Get the excel object
if @error then 
  Msgbox (0,"ExcelTest","Error Getting an active Excel Object. Error code: " & hex(@error,8))
  exit
endif
ExcelReady()
MsgBox(0,"Status","Excel is now ready !")

Func ExcelReady()
    If $oExcel.Ready = False Then
        Sleep(1000)
        ExcelReady()
    EndIf
    Return
EndFunc

Thanks all !

Share this post


Link to post
Share on other sites

Here I am ... bumping again B)

The PC where this is running on got a service pack update.

Now it is running on Office 2003 SP2 ... and the objget is failing:

"Error getting an active Excel object. Error Code: 800401E3"

I googled for the error code but I didn't get much wiser.

Anyone else having a problem with ObjGet ? Or an idea on how to resolve this ?

Share this post


Link to post
Share on other sites

Here I am ... bumping again B)

The PC where this is running on got a service pack update.

Now it is running on Office 2003 SP2 ... and the objget is failing:

"Error getting an active Excel object. Error Code: 800401E3"

I googled for the error code but I didn't get much wiser.

Anyone else having a problem with ObjGet ? Or an idea on how to resolve this ?

My first thoughts would be that the Excel Object model has changed, or the reference to Excel needs to be changed. For example, check that the reference to create Excel is not ObjGet("","Excel11.Application") or similar. Also security may be an issue, the later versions of Office apps are more security conscious. Check that macro are enabled etc.

Edited by Stumpii

“Give a man a script; you have helped him for today. Teach a man to script; and you will not have to hear him whine for help.”AutoIt4UE - Custom AutoIt toolbar and wordfile for UltraEdit/UEStudio users.AutoIt Graphical Debugger - A graphical debugger for AutoIt.SimMetrics COM Wrapper - Calculate string similarity.

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...