Converting Macro to VBA using VBA (and SendKeys issue)

[germanpearls]

My goal is to convert a macro to VBA using VBA. This program will be used on other databases and if the user has an existing autoexec macro I want to convert it to a module/function in VBA programmatically.

Here are some comments on things I've investigated and tried, to no avail:

Method #1
When editing a macro there is a button to convert it to VBA. I tried using the below code to utilize this function but a dialog box opens which needs intervention to continue so the program hangs at this point.

Application.RunCommand acCmdConvertMacrosToVisualBasic

Method #2
I tried using SendKeys to avoid the problem in #1. (I know SendKeys aren't optimal but I couldn't find another way to get this done.) The program hangs so we can't use SendKeys just to work with the dialog, we have to use SendKeys to activate the command as well. In addition, the command is only available once the macro is opened in EDIT mode, which I can't find a VBA code for either. So, I'm forced to use SendKeys to open the macro in edit mode and then SendKeys again to run the conversion command. It looks something like this:

DoCmd.SelectObject acMacro, macroName, True
DoEvents
'open the macro in design mode
SendKeys "^({ENTER})", True
DoEvents
'below works in tests of this function on its own but not when running full setup routine
SendKeys "%JMV%(C){ENTER}", True
DoEvents
DoCmd.Close acMacro, macroName, acSaveNo

The above code works if I run it as a small sub. Note I had to include the DoEvents after each step to get it to run successfully.

However, once I run that code as part of a larger routine, it doesn't work. It seems like the routine is going to fast and the SendKeys aren't keeping up? It appears as if the macro isn't even opening in edit mode until the code breaks on error.

I tried various methods of adding delays, waits, doevents and nothing has worked. I also suspected maybe the window lost focus due to other parts of the routine but I added some bogus table opening and closing to ensure the database was in focus and that didn't resolve the issue.

Hoping anyone can provide a better solution for this.


I'm open to any help whether be:
- an entirely different approach, or
- help as to why my SendKeys aren't working when in a larger routine

Thank you in advance.

[wvmitchell]

SendKeys can be funny sometimes. Have you tried sending the keys _before_ opening the macro?

[germanpearls]

Thank you @wvmitchell

I tried many different methods and was unable to get SendKeys to work reliably when part of a longer routine.

I decided to approach this from an entirely different direction which didn't require the use of SendKeys at all.

Closing this thread.