ankit.tayal560 has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

I've an excel file and i want to save my excel file and do other operations on that file through my code.

I am using Win32::GuiTest module for automation

use strict;
use warnings;
use Win32::GuiTest qw(:ALL);

my @window=FindWindowLike(undef,"Book1.xlsx - Excel");
sleep 2;
SetForegroundWindow($window[0]);
sleep 2;
MenuSelect("&File|&Save");
sleep 2;

[download]

but this code is not saving a existing excel file named "book1.xlsx"

Any suggestions or solutions to how to automatically do that from my script

P.S. saving an excel file is just taken as an example I need to other menu options through my script as well

I am not getting ny errors but it is not performing the desired task

any help is appreciated

Replies are listed 'Best First'.
Re: How to use menu options
by marto (Cardinal) on Nov 10, 2016 at 09:48 UTC

    I don't think your example does what you think it's doing. You don't check @window, I believe this will be undef, so the remainder or your code won't work as expected. You need to start error checking and basic debugging. Consider the following, your call to FindWindowLike is wrong. Assuming things work isn't wise.

    use strict;
use warnings;
use Win32::GuiTest qw(:ALL);

my @window = FindWindowLike(undef, "Excel");

# die if Excel can't be found

if (not @window) {
   die "Could not find Excel\n";
}

# do stuff here

    [download]

    Corion is wise to suggest sending hotkeys, since you seem to want to persist on driving things via a menu I'll once again point you to the module documentation which explains this and has example code for dumping menus Win32::GuiTest/Win32::GuiTest::Examples. Also Tutorials/Debugging and Optimization. You could launch excel and open a file (you've been given lots of help on starting apps previously).

    Update: slight rewording for clarity.

[reply]
[d/l]
[select]
Re: How to use menu options
by Corion (Patriarch) on Nov 10, 2016 at 09:03 UTC

    Instead of navigating the menus, I recommend using the hotkeys. In the case of Microsoft Excel, that would be CTRL-S (or in Win32::GuiTest speak, sendkeys(^S)) for saving the file.

[reply]
[d/l]
[select]

      Okay, but that's why I wrote that saving a file is just taken as an example there can be certain functions which do not have shortcut keys.

      i'll have to navigate the menu only in that case that's why wanted to approach in that way

[reply]

        I recommend sending keys instead of the MenuSelect function, especially if the MenuSelect function does not work for you.

        In your example case, this would mean sending %FS for the sequence ALT-F S to select the File menu and then Save.

[reply]
[d/l]
[select]
Re: How to use menu options
by pryrt (Abbot) on Nov 10, 2016 at 14:59 UTC

    Which version of Excel do you have? I think it might be that the modern "Ribbon" interface isn't a menu in the same sense that GuiTest expects. Using the advice that Corion and marto gave, I was able to use SendKeys('%FS') to perform a save, but not the MenuSelect with either '&File|&Save' or 'File|Save'.

    Also, regarding FindWindowLike(), I was able to match windows with 'Excel' and 'Book1', but not with the patterns including the hyphen:

    use strict;
use warnings;
use Win32::GuiTest qw(:ALL);

foreach my $like ( 'Excel', 'Book1 - Excel', 'Book1.xslx - Excel', 'Bo
+ok1') {
    my @window = FindWindowLike(undef, $like);
    unless (@window) {
        warn "Couldn't find '$like'\n";
        next;
    }
    print $like, @window;
    foreach (@window) {
        SetForegroundWindow( $_ );
        SendKeys(join '{TAB}', "Hello World", $like, $_, GetWindowText
+($_), "{ENTER}");
        #MenuSelect("&File|&Save");
        #MenuSelect("File|Save");
        SendKeys('%FS');
    }
}
__END__

__OUTPUT__
Excel    1903500    6817874    1580572
Couldn't find 'Book1 - Excel'
Couldn't find 'Book1.xslx - Excel'
Book1    1903500    4919696

    [download]
[reply]
[d/l]
[select]
Re: How to use menu options
by Anonymous Monk on Nov 11, 2016 at 03:13 UTC

    Hi,

    Are you not able to use Win32::OLE? It does all the things you want with commands, no menus or sendkeys at all. it's not hard to use either, even I can do it!

    J.C.

[reply]

Back to Seekers of Perl Wisdom