Home > IT Answers > AS/400 > Update excel charts auto from as400 data
Gerrib
150 pts.
 Update excel charts auto from as400 data
AS/400, AS/400 import/export, Excel Charts, Microsoft Excel
I have a user request to create charts in excel using data held on the as400. I can do this in seperate steps but the user wants it as automatic as possible. At the moment : I run a query on the as400 to create data file I transfer using client access I open in excel & then copy data to graph excel spreadsheet on eorksheet 1 and graph is updated. Any ideas how to do this so it would require the least amount of user intervention. Thanks a million

Software/Hardware used:
ASKED: June 9, 2008  11:43 AM
UPDATED: June 13, 2008  3:24 PM
RELATED QUESTIONS
Answer Wiki:
well.... Pretty more complicated but it worth it: Here we go. First, you havw to build an ODBC link between your AS/400 and your PC handling your Excel spreadsheet. Then, ... Instead of running a query on your AS/400, build up and SQL within the data transfer of Excel spread sheet: - In your Excel Menu bar, choose "Data", "External Data", "Create request" (sorry if it is not the right terms but I have a french version of Excel) - Then, choose your ODBC definition pointing to your AS/400 to get the information. - Choose all the fields you need, even if you dont have them all. - Press Next until the the last windows asking you what to do in the result. On this window, select the option where you can show data or modify the Microsoft Query SQL request. - The result of you initial request will then be shown - Add up to the SQL instruction if requested to complete your selection - Save the request on your PC or Lan Disk There you go, you now can extract all the data you need to create your chart on your Excel. Now to automate it: - Start the macro recorder On excel and run the saved SQL request (by doing "Data", "External Data", "Modify request" (sorry if it is not the right terms but I have a french version of Excel) The Excel should refresh your information on screen. - Stop recording and name your Macro "Auto_Open ". This will make your Macro run whenever you open your Excel spreadsheet. Now building your Macro may not contain only your data download but also the build up of your chart so you can complete all the excel moves and calculation that you need to finally get your chart. Have fun ! ______________________ DanTheMan
Last Wiki Answer Submitted:  June 13, 2008  3:24 pm  by  Bigmac46   1,000 pts.
All Answer Wiki Contributors:  Bigmac46   1,000 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

This topic has been covered on Search400.com.
Transfer iSeries files into Excel using Client Access

This is a Ask the Expert response that follows up the article.

Hope this helps!

Leah Rosin
 490 pts.