Closing all MDI child forms when Ctrl-F4 not available

Ronnie1001

New member
Local time
Today, 07:00
Joined
Mar 28, 2025
Messages
2
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?
 
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.
 
Last edited:
For #1, try using Ctrl+W

Hope that helps...
 
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.
 
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.

Thanks for taking the time to answer. But my problem as stated in the first line of my question remains: it's a 20+ year-old black-box database app. Your suggestions would be excellent if we had any access to any of the design or the source code it runs, but we don't.

However, I hadn't thought of DoCmd.Close and I shall try it tomorrow.

The black-box database app was written by a 3rd-Party supplier (whose website is now inactive), with expensive 3rd-level-only support from another 3rd-party company. My company is spending big budget to eliminate it but that process won't be complete until the end of 2026.

So for the quarter-ends until the replacement goes online some mid-level analysts (who are the few who have justified the expensive licenses to use it) will have to spend multiple working days putting an account code in, clicking Start and waiting an unknown delay until the black-box program emits an untitled Excel (which is not in the same Task Manager process as any previously loaded Excel, so doesn't show in any Windows or Workbooks collection), saving it and repeating for all the hundreds of other account codes, unless I can find a way of automating it.

Back in the day, there were automated testing tools like Rational SQA Teamtest/Robot that would solve my problem in moments, but these are no longer available.
 
Thanks for taking the time to answer. But my problem as stated in the first line of my question remains: it's a 20+ year-old black-box database app. Your suggestions would be excellent if we had any access to any of the design or the source code it runs, but we don't.

However, I hadn't thought of DoCmd.Close and I shall try it tomorrow.

The black-box database app was written by a 3rd-Party supplier (whose website is now inactive), with expensive 3rd-level-only support from another 3rd-party company. My company is spending big budget to eliminate it but that process won't be complete until the end of 2026.

So for the quarter-ends until the replacement goes online some mid-level analysts (who are the few who have justified the expensive licenses to use it) will have to spend multiple working days putting an account code in, clicking Start and waiting an unknown delay until the black-box program emits an untitled Excel (which is not in the same Task Manager process as any previously loaded Excel, so doesn't show in any Windows or Workbooks collection), saving it and repeating for all the hundreds of other account codes, unless I can find a way of automating it.

Back in the day, there were automated testing tools like Rational SQA Teamtest/Robot that would solve my problem in moments, but these are no longer available.
Perhaps it's a long shot, but maybe Desktop Power Automate could be useful in repeatedly performing the required steps. I am aware that at least some part of the process is selecting/entering an account code, so that would complicate it.

 
Your suggestions would be excellent if we had any access to any of the design or the source code it runs, but we don't.
You have to have an unsecured .mdb or .accdb if you expect to change any code. Sounds like you don't have access to the underlying code so your only option is to externally automate the application.

It is easy enough to use OLE to automate Access by creating a separate Access application to do it but I've never tried to do it blind so to speak. The underlying application was intended to work by interacting with a different Access app. If you have anyone who can attempt this, give it a go. Otherwise, George's solution could work for you.

The logic for the automation is simple enough but you have to navigate blind to the correct form and be able to anticipate prompts and respond to them. It's a little like playing pin the tail on the donkey. But, I think you have the exact same problem whether you try to use Power Automate or Access itself unless Power Automate is smarter than I think. At a minimum, you need to be create a query that retrieves all the codes for the exports you want to produce. I don't know if Power Automate can do that. If it can't, you may be able to use another tool to create an excel workbook by exporting the list directly from the Oracle BE and hopefully Power Automate is smart enough to use that to drive the automation.
 
Last edited:

Users who are viewing this thread

Back
Top Bottom