Automatic transfer of Data from AS/400 DDS Described file to Excel spreadsheet
I have a critical problem in AS400 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 MSOffice 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 radiculous.
Please let us know what is the mystery behind it.
Regards,
Shailesh Mahajan (Section Manager-MIS)
BRIDGESTONE INDIA PRIVATE LIMITED
Pithampur, Distt Dhar
Software/Hardware used:
Software/Hardware used:
ASKED:
June 7, 2008 4:08 AM
UPDATED:
April 25, 2010 10:03 AM
Answer Wiki:
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!
/Calle
To see all answers submitted to the Answer Wiki: View Answer History.
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.
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