Update excel charts auto from as400 data

150 pts.
Tags:
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

Answer Wiki

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

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

Discuss This Question: 1  Reply

 
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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following