Home > IT Answers > AS/400 > Macro Script on AS/400
deepu9321
3,370 pts.
 Macro Script on AS/400
AS/400, AS/400 macros, Macros
Hi,

Currently We are getting a Report on Monthly Basis using Macro script which runs manually on next day to monthend. Now, We have requirement for generating the Report on Monthend Date itself as part of over night process. Is there anyway to automate the Macro Scripts in AS400. Please suggest.

Pradeep.



Software/Hardware used:
AS/400
ASKED: August 9, 2011  5:56 AM
UPDATED: March 31, 2012  7:07 PM
RELATED QUESTIONS
Answer Wiki:
Pradeep, are you referring to a Client Access Macro script? Are you calling something from a menu? More details are needed I think... I've not used Macros from Client Access a whole lot, but I'm not aware of a way to run them automatically. Your PC would have to be on with an active logged in connection to the system...I believe... Not sure how this would be done automatically... Can you just put the programs into the batch process? Or are you running them from an interactive menu? If that is the case you'll have to write a wrapper that get submitted in batch or break out the base logic into a separate program that can be called via an interactive program or be submitted via batch.
Last Wiki Answer Submitted:  August 10, 2011  2:43 pm  by  Brycekmartin   45 pts.
All Answer Wiki Contributors:  Brycekmartin   45 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Although there’s not much chance of replicating the actual macro onto your AS/400, the functions that are done by the macro can usually be duplicated. The difficult parts to duplicate would be any times that your macro entered characters into a display file in one of your applications.

What tasks does your macro do?

Tom

TomLiotta
 108,135 pts.
 

I am not writing into any Display Files using Macro and using it for Extracting Data from Database to Excel Sheet.

Pradeep.

deepu9321
 3,370 pts.
 

Perhaps you should give some more details – the term ‘macro script’ isn’t very precise. Is it a macro in Client Access? in Excel? ‘macroo’s used to imply a keystroke recorder, but could now include any functionality.

How is the macro started – on which platform? Can you post the code here ?

Generating a report at a monthend is uitterly fundamental to business processing, and on IBM a Scheduled job entry would be a start point.

More information please…

Yorkshireman
 5,505 pts.
 

…using it for Extracting Data from Database to Excel Sheet.

An Excel-compatible file could be generated on the AS/400 on a schedule. The basic question would be about what “Data from Database” is used as input to the macro.

Tom

TomLiotta
 108,135 pts.
 

It is an Excel compatible macro.
And, As of now we are automating(Scheduled Jobs) all other Reports except Macro scripts.
Still it will be good if I can know about this.
Here is the structure of the code. I have not included fully.

Sub Macro1()
'
' Macro1 Macro
' Macro created 5/02/2010 by Allan Guatlo
'

'
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(44, 1), Array(55, 1), Array(64, 1), Array(73, 1), _
        Array(82, 1), Array(91, 1), Array(100, 1), Array(109, 1), Array(118, 1), Array(127, 1), _
        Array(136, 1), Array(145, 1), Array(154, 1), Array(168, 1), Array(176, 1), Array(190, 1))
    Cells.Select
    Cells.EntireColumn.AutoFit
....
...
...
    Columns("U:U").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Delete Shift:=xlToLeft
    
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Range("A1").Select
    
    ActiveCell.FormulaR1C1 = reportMonth
    
End Sub

Pradeep.

deepu9321
 3,370 pts.
 

Am I right in think ing that you run a Client Access transfer to move the dat to a PC, then your script?
If so, you can automate the Client access with the RTOPCB batch transfer. A STRPCCMD could be automated as a scheduled job within the IBM i Series, run the transfer, call Excel, and IFIRC, the macro set to run on opening the sheet.

Yorkshireman
 5,505 pts.
 

What is the macro selecting from?

Tom

TomLiotta
 108,135 pts.