Why don't you modify the print program to also output to a data file? Each
record in the file can contain the data needed to be imported by Excel
through the CA plug-ins available to Excel
Paul Nelson
Cell 708-670-6978
Office 708-425-4198
nelsonp@xxxxxxxxxxxxx
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[
mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Al Mac
Sent: Sunday, April 15, 2007 11:33 AM
To: Midrange Systems Technical Discussion
Subject: Re: Spool File to Excel
In my situation my budget is ZERO except for my time, meaning my time
availability working on other things the company might want done.
The reality is that we get the job done, but it is sufficiently a hassle
for any non-IT person, that IT persons end up doing all the migration
that's needed.
You have to have Operations Navigator installed, (which may have a name
change in the AS/400 to iSeries transition).
Because of having to keep end fiscal reports on spool file like forever,
there are thousands of reports in my name, so accessing reports certain
ways take like forever,s so for many.steps there is need to have something
else to do to keep busy while waiting.
You know in Windows how you can have some screen of some application in a
short cut icon ... well I have done that with Ops Nav Reports list
screen. It means a little less hassle getting there.
Ok, while in Client Access/400 (which may have a name change in the AS/400
to iSeries transition) I identify the date / time other characteristics of
the report I want to go to Excel, then in Ops Nav, I sort whatever columns
to make it easy to find the report, then using mouse, I right click on the
file (far left in Ops Nav printer list) then paste it inside some folder,
where I have different windows sub-directory folders for different kinds of
reports for different purposes.
Then I open Excel, designating the text file that I copied to whatever
folder from the report in Ops Nav. At this point you have to say ALL file
types in that folder. This invokes the Microsoft Windows Excel Wizard to
convert the text file to Excel-friendly. You have to specify edges of
columns, what kind of data, number decimal places etc. (Excel Wizard is not
clever enough to recognize from the AS/400 data some of the basics. This
may also vary with version of Microsopft products) It can help for you the
first time, to have the same report on screen from Client Access to review
how wide the fields need to be
When finally thru the process, save as, but this time specify a particular
Excel format (NOT all files types). There are some gotchas.
A standard AS/400 report has page breaks with column headers on each page.
My boss wants to see data in Excel without this clutter.
I can delete the page header lines from the actual Excel before I pass it
on to him, but for a big report this can be exceedingly tedious.
There are ways to use V Basic to automate some tedious tasks, or
alternatively attack the software that created the AS/400 report in the
first place.
You want the original AS/400 report to continue to be AS/400 user-friendly.
You want another version of the report that is friendly to the whole Excel
process.
I find it useful to modify the report on AS/400 before it even goes to
spool, to make the content what I call Microsoft-friendly. I now have
several programs generating data that is Microsoft-friendly. It can become
time consuming when same family of data needs to be presented in different
formats for different technology audiences.
There are different things to do to be friendly to the Wizard process ...
like I put extra junk in column headers that are messages to that process
such as "15 char wide need left justify".
Our AS/400 reports customarily have minus sign at end of numbers.
Microsoft needs them in front of numbers.
You also have to watch out for leading zeros and capabilities of whatever
generates the report ... Query/400 not as simple to make Excel friendly
reports as from RPG.
Also I have learned that even though the widest report that will print on
any of our printers is 198 positions wide, we can create a report wider
than that which is going to go into Excel, never be printed on AS/400.
Watch out when sending large reports in the e-mail. You not want to make
the result so big that someone e-mail system choke on something too big to
transmit that way.
-
Al Macintyre
Computer Data Janitor ... lots of time finding & cleaning up data messes
-
Greetings.
What low budget options are there to export spooled files to Excel?
John P. Walsh
jwalsh23@xxxxxxxxxxxxxxx
midrange.com
