Automatically refresh data from AS/400 DDS Described file to Excel spreadsheet

23,730 pts.
Tags:
AS/400
Lotus
Microsoft Excel
Hi,

Is there an easy way to automatically "refresh" the data in an excel spreadsheet from an AS/400 DDS Described Data file? I'd like the data to be collected whenever someone opens the spreadsheet. I can already get the data using a transfer request, but I have to start the request manually. I've tried making a macro to run the request, but this doesn't seem to work.

Thanks in advance,
Martin Gilbert.
ASKED: January 28, 2008  1:54 PM
UPDATED: August 27, 2013  6:55 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Simple – add the transfer option available in excel and let the user do it.
Not so simple – Must be done interactive
Create menu option which call clle to :

  1. Do the transfer using STRPPCMD
  2. Open the spread sheet with another STRPCCMD
  3. Then refresh as needed with data/transfer for iSeries option .

That is about as automatic as I can think of is to open the spreadsheet via a CLLE option
Good Luck

Another route to go would be to simply show the current data in the file as a spreadsheet using a browser and ASP.

You could create Inquiry.asp something like this:

<%@ Language=VBScript %>

<%
Option Explicit

' Response.Expires = 0
' Response.CacheControl = "no-cache"
Response.ContentType = "application/vnd.ms-excel"
Response.Expires = -1500
Response.ExpiresAbsolute = Now() - 1
Response.AddHeader "pragma","no-cache"
Response.AddHeader "cache-control","private"
Response.Buffer = True

dim mNameLast, strNumber, cn, conn, rs, mNameFull, sql

' Get the manager's name to search on
mNameLast = UCase(Request.QueryString("mNL"))

' Get the store number to search on
strNumber = Request.QueryString("str")

' Connect to the db with a DSN-less connection
cn = "Provider=IBMDA400;Data Source=n.n.n.nn;User ID=USER;Password=PASSWORD;"

' Create a connection object
Set conn = Server.CreateObject("ADODB.Connection")

' Create a server recordset object
Set rs = Server.CreateObject("ADODB.Recordset")

' Base the SQL statement on the criteria
if mNameLast > " " then
' Select the data from the table FILE filtered by the manager's last or email name
sql = "select * from LIBRARY.FILENAME where MEMAIL = '" & mNameLast & "'"
end if

if strNumber > " " then
' Select the data from the table FILE filtered by the store number
sql = "select * from LIBRARY.FILENAME where STR = " & strNumber
end if

' open the connection
conn.Open cn

' Execute the sql
rs.Open sql, conn

%>

<html>
<body>
<table align=center border=2 width=75% >
<tr bgcolor=Green>
<td><b>Store</b></td>
<td><b>Description</b></td>
<td><b>On Hand</b></td>
</tr>

<%
' Move to the first record
' rs.movefirst

' Start a loop that will end with the last record
do while not rs.eof
%>

<tr bgcolor=White>

<td>
<%= rs("STR") %>
</td>

<td>
<%= rs("DESC") %>
</td>

<td>
<%= rs("ONHAND") %>
</td>

</tr>

<%
' Move to the next record
rs.movenext
' Loop back to the do statement
loop
%>

</table>

</body>
</html>

<%
' Close and set the recordset to nothing
rs.Close
set rs=nothing
conn.Close
set conn=nothing
%>

Then, in a browser, go to the URL

http://somewhere/Inquiry.asp?mNL=JONES&

or

http://somewhere/Inquiry.asp?str=123&

What would display (providing in the file) would be either all the records for JONES or all the records for store 123 displayed in an EXCEL spreadsheet. Complete with sortable columns.

Hope this helps.

—————-
<b>Another way</b> that I have used is to have an embedded MS Query inside the Excel which you can set to automatically refresh upon opening the Excel document.

To do this you need to install the ODBC data access component of iSeries Access for Windows.
Next – you have to create an ODBC System DSN (data source).
In the Administrative Tools in Windows Control Panel, double-click Data Souces ODBC.
Go to the system DSN tab and click Add.
Choose iSeries Access ODBC driver.
On the iSeries Access for Windows ODBC setup window you only need to complete a couple of fields on two of the tabs, as follows:

  1. On the General Tab give your data source a name like “MyAS400db”, and select your system name using the Drop-down.
  2. On the server tab, in SQL default library, enter your data library you want to query. If you don’t want users to upload data to the iSeries then change the Connection type to ReadOnly.

That’s pretty much it, so click ok.

Now is the best bit.

Open up Excel – your now ready to create an embedded query to retrieve the data straight into Excel. Here how to do it:

On the menu goto – Data -> Import External Data ->New Database Query
A window should appear asking you to choose your data source. Choose the data source name you created earlier “MyAS400db”.
This will startup Microsoft Query and show you a list of tables from your default library you specified in the server tab of the DSN.
Now choose your table and click add. Hit close if no more tables are required.
The table should be at the top of the screen now showing you the field names in alphabetical order wit the first field being an “*” which means All Fields.
Drag a field or the * field to the bottom pane inside MS Query.
The contents of the table should populate immediately.
You can obviously do sophisticated record selection here and join more than one table using key fields, but for now just stick with one table.
If you go to the File menu and choose Return Data to Micorsoft Office Excel, MS Query will close and Excel will prompt you for the location to put your Imported Data.
If you click ok the table contents will be dropped automatically into Excel.
If you right-click anywhere in the imported data range, and choose the option Edit Data Range properties you can control refresh options, one of which is to refresh on Open.

