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.
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 !