Sign in to follow this  
Followers 0
dymodcc

Closing multiple excel files

2 posts in this topic

Mornin.

I need to write a script to close alot of excel files, and based off a list decide wether to save or not save each file.

i tried out a few metheods, and finally came to the final one which i am testing now, but I am hopefully maybe someone will see this and have a better idea of how to do this.

Opt("WinTitleMatchMode", 3)
$snooze = "250"
$file = FileOpen("log.txt", 1)
If $file = -1 Then
    MsgBox(0, "Error", "Unable to open log file.")
    Exit
EndIf
$vart = IniReadSection("excel.ini", "dontsave")
If @error Then 
    MsgBox(4096, "", "Error occurred, probably no INI file.")
EndIf
Sleep($snooze)
    For $i = 1 To $vart[0][0]
        If WinExists( $vart[$i][1]) Then
            WinActivate( $vart[$i][1], "")
            Sleep($snooze)
            Send("{ESC}")
            Sleep($snooze)
            Send("{CTRLdown}")
            Send("w")
            Send("{ctrlup}")
            Sleep($snooze)
            Send("n")
            FileWrite($file, "notsaved " & $vart[$i][1] & @CRLF)
        EndIf
    Next

$var = IniReadSection("excel.ini", "Save")
If @error Then 
    MsgBox(4096, "", "Error occurred, probably no INI file.")
    EndIf
Sleep($snooze)
    For $i = 1 To $var[0][0]
        If WinExists( $var[$i][1]) Then
            WinActivate( $var[$i][1], "")
            Sleep($snooze)
            Send("{ESC}")
            Sleep($snooze)
            Send("{CTRLdown}")
            Send("w")
            Send("{ctrlup}")
            Sleep($snooze)
            Send("j")
            FileWrite($file, "saved " & $var[$i][1] & @CRLF)
            Sleep($snooze)
        EndIf
    Next
    FileClose($file)
    MsgBox(0, "Fertig", "Script is done.")
Exit

oÝ÷ Ú«z+-è§ø¥z,+¢×°Øméb²Ú~)^²)íÂrبÊ'yú+ljø§©Ý¢w¢»¾)àjëh×6
[dontsave]
1=K.xls
2=K015.xls
3=K020.xls
4=K021.xls
5=K022.xls
6=K023.xls
7=K044.xls
8=K054.xls
9=K905.xls
[Save]
1=K013D.xls
2=K015D.xls
3=K020D.xls
4=K021D.xls
5=K022D.xls
6=K023D.xls
7=K044D.xls
8=K054D.xls
9=P.xls

They need to be closed in the same order listed in the ini.

My understanding of the way i wrote this is, it should work like so:

set the windows title match code to strict matching.

opens the log file log.txt or errors out

opens the ini and reads the "dontsave" section ( or errors out if no ini is found)

1 parses thru each value in the dontsave section of the ini

2 searching for a window with the matching ini value (K.xls)

3 simulates the "esc" key to get out of any table field it might be in, and then does ctrl-W to close the worksheet, and simulates N to not save

4 writes to the log.txt notsave and the ini value

5 rinse and repeats for each value line in the ini in the dont save section

does the same thing again except it uses the "save" section, and sends the J ( german Yes) to

save the file instead.

then closes the log file and pops up a box saying the script is done.

the full ini has 50 values in the save and 50 in the dontsave section, I have tested it with some sample files 5-6 on each side. however It seems to have a problem.

here is my sample testing setup.

8 excel files

K.xls

P.xls

K015.xls

K020.xls

K912.xls

K015D.xls

K020D.xls

K912D.xls

and here is the log file

notsaved K.xls

notsaved K015.xls

notsaved K020.xls

notsaved K912.xls

saved K015D.xls

saved P.xls

however the K.xls and the K015.xls are still open

any tips/hints would be greatly appreciated.

Share this post


Link to post
Share on other sites



Have you run the script while watching what is happenning on the screen?

I can imagine right off that your "Sleep($snooze)" might be unreliable as the fixed duration of $snooze may not always work.

Better to eliminate sleeps wherever you can. Looks like they're there because you're waiting fot the window to become active before sending the keystrokes. Might want to investigate WinWaitActive().

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