TechTip: MS Excel--They Gotta Have It!

[S.Mildenberger]

** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
This has actually been available back to at least V4R5 via a PTF. Scott Mildenberger

[Guest.Visitor]

** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
MC WebMaster wrote: > This is a discussion about TechTip: MS Excel--They Gotta Have It!. Ick, this puts a lot of work on the user and reduces the reusability of a spreadsheet. I have found great success by creating MS Query's into the spreadsheet. This way the data will already be formatted for the user and, for many queries, they only have to refresh the data to get the current statistics. Parameterized queries add even more power to the user. If you were to show a group of users the right query and then show them the results of processing this data through a pivot table, you could experience your first standing ovation. Bill

[dchristie]

** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
The article is bang on concerning Excel. We have implemented a similar method as described in the article and it is the preferred method for reports. It's come to the point where the users will demand an option to email the CSV to them when we create a new report. Most of our reports (i.e ATB's etc) are run during the evening, zipped and emailed to the recipient for the next morning. We also use this technique for outside clients, vendors etc. Most don't believe it comes from the old green screen AS400 (actually the zip and encryption comes from the PC, but I give credit to the AS400). For physical files, we run a few commands to insert the file fields into a work file, then copy the file to the work file. This way headings will also be sent. For spooled files, we format the output with commas (creating a CSV file in the PRTF), copy it and email the results. The spooled file method gives you complete control over the layout, but can be messy. Except for my PC, I've stayed away from MS Query, we would have a difficult time installing it on each PC (the add on is not standard), and I believe you must also configure ODBC (may be wrong there). But Bill is correct in that it is a great way to present data. Great article in reminding everyone of what is a very powerful business tool, I mean the AS400 of course, but so is Excel! Thanks

[Guest.Visitor]

** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
David Abramowitz wrote: > As I stated in the article, there are many ways to accomplish the > goal of creating the spreadsheet, but fewer ways of automating the > process. Agree somewhat. > There is nothing wrong with using MS Query, or Crystal Reports, or > other means to obtain the data when the user is there, but delivering > the spreadsheet on an automated basis, i.e. without any user > interaction, is a different issue. But sending a delimited file is not sending a spreadsheet, it's just sending a data file - there aren't any column headers, the print page isn't formatted, the data columns might be of the wrong format and no further data manipulation can occur without human intervention - unless you are coding these things in a Visual Basic for Applications macro. Spreadsheets can be automated to do just about anything through the use of VBA. You could even build a spreadsheet that would automatically download the data and then have it email itself without any intervention. Bill

[Guest.Visitor]

** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
David, You mention the time required to set up MS Query on each desktop and what is necessary to get the ODBC driver setup as well. Compare this to the time required to create and email the CSV to each user that desires it. With Query set up and the ODBC set up, they could then refresh the data themselves without any involvement from you. If your users are "addicted" to the CSV format, I could just about guarantee they'll drop that addiction for the MS Query method. Try it out on just a couple at first and see what they think. Bill David Christie wrote: > The article is bang on concerning Excel. We have implemented a > similar method as described in the article and it is the preferred > method for reports. It's come to the point where the users will > demand an option to email the CSV to them when we create a new > report. > > Most of our reports (i.e ATB's etc) are run during the evening, > zipped and emailed to the recipient for the next morning. We also use > this technique for outside clients, vendors etc. Most don't believe > it comes from the old green screen AS400 (actually the zip and > encryption comes from the PC, but I give credit to the AS400). > > For physical files, we run a few commands to insert the file fields > into a work file, then copy the file to the work file. This way > headings will also be sent. > > For spooled files, we format the output with commas (creating a CSV > file in the PRTF), copy it and email the results. The spooled file > method gives you complete control over the layout, but can be messy. > > Except for my PC, I've stayed away from MS Query, we would have a > difficult time installing it on each PC (the add on is not standard), > and I believe you must also configure ODBC (may be wrong there). But > Bill is correct in that it is a great way to present data. > > Great article in reminding everyone of what is a very powerful > business tool, I mean the AS400 of course, but so is Excel! > > Thanks

[Guest.Visitor]

** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
I copied a PF to a directory in the IFS file in '.csv' format and it works fine and I am able to open it in EXCEL, but I am not able to copy to a folder in 'QDLS/myfolder/myworksheet.csv'. How to copy a PF directly to a folder. I have a batch program that converts PF to Excel and I want it to send using SNDDST but it prompts for folder not directory. Any help will be greatly appreciated. Sky

[S.Mildenberger]

** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
QDLS only supports the 8.3 naming standard, shorten your name to fit in this and it should work. Scott Mildenberger

[dchristie]

** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
We use the CPYTOPCD or CPYTOIMPF commands. If you copy to QDLS, the file name must be a total of 12 characters. V5R1 example

 Chgvar &DOCNAME ('PR' |< &JOBNBR |< '.XML') Chgvar &TOSTMF ('/QDLS/TEMP/' |< &DOCNAME) CPYTOIMPF FROMFILE(QTEMP/FILE) TOSTMF(&TOSTMF) + MBROPT(*ADD) STMFCODPAG(*PCASCII) + RCDDLM(*CRLF) STRDLM(*NONE) 

or

 Chgvar &DOCNAME ('SF' |< &JOBNBR |< '.CSV') CPYTOPCD FROMFILE(QTEMP/SPLF) TOFLR(TEMP) + TODOC(&DOCNAME) REPLACE(*YES) 

[Guest.Visitor]

** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
David Abramowitz wrote: > Perhaps you would like to discern your methods in a form of a tech > tip, so that all readers could share your technique. Then he wouldn't have a product to sell. Bill

[Guest.Visitor]

** This thread discusses the article: TechTip: MS Excel--They Gotta Have It! **
"The users are home in bed sleeping when the jobs are run." I liked your idea of automation. I have used CSV types before, but with user intervention, dealing with CPYTOIMPF and using FTP together to get my CSV. May I ask, where does CPYFRMQRYF comes in? How do you automate it? Thank you for sharing your tech-tips. It is greatly appreciated. GOD BLESS! =) Joan