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 :
Do the transfer using STRPPCMD
- Open the spread sheet with another STRPCCMD
- 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
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 %>
' Response.Expires = 0
' Response.CacheControl = "no-cache"
Response.ContentType = "application/vnd.ms-excel"
Response.Expires = -1500
Response.ExpiresAbsolute = Now() - 1
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 & "'"
if strNumber > " " then
' Select the data from the table FILE filtered by the store number
sql = "select * from LIBRARY.FILENAME where STR = " & strNumber
' open the connection
' Execute the sql
rs.Open sql, conn
<table align=center border=2 width=75% >
' Move to the first record
' Start a loop that will end with the last record
do while not rs.eof
<%= rs("STR") %>
<%= rs("DESC") %>
<%= rs("ONHAND") %>
' Move to the next record
' Loop back to the do statement
' Close and set the recordset to nothing
Then, in a browser, go to the URL
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:
On the General Tab give your data source a name like “MyAS400db”, and select your system name using the Drop-down.
- 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…