Transfer of data from AS/400 DDS Described file to Excel spreadsheet

20 pts.
Microsoft Excel
Microsoft Excel 2003
Microsoft Office
I have a critical problem in AS/400 data fetch query in MS Excel (Petch SP2 loaded).

I have created several Excel Sheets (in MS Office 2003) for fetching the data from AS400 using MS Office Query option under Data-->> Refresh Menu. Those excel sheets are using by so many different users on their desktops. I have the following problems/doubts in those Excel sheets.

  1. Same Excel sheet which is created on my PC and forwarded to the 2 different users are behaving differently like in My PC while doing Data-Refresh, doesn't prompt for user/password but on different PC it prompts. Even if i understand that my user name and password has save somewhere in my PC then another Excel sheet which is created on different machine and forwarded to me and then when I try to do Data-Refresh on this sheet it is prompting the User/Password, which indicates that the User/Password saves somewhere may be in Excel sheet also . I have tried several ways to resolve the issue, but still the problem persists.
  2. How I can set the Default User in the Excel Sheet.
I have tried with so many options like change the default user in Operation navigator, change the REGEDIT also, and so on.

But still I didn't get any solution on the above problem. I have created the excel sheets where the Excel sheet fetch the data almost 20 to 30 times. Therefore the user has to supply the user/password 20-30 times which is ridiculous.

Please let us know what is the mystery behind it.

Shailesh Mahajan

Answer Wiki

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

We use the IFS on the AS400. We map a drive to a given directory (i.e. /TRASH) from the PC. A job runs on the AS400 (CL command) will extract data to a file (work file or copy of a production file in qtemp). After the extract is done, the CL command (CPYTOIMPF FROMFILE(QTEMP/ADDRFINAL) TOSTMF(‘/trash/addrfinal.csv’) STMFCODPAG(*PCASCII) RCDDLM(*CRLF)) is run. The PC user can now load this “.CSV” into EXCEL w/o any problem. The PC file will be in the same format as the DDS fields. We have all sorts of directories for every department in the company (including a “TRASH” directory which is cleared each night). If a user needs a file uploads (“.CSV” format) , IT will have to set up a DDS file (or SQL table) and use the CL command “CPYFRMIMPF”. HOPE THIS HELPS ==> Michael Dratwa

Calle says
This is probably the best way to create excel in a simple way, because you will have passed all autorisations on the iSeries and you will have full control over the creation. It is also very easy to create a csv-file in the IFS even with simply head lines from a DFPFFD command
Get on with it!

Discuss This Question: 2  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.
    Hi, i seems that your problem is not an Excel problem but a I5 connection Setup issue. Normally when you have defined your Queries, you have specifiied the system name. this system has normally been defined using Operation navigator with some options like : - default userid. I've noticed that if inside excel, when you are prompted from ODBC driver to specify userid and password, you use this default userid and the default password, you'll never be asked for the password again as long as you remain logged-on with you pc. If you use another userid and password, you'll be asked for the user and password every time excel will launch another SQL request, and this can be anoying. One easy way for that, is to use a default ODBC userid and password for such a connection. but the best solution, is to use the userid one, defined in Op nav. Hope it may help you.
    470 pointsBadges:
  • TomLiotta
    Be extremely careful when attempting to embed any profile/password combination in anything like an Excel spreadsheet. You are distributing valid profile/password combinations to anyone who gets a copy of the spreadsheet. Tom
    125,585 pointsBadges:

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.

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


Share this item with your network: