Sign in to follow this  
Followers 0
edl

Tricky Issue with Excel

18 posts in this topic

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
 

Share this post


Link to post
Share on other sites



Welcome to AutoIt and the forum!

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


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

#4 ·  Posted (edited)

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 by edl

Share this post


Link to post
Share on other sites

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

Can you post the script you use?


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
#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

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

_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 by water

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

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?   

Share this post


Link to post
Share on other sites

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


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

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

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


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

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.

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

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 by DicatoroftheUSA

Share this post


Link to post
Share on other sites

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


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

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. ;)

Share this post


Link to post
Share on other sites

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.


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

#17 ·  Posted (edited)

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 by jdelaney

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

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

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