PQ Systems Knowledge Base

Revision Date: 2005-11-07

Problem:

Under some circumstances you may experience problems when using Microsoft Excel to open or edit a workbook file at the same time that CHARTrunner is using that same workbook file. The issues discussed here do not apply to CHARTrunner-XL.

This issue can be influenced by many factors, such as which program (CHARTrunner or Excel) is first used to open the workbook.

Here is a typical problem: You open the workbook .XLS file (that is not in “shared workbook” mode) using Excel. Then in CHARTrunner you display a chart that fetches its data from the same workbook (it doesn’t matter whether the chart definition has “Close data source after drawing chart” checked or not). Next, you close Excel via File > Exit. Finally, in Windows Explorer you double-click on the workbook file which will attempt to open the workbook in Excel. However, instead of successfully opening the workbook, the Excel window appears and then freezes or locks up and the contents of the workbook never appear in the Excel window. In order to recover from this situation you have to exit from both CHARTrunner and Excel. Oddly, this problem doesn’t occur if instead of opening the workbook via Windows Explorer you start Excel and then open the workbook.

Another typical problem occurs when your open CHARTrunner chart does not see changes that you make to the worksheet using Excel. In this scenario, you have the workbook open in Excel, make changes to existing data, or add new data, and save the changes to the workbook, but the open CHARTrunner chart doesn’t reflect those changes when the chart is refreshed (either by pressing F5 to manually refresh the chart, or the chart automatically refreshes itself due to the “Refresh interval” specified in the chart definition).

Solution:

Here are some steps you can take to fix these types of problems:

1. In Excel mark the workbook as “Shared” so that Excel is more tolerant of concurrent access to the workbook by both Excel and CHARTrunner. By default Excel workbooks are not created in “shared” mode. In order to place an open workbook in “shared” mode click on Tools > “Share Workbook…” and put a check in the box to “Allow changes by more than one user at the same time”.
   
2. In CHARTrunner ensure that the “Close data source after drawing chart” checkbox is checked on the “Misc” tab of the chart definition.

If you still experience a problem after implementing the two steps mentioned above, then try using an alternate data access type to get to your Excel data. CHARTrunner can get to Excel data via two different data access methods by specifying on the “Data source” tab of the chart definition a “Type” of either:

1. “Microsoft Excel” (which uses DAO/Jet).
   
2. “ADO; OLE DB provider” along with the “OLE DB Provider for Microsoft Jet - Excel 97/2000” connection string (which uses ADO/Jet). See below for more details.

Getting to Excel Data via the ADO; OLE DB Provider

You can use ADO to get to Excel data as follows:

Edit the CHARTrunner chart definition and select the "Data source" tab.

In the Type field select: ADO; OLE DB provider

In the ADO OLE DB Provider field select:
     OLE DB provider for Microsoft Jet - Excel 97/2000

In the Connection String edit the supplied template to specify the full path to the Excel workbook file, for example:

Provider=MicrosoftJetOLEDB.4.0;Data Source=C:\MyData\MyWorkbook.xls; Extended Properties="Excel 8.0;HDR=Yes;"

You will need to enter the full path to your Excel workbook file in place of the C:\MyData\MyWorkbook.xls as shown in the example above.

Notice that if you use ADO and draw the chart first, you may not be able to open the worksheet using Microsoft Excel. You may need to first open the worksheet using Microsoft Excel and then draw the chart in CHARTrunner.