10.2. Axys graphs don't lay out properly in Excel 2007 or 2010

Note: these instructions were provided by Advent, and are not supported in any way by Trumpet

 

Excel macros do not record many of the changes to chart appearance that were possible in Excel 2003.  However, you can use a chart template in Excel 2007 to make the same changes, and reference the template in your Excel macro. 

A chart template in Excel is a spreadsheet file containing common data and formatting options that is used as a model for other spreadsheets. Excel does not provide a comprehensive list of changes you can make using a chart template. Formatting can include the following:

• Font styles and size.
• Chart type changes.
• Chart layout changes.
• Color changes - changing the fill colors of your chart.
• Moving/Deleting/Resizing the chart legend.

The steps below detail how to create a chart template in Excel for your APX and Axys reports and then how to apply the saved chart template to an Excel macro and then how to reference the Excel macro in an Axys or APX report.

A. Create an Excel Chart from a Report

1. Run a report for which you can create a chart.
2. In the report dialog box, select the Graph check box.
3. Define the chart you want to create, and click OK.
4. Click OK. The report runs.
5. If the chart does not appear on screen, click the "Reports and Charts View" icon to display the chart view.
6. Single-click the chart to display the chart frame (do not double-click).
7. Choose File > Save As.
8. In the "File name" field, enter a name for the chart, 8 characters or less (Example: Graph).
9. In the "Save as type" field, choose "Excel Chart (*.xls)."
10. Click Save to save the report as an Excel chart.
11. Close the report window.

B. Create a Chart Template in Excel

1. In the Windows menu, choose Programs > Microsoft Excel.
2. In Excel, from the Office Button choose open.
3. Open the chart (*.xls) that you saved created from the report in Axys or APX (Step 10).
4. Select the chart area (clicking once on the chart area), this will activate the Chart Tools menu.
5. Make the necessary changes you want to make to the chart appearance. 

• You can change the colors of the series (if using Microsoft Office Ultimate 2007 you can apply a Color Scheme to change all the colors of your graph).
• Move and resize the legend.
• Change the font style and size of the report title.
• Change the chart layout of the report.

1. When changes are completed, click on Chart Tools >Design Menu > select the "Save as Template" button.
2. Save your chart template to the default location (we will discuss how to move the template to shared location so that it can be used by the whole office later in this document.) 
3. You may want to test the template at this point to make sure that your changes apply (you can only do this if you save the template to the default location). 
   1. To do this close the open Axys or APX chart without saving and re-open it. 
   2. Select the chart, click on Charts Tool > Design > select "Change Chart Type."
   3. Select "Templates" from the left side of the screen, and in the My Templates section on the right select the template you just created and click OK. 
   4. You should see all the changes applied to the chart that you had made before.
   5. When you have completed the review of the chart, close the chart file without saving and re-open it.

C. Create a Macro in Excel to Reference your Chart Template

1. With the chart open in Excel make sure that the chart is selected (you can tell because the Chart Tools menu will be activated).
2. Click on the View menu, then Macros > Record Macros.
3. Give the macro a name (i.e. Graph.). You will need to reference this name from when running your report.
4. In the "Store macro in" field, choose New Workbook, and click OK.
5. Click on the Chart Tools > Design > Change Chart Type.
6. Select the templates folder from the left side of the screen, then choose the template you just created (Step B7) from the right side of the screen and click OK.
7. Click on the View Menu > Macros > Stop Recording.
8. From the View Menu choose Switch Windows and select the workbook created by the macro (it will be called Book2).
9. Click on the Office Button > Save As.
10. Make sure you select the type Excel Macro Enabled Workbook (.xlsm) and give it a name and save the file.

D. Using Your Excel Macro in Reports

1. Run your report. 
2. In the report dialog box, select the Graph check box.
3. Click Browse.
4. Choose the name of the Excel Macro-Enabled Workbook (.xlsm) file you created (Step C9) and click OK. The file name appears in the "Excel Macro File" field.
5. In the "Excel Macro Name" field, enter the name of the Excel macro that you created (Step C3).
6. Click OK.
7. Click OK again. The report runs and displays chart with the changes recorded in the Excel macro.

To store the Chart Template in a shared location on a server so that it can be used by the whole office:

1. Follow the steps above.
2. Copy the template you created in part B to the server.  For example, you might create a directory off the APX or Axys share for templates and copy the templates to \\server\apx$\templates or \\advent\axys\exceluse.  Chart templates have the extension CRTX and you commonly find them in your user directory on the machine where you create the chart template.  

• In Microsoft Windows Vista: "Roamings\<USERNAME>\AppData\Romaing\Microsoft\Templates\Charts" where USERNAME is the name of the user who created the template.
• In Microsoft Windows XP:  "c:\documents and settings\<USERNAME>\Application Data\Microsoft\Templates\Charts" where USERNAME is the name of the user who created the template.

1. Open the Excel workbook you saved in Step C10. 

• If you have not enabled all macros in Excel Trust Center then when you open the document you may need to enable macros. To enable all macros all the time go to the Office Button > Excel Options > Trust Center > Trust Center Settings. Then from the left screen choose Macro Settings and Enable all macros. 

1. Go to View > Macros > View Macros.
2. Select your macro and click Edit.
3. You should see a path reference to Roamings\advent\AppData\Romaing\Microsoft\Templates\Charts\template1.crtx.
4. Change it to the new location \\server\apx$\templates\template1.crtx / \\advent\axys\exceluse\template1.crtx and save.
5. Close the macro and test it.