run code from Module via a macro

Discussion in 'Access Macros' started by sverre, May 21, 2008.

  1. sverre

    sverre Guest

    Hi,

    I have a module with VBA code that works fine, but I want the code to be
    executed/called on from a macro as I have other tasks in the macro to be
    executed
    directly after the vba-code. I thought I could use "openmodule" in a macro
    but that seem only to open the vba code in design view, not run it.

    The macro in which I would like to add my VBA-code is executed from a
    command-button on click by the way. The VBA looks like below:

    Best regards Sverre

    IMPORT ()
    Dim strFolder As String
    Dim strFile As String

    strFolder =
    "\\fspa\fileroot\DFS.8803.gemdisk\Projekt\Refaet\MAP\Avstämning_SESAM_Jeevesextrakt\extraktimport\"

    strFile = Dir(strFolder & "*.txt")

    Do While Len(strFile) > 0
    ' Put your code to import strFolder & strFile here

    DoCmd.TransferText acImportFixed, "Bokföringsextrakt_import_spec",
    "bokföringen mxg", strFolder & strFile, False
    strFile = Dir()
    Loop
    End Sub
     
    sverre, May 21, 2008
    #1
    1. Advertisements

  2. Sverre,

    Just like it says in the Subject of your post, the Action you need in
    your macro is RunCode.
     
    Steve Schapel, May 22, 2008
    #2
    1. Advertisements

  3. sverre

    sverre Guest

    Steve

    When I insert a macro with Runcode I get the possibility to insert a
    function with the expression builder.
    In there I can see my module called "import" but how do I build a expression
    to run my module with the
    expression builder?

    Best regards Sverre
     
    sverre, May 26, 2008
    #3
  4. Sverre,

    You don't include the name of the Module in the RunCode macro. I
    suggest to write the code as a function, in other words you can simply
    change the first line of the procedure to:
    Public Function Import()
    .... and the last line to:
    End Function

    And then in the RunCode macro:
    =Import()
     
    Steve Schapel, May 26, 2008
    #4
  5. sverre

    Derek Guest

    Steve,

    Alright.. I am having trouble with this. Maybe i am just not doing it right.
    I have my macro with "Runcode" and "=Import()" in the argument and the code
    in the module1 as:

    Private Function Import()
    On Error Resume Next
    DoCmd.SetWarnings False
    DoCmd.DeleteObject acTable, "AdjLiveRpt"
    DoCmd.TransferText acImport, "AdjLiveRpt", "AdjLiveRpt", "File.txt", True
    DoCmd.SetWarnings True
    End Function

    But it still give error "Expression entered has a function name Office can't
    find". Any Ideas?
     
    Derek, May 14, 2010
    #5
  6. Derek,

    Try making it Public Function, not Private.

    Also, I am not sure about the "acImport" in your code. It might be ok, but
    as far as I know, the options for TransferText should be acImportDelim,
    acImportFixed, or acImportHTML.

    Also, I assume you have created an Import Specification named "AdjLiveRpt"?

    As an aside, the SetWarnings actions in the macro don't achieve anything in
    this case, so they can be removed.

    My other question would be, if you are using a RunCode action in a macro to
    run a TransferText method... why not just simply do the TransferText action
    directly in the macro? Easier.
     
    Steve Schapel, May 14, 2010
    #6
  7. sverre

    Derek Guest

    Steve,

    The "Public" function worked great. Thank you! The importdelim you referred
    to was another thing that i had to change. And yes, the AdjLiveRpt was the
    Specification i created.

    This is working for now but i may soon put the code in the macro to make it
    easier.

    I have another question. I know in excel you can make a macro to subtotal
    one column with a break on another column (or grouping). Can you do the same
    in access?
     
    Derek, May 14, 2010
    #7
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.