Adding overlays to an AS400, great for replacing pre-printed paper - Its a pain to get going but like everything once you can map it out in your head its easy.

Prepare the AS400

1. Setup an Overlay Library > CRTLIB

2. Create a physical file to contain the PC overlay file (You only need to do this once)

> CRTPF FILE(OVERLAY/INVOICES) RCDLEN(32766) TEXT('INVOICE OVERLAYS') MAXMBRS(*NOMAX) LVLCHK(*NO)

Creating and Setting the Overlay

1. Download and extract the AFP printer drivers http://www-01.ibm.com/support/docview.wss?uid=psd1P4000187

2. Setup a new printer using the drivers you downloaded - Select "Print to File" as the port --> Click have disk and point to the AFP drivers from step 1. Now select IBM AFP 144,240,300 or 600 the number correspond to the DPI of the printed document - 600 is fine for local printing but if you are pushing overlays over WAN links then pay careful attention to the final file size (E.g. A invoice overlay I made is 177KB using the 240DPI and 1.2mb using 600DPI)

3. Create your overlay - I use Adobe Fireworks but whatever you prefer.

4. On XP --> Right click the final file and print (The Default XP print wizard) select your AFP printer and input the file name (For this example we'll use "invoice.oly". The file will generate to your profile folder c:\Documents and Settings\%username%\

5. Upload the file to a dir on your AS400 - You can ftp it or use iSeries Navigator (I'm using a folder call "overlays")

> CPYFRMPCD

> CRTOVL

If you have exsisting print queues and the overlay isn't printing then check its setup as *AFPDS and not *SCS

There is loads of details in this PDF: http://joekane.eu/as400-printing-direct-to-ip-printer

Best of luck and enjoy.

Page 40 Notes

Click here to download:

as400-print-setup.pdf (4.03 MB)

(download)

Click here to download:

as400-print-setup.pdf (4.03 MB)

If you have used the tn5250j (5250 terminal emulator for the AS/400 written in Java) software then you will know the Enter Key is setup as "Field exit" This is the setup on the old terminal key stations but on PC Enter means Enter and users don't want to change their habits.

Lucky the tn5250j is open source so you can just download and compile you own version. This is surprisingly simple - download the source and read antbuild.txt - There are a few dependencies but nothing too difficult.

I have attached the compiled Jar file so you dont have do, Enjoy

P.S. This also works great for OpenVPN ALS - An Opensource web-based SSL VPN server written in Java.

Click here to download:

tn5250j.jar (588 KB)

(download)

Click here to download:

AS400 Java Client.avi (13.23 MB)

Here is a great time saving macro for iSeries Client access sessions. If you use client access then you know manually updating records gives you a headache.

This script pulls records from an excel spreadsheet and imports them based on a recorded macro. Really simply solution and very effective.

First create your excel file with two headers "COL1" and "COL2" without the ""

Download the template and insert your macro. If your not familiar with macros - Start a client access sessions go to
Actions --> Start Recording Macro --> Give it a name and make sure to select VBScript File.

The macro saves to "C:\Program Files\IBM\Client Access\Emulator\Private\"

Thanks to Laurens Van Keer for the script: http://laurens.vankeer.eu/scripting/excel-2-as400-update + some slight adjustments from me.

[PCOMM SCRIPT HEADER]
LANGUAGE=VBSCRIPT
DESCRIPTION=
[PCOMM SCRIPT SOURCE]
OPTION EXPLICIT
autECLSession.SetConnectionByName(ThisSessionName)
Dim ObjExcelAppl, ObjWorkbook, ObjWorksheet, StrFileName
Dim cCol1, cCol2
Dim c1, c2
 
' ...
' Call the Main subroutine.
Main
' Clean up after executing your script.
Set ObjWorksheet = Nothing
Set ObjWorkbook = Nothing
Set ObjExcelAppl = Nothing
 
Sub Main()
 
    ' Open and create Excel spreadsheet.
    Set ObjExcelAppl = CreateObject("Excel.Application")
    StrFileName = ObjExcelAppl.GetOpenFilename _
        ("Microsoft Excel bestanden (*.xls*),*.xls*")
    ObjExcelAppl.DisplayAlerts = False
 
    ' Valid name?
    If StrFileName <> False then
 
        ' Open spreadsheet.
        Set ObjWorkbook = ObjExcelAppl.WorkBooks
        ObjWorkbook.Open StrFileName
 
    ' Check if spreadsheet is already open.
    On Error Resume Next
    ObjExcelAppl.ActiveWorkbook.Save
    If Err.Number = 1004 Then
        Msgbox "Excelfile is already open!"
        Exit Sub
    End If
    On Error GoTo 0
 
    ' Load first sheet in workbook.
    ObjExcelAppl.Worksheets(1).Activate
    Set ObjWorksheet = ObjExcelAppl.Worksheets(1)
 
        ' ...
        Process_Sheet
        ' ...

        ' Save and close workbook.
    ObjExcelAppl.ActiveWorkbook.Save
    ObjExcelAppl.DisplayAlerts = True
    ObjExcelAppl.ActiveWorkbook.Close(True)
 
    End If
 
End Sub
 
Function Process_Sheet()
 
    ' Correct sheet?
    Dim i
    If Is_Correct_Sheet() Then
 
        ' Process every row (except the header, i=1).
    i = 2
 
    ' Stop upon the first empty row we find.
    Do While ObjWorksheet.Cells(i, 2).Value <> Empty
        Process_Row(i)
        i = i + 1
        Loop
 
    Else
    Msgbox "Wrong Excel sheet!"
    End If
 
End Function
 
' Check if the given sheet is correct.
Function Is_Correct_Sheet()
 
    Dim c ' column number
    Is_Correct_Sheet = False
    c = 1
 
    Do While ObjWorksheet.Cells(1, c).Value <> ""
 
        Select case ObjWorksheet.Cells(1, c).Value
            case "COL1" cCol1 = c
            case "COL2" cCol2 = c
    End Select
 
        c = c + 1
 
    Loop
 
    ' Found header?
    If ( cCol1 <> 0 And cCol2 <> 0 ) Then
        Correct_Sheet = true
    End If
 
End Function
 
Function Process_Row(ByVal row)
 
    c1 = CStr(ObjWorksheet.Cells(row, cCol1).Value)
    c2 = CStr(ObjWorksheet.Cells(row, cCol2).Value)
 
    ' ... Insert your Recorded vbs Macro here

    ' Place cursor.
    autECLSession.autECLOIA.WaitForAppAvailable
    autECLSession.autECLPS.SetCursorPos 09,034
 
    ' Type c1 in current field (where cursor is positioned).
    autECLSession.autECLOIA.WaitForInputReady
    autECLSession.autECLPS.SendKeys c1
    autECLSession.autECLPS.SendKeys "[field+]"
 
    ' Type c2 in the next field.
    autECLSession.autECLOIA.WaitForInputReady
    autECLSession.autECLPS.SendKeys c2   
 
    ' Send the data.
    autECLSession.autECLOIA.WaitForInputReady
    autECLSession.autECLPS.SendKeys "[enter]"
    autECLSession.autECLOIA.WaitForAppAvailable
 
    ' ...

End Function
 
' ...

(download)

Click here to download:

video.avi (15.29 MB)