Hope this help too…

Rod

Discuss This Question: 8  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • JAC
    I have been looking for a solution like this one for a while. I should have started here! THIS IS GREAT! Is there a possibility to have sample code for: 1. do the transfer using STRPPCMD 2. open the spread sheet with another STRPCCMD 3. then refresh as needed with data/transfer for Iseries option .
    260 pointsBadges:
    report
  • TSCHMITZ
    Hi, i personaly do not like automatic updates on open (MS_QUERY) because sometimes the excel file should be available when the user is not connected to the I5. I prefer the following solution, working on request from the user : 1) the main excel sheet shows Timestamp of both Available DB from I5 and timestamp of implemented data from i5, int the following form : Check new Update Available DB dated : 2008-02-06 13:44 Finished Update Data Implemented DB dated : 2008-02-06 13:44 "Check New update" and "Update data" are action buttons inside excel. "Check New update" data use a MS Query inside excel to control that : 1) I5 process is not running (if yes, update is not allowed and a pop-up message notify user to wait a bit) 2) TimeStamp of available DB is more recent than implemented one. If yes, update Timstamp of available data inside excel and visualy shows that with color. Update data run the following process(es). 1) Clear actual data inside Excel 2) Remove Autofilters 3) Import new data 4) Synchronize Available and imported Timstamp 5) optional treament... like Pivot table regenaration... If you put the full name of the 2 required MS Query variable inside one sheet of your excel file, you may reuse this excel file to import and make some basic B.O. application very quick. On i5 job (assuming the data to be used in excel is generated in a batch process), the requirements are : 1)When Batch job start, flag the application with ACTIVE STATUS using Timestamp control file (will lock excel import for a while) 2) Let your update run 3) When batch stops, update the TImestamp control file and release status. If somebody has some interest using this technique, send me a private message and i"ll prepare a example of both I5 and Excel side. Prerequesites are ODBC link available on the machine running the excel import. Bye.
    470 pointsBadges:
    report
  • Bigmac46
      PGM                                                        
            DCL        VAR(&STRCMD) TYPE(*CHAR) LEN(55) +        
                         VALUE('C:\DOWNLOAD\JTESTFROM.DTF')      
            DCL        VAR(&PCCMD) TYPE(*CHAR) LEN(55)           
            STRPCO     PCTA(*NO)                                 
            MONMSG     MSGID(IWS4010)                            
            STRPCCMD   PCCMD(&STRCMD) PAUSE(*NO)                 
            CRTDTAARA DTAARA(QTEMP/STRPCO) TYPE(*CHAR) LEN(1) +  
                TEXT('START PCO DATA ARE')                       
            MONMSG CPF0000                                       
            STRPCO PCTA(*NO)                                     
            MONMSG MSGID(IWS4010)                                
            STRPCCMD   PCCMD('START C:\DOWNLOAD\JTEST.xls') +    
                         PAUSE(*NO)                              
    
    ONCE THEY GET IN , IF IT NEEDS TO BE UPDATED: CLICK DATA CLICK TRANSFER FORM ISERIES AND SELECT THE TRANSFER TO IMPORT DATE STARTING WHERE YOU ARE POSITIONED IN THE SPREADSHEET THEN RUN IT. OR GET OUT AND TAKE THE MENU OPTION TO RUN THE PROGRAM AGAIN
    1,000 pointsBadges:
    report
  • Chuckmool
    Hi TSCHMITZ, Great tip! I have been serching for a solution like yours for a week. I am stumped and would be very grateful if you could send me an "example of both I5 and Excel side." "If somebody has some interest using this technique, send me a private message and i”ll prepare a example of both I5 and Excel side." my email address is mledra@rogers.com Thank you!
    10 pointsBadges:
    report
  • Alicsc
    Hi TSCHMITZ, Please send it to me. Appreciate. Thanks, Ali
    370 pointsBadges:
    report
  • nhewage
    Hi TSCHMITZ, Please send it to me too. Appreciate. Thanks, Hewage
    50 pointsBadges:
    report
  • DoneThat
    Wow...I can sure use the VB script + Browser example above. Let me add my thanks for that. Here's some tips/gotchas on using MS Query. 1) Think about usability before making the refresh automatic. You will lock user out of Excel when it opens. It can be slow, user has no idea why (no screen info other than hourglass) and can be extremely annoying. 2) Did you know you can paste any SQL statement into MS Query instead of using the GUI build? You can do all your development interactively on AS400 & when done - paste & go. And that SQL can be ANY complexity you dream up which becomes VERY powerful! I have 300+ line SQL's with multiple joins, unions & CTEs in Excel 2003 running with no problem. 3) Gotcha 1...you can use /*...*/ in MS Query to doc your work. BUT (hah) you cannot START the query with a comment. 4) Gotcha 2...do not use a ; to end the statement. 5) Gotcha 3...make "WITH NC" (no commitment) the last line for all SELECT queries. And yes TSCHMITZ - I would like to see your technique too. I make users go to the Data Menu to refresh but giving them a couple buttons is a nice touch. Please send to garylavery@aol.com. Very much appreciated. Gary
    830 pointsBadges:
    report
  • mcarpntr
    Hi TSCHMITZ, Great tip! I have been serching for a solution like yours for a week. I am stumped and would be very grateful if you could send me an “example of both I5 and Excel side.” my email address is <a href="mailto:mcarpent@dimeo.com" Thank you!
    135 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following