run code from Module via a macro

[sverre]

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

 

[Steve Schapel]

Sverre,

Just like it says in the Subject of your post, the Action you need in
your macro is RunCode.

 

[sverre]

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

 

[Steve Schapel]

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()

 

[Derek]

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?

 

[Steve Schapel]

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.

 

[Derek]

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?