Convertir macros de Access a VBA

VBA

Notas

SendKeys se utiliza para evitar tener que confirmar este cuadro de diálogo:

y este mensaje (excepto el primero, y quizás algunos más):

Y debido al uso de SendKeys, el cuadro de mensaje no se muestra al final. Presione Ctrl-G para abrir la ventana de depuración y ver qué se hizo. No es una solución perfecta, pero lo ayudará a ver mejor qué está sucediendo.

Cuando se realiza la conversión de macros a VBA,
compilar
VBA. Kim Young me dijo que las macros de datos en realidad no se eliminan cuando las «eliminas»; no estoy seguro de si esto se aplica a las macros incrustadas o no. Después de ejecutar este código, es posible que aparezca un error como este:

Comente el procedimiento adicional para que pueda examinarlo más tarde si lo desea y compile hasta que todo esté bien.

Módulo estándar

'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: 
' 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 *****************************************************

El código se generó con colores utilizando el complemento gratuito Color Code para Access