Tricky Issue with Excel

[edl]

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

 

[water]

Welcome to AutoIt and the forum!

Don't automate the GUI - use the Excel UDF that comes with AutoIt.

[Bert]

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"])

[edl]

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

[water]

Automating the GUI (pressing a button) is never as reliable as using the Excel UDF.

Can you post the script you use?

[edl]

#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

[Bert]

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.

[water]

_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

[edl]

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?   

[JLogan3o13]

Why use the vbscript at all? Water shows you how to open the excel file in post #8.

[water]

I wouldn't mix VBScript and AutoIt in your case.

Either tell VBScript to suppress the pop ups or do everything in AutoIt.

[edl]

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.

[DicatoroftheUSA]

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

[water]

Do you know how the VBScript automates Excel? Using COM or by automating the GUI?

[edl]

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.

[water]

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.

[jdelaney]

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

[edl]

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