Re: Opening Excel from AS400

Thanks Scott. I tried this and the .xls opened in Excel but I had to do the
conversion within Excel to see the data properly. On the other hand, when I
renamed it to .csv, it opened in Excel perfectly. (Maybe because my system
is set to open .csv with Excel).

Vinay

On Mon, Jan 18, 2010 at 1:04 AM, Scott Klement
<midrange-l@xxxxxxxxxxxxxxxx>wrote:

What do you mean by "seamlessly"? What would you consider seamless?
What would you not consider seamless?

These are the steps (as it seems you already know):

a) Make sure NetServer is running/configured/usable by your users.
b) Have your "AS400 program" write the CSV to the IFS.
c) Use STRPCCMD to open Excel and open the document.

In step (b), you probably want to end the file with .xls. Personally I
abhor doing that, since the file isn't actually an XLS file, it's a CSV!
But ending with XLS is how you get Windows to open it in Excel.

Then use STRPCCMD like this:

CHGVAR VAR(&CMD) +
VALUE('rundll32 shell32,ShellExec_RunDLL +
"\\192.168.0.1\xls\whatever.xls"' )
STRPCCMD PCCMD(&CMD) PAUSE(*NO)

This will open Excel on the file you specified. However, there are
several problems that might make you say "that's not seamless!"

1) STRPCCMD has no ability to check errors on the command it runs. So
if there's a problem opening it, you can't detect that in your program.

2) CSV files are not Excel documents. They lack any manner of
formatting, inserting formulas, etc. Yecccch.

3) You have a bit of a housekeeping problem, in that there's no way to
know when the user is done with the CSV file in order to clean it up.

4) Accessing NetServer is klunky, IMHO. Especially if you have any
users who aren't on your local network... using NetServer over the
Internet is yucky.

5) With this method of doing things, you may have a problem with
multiple users running your application at the same time because the app
might always use the same filename, and two users writing to the same
CSV file would create a mess. I suppose you could work around this by
generating a unique filename for every request... but, then the user
sees this "ugly" filename on their PC.


Personally, I would generate an ACTUAL XLS file instead of a CSV, and I
would control formatting, etc, to make it attractive to the user. I
would also use an HTTP download instead of NetServer to get it to the
user's desktop. The HTTP download can eliminate the need for a funky
filename on the PC. If you run your HTTP download through an
application, you can detect when teh download is complete and delete the
XLS file, solving the cleanup problem as well. HTTP download works a
lot better over the Internet as well.




Vinay Gavankar wrote:

Is it posible to write a CSV file to IFS and open it in Excel, seamlessly
from an AS400 program?

This is what I want to do:

Program will display data from a file. When user presses an F key (Export

to

Excel), Excel is started in a new window with that data. User can then

save

it whereever he wants on his PC.

I was thinking that a CSV file could be written somewhere on IFS and then
maybe STRPCCMD (or something similar) can open Excel with that file. I

don't

know whether such a thing is possible.

There is no specialized software on AS400, and getting any new software
installed, is not an option.

Any ideas?

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.