• Subject: Re: Automating data from AS/400 to Microsoft Access.
  • From: Don Schenck <schencd%AM_LZCH%VASELL@xxxxxxxxxxxxxx>
  • Date: Fri, 04 Dec 1998 09:30:20 -0400
  • Autoforwarded: false
  • Disclose-recipients: prohibited
  • Hop-count: 2
  • Importance: normal
  • In-reply-to: <80D6C3BD40BED111A0080001FA44AEB6012CFDF4@nmchqt16.nmc.com>
  • MR-Received: by mta TIMONE.MUAS; Relayed; Fri, 04 Dec 1998 09:30:20 -0400
  • MR-Received: by mta VASELL; Relayed; Fri, 04 Dec 1998 14:30:09 -0400
  • MR-Received: by mta MPMV04; Relayed; Fri, 04 Dec 1998 14:29:22 -0400
  • Sensitivity: Company-Confidential
  • UA-content-id: 11CC239E1300
  • X400-MTS-identifier: [;6020301404121998/A43159/TIMONE]

:::O.K.  I'll bite.....how do you put AS/400 data into an Excel spreadsheet
:::using only RPGLE. 

Weeeeeeeeeeell ... it's about time someone asked! <g>

Okay; I DID conveniently overlook the fact that you need the HTTP server
running on your AS/400.

But assuming that is the case:

1. Write a CGI program in ILE RPG to output a web page. Put a table -- ONLY a
table -- on the page. Into that table you put the values you want to be put
into Excel.

NOTE: You can embed EXCEL FORMULAS in your web page table! That's right! You
can put the literal "=SUM(B2:B99)" in your table on your web page, and Excel
will import it as a formula and perform the formula. Combine this with
custom-written formulas (in VBA) and you can really go to town.

2. Create an *.IQY file for use by the Excel Web Query feature. What's that?
You don't know how to do that? It's quite easy. I'll post an example here if
anyone is interested.

3. Run the web query you have defined in the file from step number two, above.
To do that, go to the Excel menu "Data|Get External Data|Run Web Query...".

4. Sit back and marvel; you have just used an RPG program to squirt data into
an Excel spreadsheet!

IF you have HTTP server running on your AS/400, I can think of NO reason why
you shouldn't at least do this once; if for no other reason than to learn and
get excited about this. The possibilities are endless.

This is yet ANOTHER reason to make the leap to ILE.

******** NOTE NOTE NOTE *********

This knowledge comes from Roger Pence, who showed this to me recently. In fact,
Roger has a sample CGI program that can easily be adapted. It can be downloaded
from www.The400Group.com and navigate your way, beginning at "Publications".



-- Don Schenck

| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com

This thread ...


Return to Archive home page | Return to MIDRANGE.COM home page