How to automate Excel Charts with multiple queries and parameters

Problem:

You have a bunch of Access reports, need cooresponding charts, but find Excel better for chart creation. You want this process automated, so that you dont have to constantly refresh data for each chart. Your queries are based on a user-defined parameter (date, etc.)

As many know, you cannot pass a parameter from Access to Excel. It won't allow it.

Many also know that if you have multiple parameters in Excel, you are asked every time, even if they are all the same.

Solution: This should consolidate, automate and create and end-user friendly chart process.


1) set up an excel workbook, place data for a chart on one worksheet, and create a chart, to be placed in a new chart sheet.

2) repeat this process for every chart you need, using every query you need.

3) On a new worksheet, make the sheet have a light color background, with no borders for the cells. This will make it look like a menu page.

4) create 3 buttons. You will create 3 modules in code to support these.

4) the first button will be to refresh the data. If your queries have parameters, please see the end of this for more information.

5) the second button will be a preview and the third will be to print the charts.

the preview code will simply be:
chart1.printpreview
chart2.printpreview
etc

the print code will simply be:
chart1.printout
chart2.printout
etc

6) If you do not have any parameters to the queries, the code for the refresh button will simply be : ThisWorkbook.RefreshAll

If you do have any parameters, you will create a user form in code. It is really easy...

-the module for the refresh button will be:
formname.Show

- View code, and in the project window, go to the forms folder and create one. Go over the properties you want for it.

- add a text box to the form and name it.

- add a command button to the form and use this code for the onclick:

Workbooks("name.xls").Worksheets("sheetname").Activate
Range("A1").Activate
ActiveCell.Value = textboxname.Value
ThisWorkbook.RefreshAll
UserForm1.Hide

I am doing this to make the text box fill a cell, and have all of the data sheets reference that cell for their parameter. This code makes a worksheet active, calls cell A1 on that sheet, and makes it = the text that the user just put into the text box. I used the main menu to place my parameter.

- if you have different parameters for each chart, you will need to repeat/add text boxes and references.

- This code will fill the users text box data into the cell, refresh all external data and hide the user form.

Now why did we put something (hidden) into A1 of the menu sheet?

For each data sheet, we have a Microsoft Query attached. In each query, we have our parameter in the criteria. As you know, if you refresh all data pages as is, you will be asked for the parameter for as many times as you have queries asking for it in this workbook.

- In each data sheet, put the cursor on a cell that is in the data range, and select the toolbar button, which looks like two tiny oxes on the left corner and a question mark in brackets. This is part of the external data toolbar.

- It is the criteria button. Change the radial to "Get the value from the following cell:"
Then point to your A1 cell.

Once this is done, you should be well on your way to automated excel charts, with a simple menu sheet for updating your charts.

This will take some customizing based on different parameters, form properties and such.

Hope this helps a lot of people!