VBA
Notes
SendKeys is used to avoid having to confirm this dialog box:
and this message (except the first one, and maybe a few more):
and because of using SendKeys, the MsgBox at the end doesn't show.
Press Ctrl-G to open the Debug Window and see what was done.
It's not a perfect solution, but will help you see better what's going on.
When the conversion from macros to VBA is done,
compile
the VBA.
Kim Young
told me that Data Macros
aren't actually deleted when you 'delete' them --
not sure if this extends to embedded macros or not.
After running this code,
you might get an error such as this:
Comment the extra procedure so you can examine it later if desired,
and compile until all is ok.
Standard module
'module: mod_ConvertMacrosToVBA_s4p
'*************** Code Start ***********************************************
' Purpose : Convert Macros To VBA
' forms, reports, stand-alone macros
' Author : crystal (strive4peace)
' Code List: www.msaccessgurus.com/code.htm
' This code: https://msaccessgurus.com/VBA/ConvertMacrosToVBA.htm
' LICENSE :
' You may freely use and share this code, but not sell it.
' Keep attribution. Mark your changes. Use at your own risk.
'-------------------------------------------------------------------------------
' ConvertMacrosToVBA_s4p
'-------------------------------------------------------------------------------
Public Sub ConvertMacrosToVBA_s4p()
' uses acCmdConvertMacrosToVisualBasic
'160725 strive4peace, 170529, 230509
'while this is running, you may need to press ENTER for it to continue
' although macros are converted to VBA,
' you will have to manually fix places where macros are called
' Close all open objects before running
'CLICK HERE
' PRESS F5 to Run!
'set up error handler
On Error GoTo Proc_Err
Dim db As DAO.Database _
,Cnt As DAO.Container _
,Doc As DAO.Document _
,obj As Object
Dim sDocument As String _
,iNumForms As Integer _
,iNumReports As Integer _
,iNumMacros As Integer _
,sMsg As String
'set an object variable to the current database
Set db = CurrentDb
sMsg = "Before running this code, backup the database, " _
& "and close all open objects." _
& vbCrLf & "Open the Debug window when done to see a list of " _
& " forms, reports, and stand-alone macros"
If MsgBox(sMsg,vbYesNo + vbDefaultButton2 _
, "Convert Macros?") <> vbYes Then
Exit Sub
End If
' 'close all open forms
' Call CloseAllForms
' 'close all open reports
' Call CloseAllReports
iNumForms = 0
iNumReports = 0
iNumMacros = 0
'convert macros for Forms
'set an object variable to the forms container
Set Cnt = db.Containers( "Forms")
Debug.Print "-------------- Forms --------------"
For Each Doc In Cnt.Documents
'get the name of the form
sDocument = Doc.Name
'print name to debug (Immediate) window
Debug.Print sDocument
'open in design view
DoCmd.OpenForm sDocument,acDesign
'acknowledge msgbox of what to convert so user is not prompted
' Add error handling
' include comments
'False: don't wait to process keystroke -- go to next statement
SendKeys "{ENTER}",False
'convert macros to vba
DoCmd.RunCommand acCmdConvertMacrosToVisualBasic
'acknowledge "conversion Finished message"
SendKeys "{ENTER}",False
'close form and save
DoCmd.Close acForm,sDocument,acSaveYes
iNumForms = iNumForms + 1
Next Doc
'convert macros for Reports
Set Cnt = db.Containers( "Reports")
Debug.Print "-------------- Reports --------------"
For Each Doc In Cnt.Documents
'get the name of the Report
sDocument = Doc.Name
'print name to debug (Immediate) window
Debug.Print sDocument
'open in design view
DoCmd.OpenReport sDocument,acDesign
SendKeys "{ENTER}",False
DoCmd.RunCommand acCmdConvertMacrosToVisualBasic
SendKeys "{ENTER}",False
'close Report and save
DoCmd.Close acReport,sDocument,acSaveYes
iNumReports = iNumReports + 1
Next Doc
'convert macros for stand along Macros
Debug.Print "-------------- Macros --------------"
For Each obj In CurrentProject.AllMacros
iNumMacros = iNumMacros + 1
Debug.Print obj.Name
DoCmd.SelectObject acMacro,obj.Name,True
SendKeys "{ENTER}",False
DoCmd.RunCommand acCmdConvertMacrosToVisualBasic
SendKeys "{ENTER}",False
Next obj
sMsg = "*** Converted macros for " & iNumForms & " forms, " _
& iNumReports & " reports, and " _
& iNumMacros & " stand-alone macros "
Debug.Print sMsg
'I think this sends an extra ENTER at the end
'… so MsgBox doesn't show - could stop that by counting and comparing
MsgBox sMsg,, "Done Converting Macros"
Proc_Exit:
On Error Resume Next
'release object variables
Set obj = Nothing
Set Doc = Nothing
Set Cnt = Nothing
Set db = Nothing
Exit Sub
Proc_Err:
MsgBox Err.Description,,_
"ERROR " & Err.Number _
& " ConvertMacrosToVBA_s4p "
Resume Proc_Exit
' Ctrl-Break the MsgBox... then Debug
' then set Resume to be the next statement by right-clicking on it
' and choosing Set Next Statement from the shortcut menu
' then press F8 to step through code one line at a time to see what is wrong
Resume
End Sub
'*************** Code End *****************************************************
Code was generated with colors using the free
Color Code add-in for Access
