locked
How to create a macro or module using vba script? RRS feed

  • Question

  • Hi:
        I am going to write a tool using vba which can check a vba code that was converted from a MS-Access macro.But there is not any available API to get the final code in Object "CurrentProject.AllMacros('macro-name')" or "CurrentProject.AllModules('converted-module-name')".

        How to create a macro by vba script?
    Saturday, November 28, 2020 12:50 PM

All replies

  • Indeed the support is a bit lacking. You can export a macro to a text file using the hidden method Application.SaveAsText.

    The modules can be accessed if you add a reference to "Microsoft Visual Basic for Application Extensibility 5.3"

    Then you can use Access.Module and its properties and methods, including InsertLines.


    -Tom. Microsoft Access MVP

    Sunday, November 29, 2020 4:19 PM
  • How to create a macro by vba script?

    You can't. The underlying structure of a macro makes that impossible.

    However you can create VBA procedures for modules as they are just text.

    In addition VBA code is MUCH more powerful than macros so if you want to use code to do this, that is definitely the way to go

    Sunday, November 29, 2020 8:09 PM
  • You *can*.

    It requires creating a text file in the correct format, and Application.LoadFromText to import it. I have done this myself.


    -Tom. Microsoft Access MVP

    Sunday, November 29, 2020 11:08 PM
  • It requires creating a text file in the correct format, and Application.LoadFromText to import it.

    What could be a reason to work in this way?

    Imb.

    Monday, November 30, 2020 8:03 AM
  • You *can*.

    It requires creating a text file in the correct format, and Application.LoadFromText to import it. I have done this myself.


    -Tom. Microsoft Access MVP

    I am well aware of that approach and have also done so myself. An obvious reason for doing so could to transfer an autokeys macro to another database.

    However, to do that you need to have created a macro 'normally' in the first place.

    In my opinion it is unrealistic to try and create a text file for a macro from 'scratch'.

    The following is a very simple autokeys macro saved as text. I wouldn't attempt to write that 

    Version =196611
    PublishOption =1
    ColumnsShown =0
    Begin
    End
    Begin
        MacroName ="{F11}"
        Condition ="GetWorkstation()<>\"COLIN-PC\""
        Comment ="Disable F11 to prevent access to nav pane"
    End
    Begin
        MacroName ="^+Z"
        Action ="OpenForm"
        Comment ="Open frmFormUnresizer"
        Argument ="frmFormUnResizer"
        Argument ="0"
        Argument =""
        Argument =""
        Argument ="-1"
        Argument ="0"
    End
    Begin
        MacroName ="^H"
        Action ="RunCode"
        Comment ="Hide Navigation Pane"
        Argument ="HideNavigationPane()"
    End
    Begin
        MacroName ="^+H"
        Action ="RunCode"
        Comment ="Show navigation pane"
        Argument ="ShowNavigationPane()"
    End
    Begin
        Action ="RunCode"
        Argument ="MinimizeNavigationPane()"
    End
    Begin
        MacroName ="{F12}"
        Action ="CancelEvent"
        Comment ="Disable SaveAs using F12 key"
    End
    Begin
        Comment ="_AXL:<?xml version=\"1.0\" encoding=\"UTF-16\" standalone=\"no\"?>\015\012<UserI"
            "nterfaceMacro MinimumClientDesignVersion=\"14.0.0000.0000\" xmlns=\"http://schem"
            "as.microsoft.com/office/accessservices/2009/11/application\"><Statements><Commen"
            "t></Comment></Statements><Sub"
    End
    Begin
        Comment ="_AXL: Name=\"{F11}\"><Statements><ConditionalBlock><If Collapsed=\"true\"><Condi"
            "tion>GetWorkstation()&lt;&gt;\"COLIN-PC\"</Condition><Statements><Comment>Disabl"
            "e F11 to prevent access to nav pane</Comment></Statements></If></ConditionalBloc"
            "k></Statements></Su"
    End
    Begin
        Comment ="_AXL:b><Sub Name=\"^+Z\"><Statements><Comment>Open frmFormUnresizer</Comment><Ac"
            "tion Name=\"OpenForm\"><Argument Name=\"FormName\">frmFormUnResizer</Argument></"
            "Action></Statements></Sub><Sub Name=\"^H\"><Statements><Comment>Hide Navigation "
            "Pane</Comment><Action"
    End
    Begin
        Comment ="_AXL: Name=\"RunCode\"><Argument Name=\"FunctionName\">HideNavigationPane()</Arg"
            "ument></Action></Statements></Sub><Sub Name=\"^+H\"><Statements><Comment>Show na"
            "vigation pane</Comment><Action Name=\"RunCode\"><Argument Name=\"FunctionName\">"
            "ShowNavigationPane()</A"
    End
    Begin
        Comment ="_AXL:rgument></Action><Action Name=\"RunCode\"><Argument Name=\"FunctionName\">M"
            "inimizeNavigationPane()</Argument></Action></Statements></Sub><Sub Name=\"{F12}\""
            "><Statements><Comment>Disable SaveAs using F12 key</Comment><Action Name=\"Cance"
            "lEvent\"/></Statemen"
    End
    Begin
        Comment ="_AXL:ts></Sub></UserInterfaceMacro>"
    End


    • Edited by isladogs52 Monday, November 30, 2020 9:41 AM
    Monday, November 30, 2020 9:40 AM
  • An obvious reason for doing so could to transfer an autokeys macro to another database.

    Hi isladogs,

    Thank you, I thought so, but perhaps also other reasons?

    Long time ago when I started my third of fourth application, I had to transfer many things form one database to the other using copy and paste. And the macros that I used, I did rewrite them in the new database, a.o. the Toolbar

    At that point I decided to convert macros to VBA-code, and place the VBA-code as much as possible in a linked code library.

    That solved many low level problems, but also gave new problems, that were far more interesting however than just copy/paste or rewrite. This now has evolved to dynamical applications.

    Imb.

    Monday, November 30, 2020 1:47 PM
  • Assuming both databases are on the same network, even copying & pasting it using the SaveAsText/LoadFromText methods is unnecessary as a macro can be imported to another database using the same method as for any other object.

    However it could be useful if for some reason you wanted to send it to someone else or save it as a text file for possible future use - a bit like I did above!



    • Edited by isladogs52 Monday, November 30, 2020 3:13 PM
    Monday, November 30, 2020 3:12 PM