How can Macros be created with Code?

[John Viescas]

If you're willing to use the undocumented and unsupported SaveAsText and
LoadFromText methods of the Application object, then it might be possible to
create a new macro if you can figure out how to construct the text file
correctly. Here's the Text file for a macro that opens a report with a
filter (with the macro name and condition columns show, but no macro name or
condition specified):

Version =196611
ColumnsShown =3
Begin
Action ="OpenReport"
Argument ="rptCustomers"
Argument ="2"
Argument =""
Argument ="[CustomerID]=1"
Argument ="0"
End

I have no clue what the Version entry means - although you could guess it
from experimentally using SaveAsText on some sample macros.

The syntax for the two methods is pretty simple:

Application.SaveAsText acMacro, "<macro name>", "< file name >"

Application.LoadFromText acMacro, "<macro name>", "< file name >"

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[Douglas J. Badin]

Great, that has definite possibilities.

I had found a posting from 1999 that talked about the undocumented
SaveAsText, but it made no mention of the LoadFromText.

I play with it and report back.

Thanks,

Doug

If you're willing to use the undocumented and unsupported SaveAsText and
LoadFromText methods of the Application object, then it might be possible to
create a new macro if you can figure out how to construct the text file
correctly. Here's the Text file for a macro that opens a report with a
filter (with the macro name and condition columns show, but no macro name or
condition specified):

Version =196611
ColumnsShown =3
Begin
Action ="OpenReport"
Argument ="rptCustomers"
Argument ="2"
Argument =""
Argument ="[CustomerID]=1"
Argument ="0"
End

I have no clue what the Version entry means - although you could guess it
from experimentally using SaveAsText on some sample macros.

The syntax for the two methods is pretty simple:

Application.SaveAsText acMacro, "<macro name>", "< file name >"

Application.LoadFromText acMacro, "<macro name>", "< file name >"

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Can Macros be created with Code? If so, how?

I am using Access 2002 and probably an Access Project (.adp) connected to
SQL Server 2000.

Ok, I have seen this in replies to similar questions,

- "Why do you want to?"
- "If you can write code, why do you need a macro?"

Not that the replies answered the question, I will satisfy your curiosities
if needed.

My client knows Macros. My client does not know VBA and has little time to
learn it and as a result, when I am gone wants to maintain the processes I
put in place with the least amount of effort especially in time critical
situations.

The simplified version of the requirements are:

- Easily maintain the process by the Client
- A configuration table will contain a list of reports that need to be run
depending on how the table is filtered.
- This table is maintained by a Form and will change very little but

when

it
does the Report Macros are re-generated.
- The client wants to be able review the Report Macro before it is used in
Production
- The client wants to be able to quickly modify the Report Macro when time
is of the essence before it is used in Production without having to try to
figure what went wrong with code that generated the Report Macro.

So, have there been improvements and can Macros be created with Code?

Thanks,

Doug

 

[Douglas J. Badin]

Sweet!

Here some sample code for an .adp.

I understand that the methods are undocumented and what that means.

Thanks a lot John!

I'm going to have check out your book "Microsoft Office Access 2003 Inside
Out".

Doug

----------------------------------------------

Const MACRO_DIR_PATH = "C:\Macro\"

Sub MacroToFile()

Dim dbs As CurrentProject
Dim strFileName As String
Dim objMacro As AccessObject
Dim strMacroName As String

Set dbs = Application.CurrentProject

For Each objMacro In dbs.AllMacros

strMacroName = objMacro.Name
strFileName = MACRO_DIR_PATH & strMacroName

' Close if open
If (SysCmd(acSysCmdGetObjectState, acMacro, strMacroName) <> 0) _
Then
DoCmd.Close acMacro, strMacroName, acSavePrompt
End If

SaveAsText acMacro, strMacroName, strFileName

Next objMacro

End Sub

Sub MacroFromFile()

Dim strFileName As String
Dim strMacroName As String

strMacroName = "Test"
strFileName = MACRO_DIR_PATH & strMacroName

LoadFromText acMacro, strMacroName, strFileName

End Sub

----------------------------------------------

Great, that has definite possibilities.

I had found a posting from 1999 that talked about the undocumented
SaveAsText, but it made no mention of the LoadFromText.

I play with it and report back.

Thanks,

Doug

If you're willing to use the undocumented and unsupported SaveAsText and
LoadFromText methods of the Application object, then it might be

possible

to
create a new macro if you can figure out how to construct the text file
correctly. Here's the Text file for a macro that opens a report with a
filter (with the macro name and condition columns show, but no macro

name

or
condition specified):

Version =196611
ColumnsShown =3
Begin
Action ="OpenReport"
Argument ="rptCustomers"
Argument ="2"
Argument =""
Argument ="[CustomerID]=1"
Argument ="0"
End

I have no clue what the Version entry means - although you could guess it
from experimentally using SaveAsText on some sample macros.

The syntax for the two methods is pretty simple:

Application.SaveAsText acMacro, "<macro name>", "< file name >"

Application.LoadFromText acMacro, "<macro name>", "< file name >"

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Can Macros be created with Code? If so, how?

I am using Access 2002 and probably an Access Project (.adp) connected to
SQL Server 2000.

Ok, I have seen this in replies to similar questions,

- "Why do you want to?"
- "If you can write code, why do you need a macro?"

Not that the replies answered the question, I will satisfy your curiosities
if needed.

My client knows Macros. My client does not know VBA and has little

time
to

learn it and as a result, when I am gone wants to maintain the

processes

I when

used

in

try

to