RE: excel -- MAPICS-L

To save as a template do a >File >>Save As.  At the bottom of the dialogue
box is a "Save as type" drop down.  Choose template.

Are you using Excel subtotal?  It will remember the subtotal criteria but
the subtotals need to be removed and then reapplied anytime the data is
refreshed.  Cell formatting is retained.

Another option is to have sheet1 have your data download/extract and do your
formatting and summaries in sheet 2

Doug

-----Original Message-----
From: Pete Olshavsky [mailto:polshavsky@xxxxxxxxx]
Sent: Tuesday, May 04, 2004 4:09 PM
To: MAPICS ERP System Discussion
Subject: RE: excel


Doug,
If I save it as a template. Which I don't know how to do. So if you please
explain.
 Will that remember my headings and subtotals and final total breaks.
Example: If I wanted sorted by item class/then by item, with subtotals on
Item Class for onhand qty.
Or is there something else that needs to be done?
 
Thanks To everyone who responded. 
Pete
 
 

"Ritsema, Doug B" <Doug.Ritsema@xxxxxxxxx> wrote:
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.
_______________________________________________
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.

                
---------------------------------
Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs 
_______________________________________________
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.