Closing all MDI child forms when Ctrl-F4 not available

[Ronnie1001]

I have a horrible task of automating a 20+ year-old "black-box" app that looks like it was written in Access97 and/or VB6, that has an Oracle back-end.

There are over 120 codes to process. You have to enter them one at a time then click a button and it goes away and creates an unnamed "Book1" Excel 365 report, taking anything between 1 and 10 minutes to complete. You then have to save the workbook with the code as the file name and repeat for all the other codes.

We have attempted to use Toad SQL monitoring to see what SQL is being queried so that we could eliminate the front end, but there is nothing like enough information returned.

So we are having to use AppActivate and SendKeys as a last resort.

We have got a long way towards achieving it, but I have 3 problems remaining, the first is appropriate for here and the others I will ask too in case someone has any ideas.

1. After each code has been processed, I want to close the 2 "Multiple Document Interface" child forms in the app so that I can start the next code from fresh. Unfortunately, the app writers assigned "Ctrl-F4" as a shortcut to open another child form instead of leaving the standard Windows MDI usage of closing the active child subform. How can I close the MDI child forms without using "Ctrl-F4"? "Alt-F4" tries to close the whole app.

2. While the unnamed "Book1" Excel report is being produced by the app I need to go round a delay loop for an unspecified time until it does appear and can then be saved with its code as the name and then closed. But it is produced in a new instance of Excel by the app (a separate line in Task Manager), so it doesn't appear in any Windows or Workbooks collections in the VBA that I am running SendKeys from. So I thought I could use "On Error Goto ..." and AppActivate within a 5-second delay loop, so that it loops indefinitely and only escapes the loop when the "Book1" appears, but there seems to be a bug in VBA that AppActivate errors are not trappable, so it Breaks and stops instead of looping, in spite of there being an active "On Error Goto ...". Any ideas how I can implement the delay?

3. When saving the file, I should be able to use the key sequence "Alt-F", "A", "O" to get to the Excel "Save As" dialog, but there seems to be a bug in Excel 365 that "Alt-F" doesn't complete showing the file menu after SendKeys "%F", unless you physically click on Excel with a mouse after doing it. So I thought I could simply do SendKeys "%{F4}" to close it, followed by SendKeys "%S" to "Save As" when it asks if I want to save before closing - but again, it seems to pause indefinitely until you physically click on it with a mouse. Any ideas?

 

[Pat Hartman]

It would be best if you posted your code. But, I think there are probably better methods for automating this. You should be using TransferSpreadsheet to create the exports rather than OLE automation. This also allows you to name the workbooks BEFORE you export them so they get saved with the correct name in the correct folder automatically. Even using OLE, you don't need to bring up the save dialog. Your code can construct the file name and just save the workbook.

Do not use macros. Do not use send keys. Use VBA. We'll help you to create the code loop to simplify the process. You can also use DoCmd.Close to close a specific form by name.

This probably seems like a hard task if you are unfamiliar with Access, but it is actually pretty easy when you understand how to use a VBA loop and built in Access methods rather than OLE automation.

If you can post the whole db, that would be best. Remove any sensitive data first if you need to.

 

[theDBguy]

For #1, try using Ctrl+W

Hope that helps...

 

[CJ_London]

looks like it was written in Access97 and/or VB6,

What is the file extension? if .mdb or .mde it should be possible to view tables and queries - and with .mdb the forms and code as well (assuming you have a full version of access available, not the runtime version which I don't think existed for '97). You could try holding down the shift key before opening the file.