Re: excel -- MAPICS-L

As long a you're application uses ODBC, Client access driver, you can
connect to your AS400.



Are there also people out there who use LINUX on there servers or desktops

IBM's Client Access and ODBC are also available for that platform.

If you have experience with apache and PHP you can easily access you're
Mapics database from the web. (Using SQL Queries, CALL CL programme's etc)

The good thing is that LINUX is for free.




----- Original Message ----- 
From: "Tom James" <TJAMES@xxxxxxxxxxxxx>
To: <mapics-l@xxxxxxxxxxxx>
Sent: Tuesday, May 04, 2004 4:35 PM
Subject: Re: excel


>
>         This should be both simple and easy.  I am sure there are many
> ways to do this.  The 2 ways I normally use are explained below. The
> first method has the advantage of being able to link the criteria to a
> cell or cells on the spreadsheet (no VBA required).
>
> Prerequisite:  Be sure you have taken all of the "Data Access" options
> in selective setup.
>         Windows menu--Start--Programs--IBM Iseries Access for
> Windows--Selective Setup
>
> Very roughly the steps are:
>
> Method 1
>
> 1) Create an ODBC data source.
>          Windows menu--Start--Programs--IBM Iseries Access for
> Windows--ODBC Administration
>                 --Add--select Client Access ODBC Drive--Finish-
>          Name the data source  (MAKE UP A NAME)
>          If your system's name is not in the "System:" box just put in
> it's IP address,  10.10.10.10 for example.
>          Go to the "Server" tab and enter *usrlibl under Library List:
> (or AMFLIB etc)
>          Go to the "Translation" tab and click the checkmark for
> "Convert Binary Data......."
>          Punch Apply and OK.
>
> 2) Create a Query in Excel.
>           Data pull down--Get External Data--Create New Query (be
> patient this may take time to load)
>           Choose the data source you created in step 2 and click OK
>           Choose a file or files to query.  ITEMBL for example and
> click add then click close
>           Link files by dragging and dropping matching fields
>           Select fields to download by dragging them to the  to the
> bottom half of the screen, ITNBR, MOHTQ, and ITCLS for example.
>           Set Criteria:  View pull down select Criteria. The criteria
> area is now in the middle of the screen.
>           Drag ITCLS from the top to the criteria area.
>           Click on the Value area below the ITMCLS criteria and type
> one of your item classes.
>           You should see the data at the bottom of the screen update.
>           Pull down File and select "Return Data to MS Excel"
>           Click OK
>
> 3) To refresh data
>          Select a cell within the data area.
>          Data pulldown--! Refresh Data.
>
> Note: You may need to review the data range properties to be sure the
> data comes down in a manner that does not mess up your Excel formulas
>
> 4)  Refresh button
>          You can either add the preexisting "!" (refresh data) button
> to your toolbar (no VBA required) or make your own on sheet button and
> use a little VBA.
>          Alternatively you can change the data range properties to
> refresh on open, (no VBA required).
>
> Method 2
>
> 1) Create download definition
>          Data pulldown--Transfer Data from ISeries
>          Select Create New and click OK
>          Select your ISeries system name or type in the IP address,
> Click next
>          Type in your file(s) library and name  AMFLIB/ITEMBL for
> example and click next
>          Click Data Options and the Details. Sign on to the ISeries
>          On the Select tab pick the fields you want. Apply
> (ITNBR,ITCLS,MOHTQ for example)
>          On the Where tab put in the criteria for record selection.
> Apply  (  (MOHTQ > 100000) for example)
>          On the Order By tab put in the sort information. Apply. OK  (
> MOHTQ for example)
>          Click OK
>          Click Properties and click Convert CCSID 65535 on the Convert
> Tab.  Apply. OK.
>          Click Next.  Fill in a file name to save your definition
> C:\test1 for example.  It will add the appropriate extension (.dtf).
>          Click Finish to download.
>
> 2) To run an existing .DTF
>          Data pulldown--Transfer Data from ISeries
>          Select the dtf file and click OK
>
> 3) Normal excel macro procedures (VBA) can make a button to automate
> this.
>
>
> This certainly take longer to explain than do.......
>
> Good Luck,
> TJ
>
>
> _______________________________________________
> This is the MAPICS ERP System Discussion (MAPICS-L) mailing list
> To post a message email: MAPICS-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/mapics-l
> or email: MAPICS-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/mapics-l.
>
> ****************************************
> This email has been scanned for viruses.
> Timesavers (Sandingmaster/Grindingmaster)
> Quality & Value Without Compromise
> ****************************************
>

****************************************
This email has been scanned for viruses.
Timesavers (Sandingmaster/Grindingmaster)
Quality & Value Without Compromise
****************************************