Why not use EXCEL query vs. transfering a whole physical file? <BR>
Joins and data selections make the process very easy

Correct me if I'm wrong, but isn't Excel similar to Access in that the query<BR>
will ALWAYS be performed on the client machine? If so, that means all of<BR>
the data will be retrieved but only the selected data will be presented.<BR>
<P>
chuck<BR>
Opinions expressed are not necessarily those of my employer.<BR>
<P>
"finncoiv" < This e-mail address is being protected from spam bots, you need JavaScript enabled to view it > wrote in message<BR>
news: This e-mail address is being protected from spam bots, you need JavaScript enabled to view it <BR>
> Why not use EXCEL query vs. transfering a whole physical file?<BR>
> Joins and data selections make the process very easy<BR>
<P>
<P>

Yes you can use Excel Query. It is a very powerful tool. However, it can also create a lot of overhead on the iSeries if the query needs to build an access path. The intent of the article is to provide the basics of downloading data via Excel.

When using the Browse button, how is the library list determined? All we see currently is QGPL. Do we need to change something in iSeries Navigator or somewhere else?

The article says, "There is a limitation of a maximum of 65,535 records that can be transferred from an iSeries physical file to an Excel spreadsheet". <p>I'm not sure what release it started in, but i think that at least as of V4R5, when there is more than 65,535 records returned, additional worksheets will be added to accomodate the overflow.

Actually, it turns out that if you specify a data transfer from within Excel, you cannot create a library list prior to browsing. You can however type in a library name eg MYLIB and then click to browse, which will reflect the library you just entered. Furthermore, it doesn't appear to accept comma delimeted lists of Libraries unless you fully qualify LIB/FILE. An alternative would be to create the transfer request definition via Client Access Transfer-->Receive File From Host. This not only allows you to define a transfer request which can be used with Exel, but also allows defining of a true query.

AFAIK, the 65535 limitation is an Excel limitation in many versions, not an iseries limitation. <p>To get around it, download to a CSV, and then bring the data into MS Access. <p>Dave

David,<BR>
<P>
It's still the limit in Office 2003.<BR>
<P>
chuck<BR>
Opinions expressed are not necessarily those of my employer.<BR>
<P>
"David Abramowitz" < This e-mail address is being protected from spam bots, you need JavaScript enabled to view it > wrote in message<BR>
news: This e-mail address is being protected from spam bots, you need JavaScript enabled to view it <BR>
> AFAIK, the 65535 limitation is an Excel limitation in many versions, not<BR>
an iseries limitation.<BR>
><BR>
> To get around it, download to a CSV, and then bring the data into MS<BR>
Access.<BR>
><BR>
> Dave<BR>
<P>
<P>

Chuck Ackerman wrote:<BR>
> Correct me if I'm wrong, but isn't Excel similar to Access in that<BR>
> the query will ALWAYS be performed on the client machine? If so,<BR>
> that means all of the data will be retrieved but only the selected<BR>
> data will be presented.<BR>
<P>
Nope, the data selection is done on the 400. You can even prove this to<BR>
yourself by running a query and looking at the open files for that session.<BR>
<P>
Bill<BR>
<P>
<P>

I'm using Excel 2002 and there is no option for data transfer from the iSeries. I can find the file cwbtfxla. But nothing happens when I open it. Unfer the "Data" menu option in Excel there is an "Import External Data" option but nothing in there indicates an option to x-fer from iSeries. Where am I going wrong ?

While in Excel, open TOOLS, ADD-Ins. <p>When the list of Add-ins appears there should be an option to "Add" an Add-in, select that, and a file-open box should appear. <p>Select the cwbrfxla file, and then the add-in should appear within Excel. <p>Dave

I just don't know my Excel. Thanks I have it sorted now.

This is a discussion about <B>TechTip: Download Physical Files Using Microsoft Excel</b>.<p align='center'><a href=http://www.mcpressonline.com/mc? This e-mail address is being protected from spam bots, you need JavaScript enabled to view it @.6b16d2c2>Click here for the article</a>.</p>

I have a VB program that opens an Excel workbook and then refresh the data. It works if I run the VB program, but when I create an .exe file and run it from the AS400 using STRPCCMMD, the refresh doesn't work. What can I do to make sure the refresh data is done before it executes the next line of code? <p>Winnie