RE: excel

["Ritsema, Doug B" <Doug.Ritsema@xxxxxxxxx>]

Just one other thing to add to method 1.

You can save the spreadsheet as a template file with out the data and have
it automatically refresh every time you open the template.

Doug

-----Original Message-----
From: Tom James [mailto:TJAMES@xxxxxxxxxxxxx]
Sent: Tuesday, May 04, 2004 10:36 AM
To: mapics-l@xxxxxxxxxxxx
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.