LabWindows/CVI Excel Macro problem

[rgelbmann]

I am using LabWindows CVI version 8.5 and am trying to call a macro in Excel.  The Excel file name has a space in it and when I call it (using the code below), I get an error (Exception occured).  The error number returned is -2147352567.  If the Excel file doesn't have a space in the file name it works.  What do I need to do?   I have looked on the support forum but haven’t found an answer.
 
Below is the function the Excel2000dem.c sample provided with LabWindows CVI that I modified to call my excel file.
 
 
//----------------------------------------------------------------------------
// RunMacro
//----------------------------------------------------------------------------
int CVICALLBACK RunMacro (int panel, int control, int event,
        void *callbackData, int eventData1, int eventData2)
{
HRESULT error = 0;
 
  switch(event)
    {
    case EVENT_COMMIT:
      SetWaitCursor (1);
 
      // Application.Run "exceldem.xls!NewDataFromMacro"
#if(0)
      //original code supplied
      status = CA_VariantSetCString (&MyVariant, "exceldem.xls!NewDataFromMacro");  
#else
      //This works but if the name is changed to “Macro Test.xls!MoveCell” it doesn’t
      status = CA_VariantSetCString (&MyVariant, "MacroTest.xls!MoveCell");
#endif
      error = Excel_AppRun (ExcelAppHandle, NULL, MyVariant, CA_DEFAULT_VAL,
                            CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL,
                            CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL,
                            CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL,
                            CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL,
                            CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL,
                            CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL,
                            CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL,
                            CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL,
                            CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL,
                            CA_DEFAULT_VAL, CA_DEFAULT_VAL, NULL);
      CA_VariantClear(&MyVariant);
     
      if(status<0) goto Error;
 
      SetWaitCursor (0);
      break;
    }
 
Error:
  SetWaitCursor (0);
 
  if(error < 0)
    {
    ReportAppAutomationError (error);
    }
 
  return 0;
}

[Daniel-E]

Hey rgelbmann, 

Try escaping the space--here's an example:

status = CA_VariantSetCString (&MyVariant, "Macro\ Test.xls!MoveCell");

Hopefully that will work. I suspect that when the CA_VariantSetCString function converts the string to a binary string, it's interpreting the space as a null character and therefore not saving the correct filename. Let us know if that doesn't work and we can look into other possibilities.

[RobertoBozzolo]

Another possibility could be to embed quotes in the string, as you normally do when using filenames with embedded spaces.

status = CA_VariantSetCString (&MyVariant, "\"Macro Test.xls\"!MoveCell");

I also noted that Excel when making references to cells in sheets with spaces in their name embeds the reference in single quotes: e.g. 'Sheet 2'!A1 so you may try this way also in case the prededing ones are unsuccessful. Unfortunately I cannot test any of these suggestions now.

[rgelbmann]

Daniel-E

I tried that and the compiler is giving me the warning of " unrecognized character escape sequence '\' " and then fails.

[rgelbmann]

Roberto,

I tried your suggestions by adding the double quotes around the file name and I got the same result (exception erro).  However, adding a single quote did work!

Thanks for the suggestions!