Testing and debugging spreadsheets

Test the demo spreadsheet

Before using your spreadsheet with Symphony, debug it with VBAMacroTest.xls. The macro in this spreadsheet performs the exact same steps as the ConnectorForExcel service.

  1. Register ConnectorForExcel.dll.

    regsvr32 ConnectorForExcel.dll

  2. In Excel, open %SOAM_HOME%\5.0\Integrations\ConnectorForMsExcel\samples\spreadsheets\VBAMacroTest.xls.
  3. Ensure the ConnectorForExcel.dll is referenced. The VBA code in VBAMacroTest.xls requires the ConnectorForExcel.dll COM object to be properly referenced because it attempts to create an instance of that COM object.
    1. Select Tools > Macro > Visual Basic Editor.
    2. In the Visual Basic Editor window, select Tools > References.

      The VBAProject dialog box opens.

    3. In the dialog, select the "ConnectorForMSExcel 1.0 Type Library" to place a checkmark in the checkbox.
  4. Test that VBAMacroTest.xls works with the demo spreadsheet.
    1. In VBAMacroTest.xls, specify parameters to test the spreadsheet:
      1. Name of spreadsheet to test: ConnectorForExcelDemo.xls

      2. Absolute path to the directory that contains the spreadsheet to test:

        For example: %SOAM_HOME%\5.0\Integrations\ConnectorForMsExcel\samples\spreadsheets

    2. Click the button Excecute Macro.

      You should see the following output:

Test your spreadsheet

Open VBAMacroTest.xls and specify parameters for your spreadsheet:
  1. Name of spreadsheet to test: Specify the name of your Excel spreadsheet.

  2. Name of VBA macro to test: Indicate the macro to execute.

  3. Fill in the remaining fields as required.

Debug your spreadsheet

  1. In VBAMacroTest.xls, use the VBA debugger to stop in the Sub CommandButton1_Click() line, and check step-by-step what ConnectorForExcel.dll returns in the following statements:
    • result_start = TestExcelRunnerDemo.StartExcel(pid)

    • result = TestExcelRunnerDemo.ExecuteMacro(SheetName, MacroName, Param, PathPrefix)

    • result_quit = TestExcelRunnerDemo.QuitExcel()

  2. Examine the dialog box messages raised during execution of your macro and eliminate their causes.
  3. Consider your current memory threshold setting in Excel, and ensure it is set at a level that will allow macro execution. See Troubleshooting section for details.