AS/400 and Excel
Solved
alain
-
chickyta -
chickyta -
Hello,
Is it possible to import an AS/400 document in
.xls format without copying and pasting?
Thank you very much for your help.
alain
Is it possible to import an AS/400 document in
.xls format without copying and pasting?
Thank you very much for your help.
alain
7 answers
If you are using an AS400 and a PC, you are probably also using Client Access or an equivalent (Rumba, etc.). These emulators have a feature that allows you to transfer files (both ways) and even convert them to Excel format. You can find your AS400 table in EXCEL with the field names in row A of Excel... be careful with the size of the extractions, EXCEL has its limits!
From AS400
It is necessary to convert the AS400 file to ASCII format and "store" it in the IFS (Integrated File System) directories of the AS400 using the command
CPYTOIMPF FROMFILE(Library/File) +
TOSTMF('/QDLS/Directory/Example.CSV') +
STMFCODPAG(*STDASCII) RCDDLM(*CRLF) +
FLDDLM(';') DECPNT(*COMMA)
Afterward, from AS400, (if the settings are done on AS400) you can send the file as an email attachment
SNDDST TYPE(*DOC) TOINTNET((toto.tata@titi.fr)) +
DSTD(Text) MSG(text) +
DOC(example.csv) FLR(directory)
Otherwise, from Excel, if iSeries Navigator has been installed in Data, external data, create a query, select the appropriate ODBC iSeries Navigator link.
It is necessary to convert the AS400 file to ASCII format and "store" it in the IFS (Integrated File System) directories of the AS400 using the command
CPYTOIMPF FROMFILE(Library/File) +
TOSTMF('/QDLS/Directory/Example.CSV') +
STMFCODPAG(*STDASCII) RCDDLM(*CRLF) +
FLDDLM(';') DECPNT(*COMMA)
Afterward, from AS400, (if the settings are done on AS400) you can send the file as an email attachment
SNDDST TYPE(*DOC) TOINTNET((toto.tata@titi.fr)) +
DSTD(Text) MSG(text) +
DOC(example.csv) FLR(directory)
Otherwise, from Excel, if iSeries Navigator has been installed in Data, external data, create a query, select the appropriate ODBC iSeries Navigator link.
Import your AS400 data into Access, then click on Tools, select Office Links, and analyze with Excel, there you go!
Hello, I have a problem installing Excel on my PC. I'm not very skilled with computers and my PC runs on XP Home, so if any of you could give me a little help, that would be nice.
Thank you in advance to you all.
Thank you in advance to you all.
Hello,
can I initiate an automatic transfer from an AS400 database to my PC using an Excel file through a VBA macro (based on a .dtf query)?
In fact, I have an Excel spreadsheet where I retrieve information through formulas from a second sheet that I populate with an AS400 table that I transfer each time (for update reasons). I would like this AS400 table to automatically refresh upon opening my Excel workbook. Therefore, I am considering a macro.
Thank you in advance.
can I initiate an automatic transfer from an AS400 database to my PC using an Excel file through a VBA macro (based on a .dtf query)?
In fact, I have an Excel spreadsheet where I retrieve information through formulas from a second sheet that I populate with an AS400 table that I transfer each time (for update reasons). I would like this AS400 table to automatically refresh upon opening my Excel workbook. Therefore, I am considering a macro.
Thank you in advance.
To easily generate an Excel table and automate all processes, visit here: http://www.easycom-aura.com
This solution allows you to use all the features of Excel while accessing DB2/400.
Have a nice day everyone.
This solution allows you to use all the features of Excel while accessing DB2/400.
Have a nice day everyone.