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,

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

--
Steve Schapel, Microsoft Access MVP

sverre wrote:
> 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

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" wrote:

> Sverre,
>
> Just like it says in the Subject of your post, the Action you need in
> your macro is RunCode.
>
> --
> Steve Schapel, Microsoft Access MVP
>
> sverre wrote:
> > 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,

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, Microsoft Access MVP

sverre wrote:
> 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?

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" wrote:

> 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, Microsoft Access MVP

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, Microsoft Access MVP


"Derek" <(E-Mail Removed)> wrote in message
news:A10A6762-FB37-4C89-985E-(E-Mail Removed)...
> 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" wrote:
>
>> 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, Microsoft Access MVP

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?

"Steve Schapel" wrote:

> 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, Microsoft Access MVP