Rif.: Cvi and Excel Help ?

[Shako]

Hi All

Is there a way of writing to an Excel Spread sheet when that same spread sheet is open with windows explorer?

Something like a shared connection.

Kind Regards

Shako

[RobertoBozzolo]

You can obtain a handle to an active Excel application and connect to it, reading and writing to the active sheet.

As an example, I modified Excel2000dem sample application that comes with CVI to obtain this behaviour: modifications are in this callback:

//----------------------------------------------------------------------------
// OpenAppFile
//----------------------------------------------------------------------------
int CVICALLBACK OpenAppFile (int panel, int control, int event,
        void *callbackData, int eventData1, int eventData2)
{
    HRESULT error = 0;
    char fileName[MAX_PATHNAME_LEN];
    
    switch (event) {
        case EVENT_COMMIT:
            if (!ExcelWorkbooksHandle)
            {
                // Get Workbooks    
                error = Excel_GetProperty (ExcelAppHandle, NULL, Excel_AppWorkbooks, 
                    CAVT_OBJHANDLE, &ExcelWorkbooksHandle);
                if (error<0) 
                    goto Error;

// EXCLUDED LINES IN CASE OF CONNECTION TO AN ALREADY OPENED FILE
                // Open existing Workbook
//                GetProjectDir (fileName);
//                strcat(fileName, "\\exceldem.xls");
//                error = Excel_WorkbooksOpen (ExcelWorkbooksHandle, NULL, fileName, 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, &ExcelWorkbookHandle);
//                if (error<0) 
//                    goto Error;

//                // Get Active Workbook Sheets
//                error = Excel_GetProperty (ExcelAppHandle, NULL, Excel_AppSheets, 
//                    CAVT_OBJHANDLE, &ExcelSheetsHandle);
//                if (error<0) 
//                    goto Error;
    
//                // Get First Sheet
//                error = Excel_SheetsItem (ExcelSheetsHandle, NULL, CA_VariantInt(1), 
//                    &ExcelWorksheetHandle);
//                if (error<0) 
//                    goto Error;
    
//                // Make First Sheet Active - should already be active    
//                error = Excel_WorksheetActivate (ExcelWorksheetHandle, NULL);
//                if (error<0) 
//                    goto Error;
// END OF EXCLUDED LINES

// New command to obtain a handle to the active sheet                
		error = Excel_GetProperty (ExcelAppHandle, NULL, Excel_AppActiveSheet, CAVT_OBJHANDLE, &ExcelWorksheetHandle);
                if (error<0) 
                    goto Error;

		// Update UIR    
                UpdateUIRDimming(panel);
            }                                  
            else 
                MessagePopup(APP_WARNING, "Document already open");

            break;
    }
    
    return 0;    
Error:
    ClearObjHandle (&ExcelWorksheetHandle);
    ClearObjHandle (&ExcelSheetsHandle);
    ClearObjHandle (&ExcelWorkbookHandle);
    ClearObjHandle (&ExcelWorkbooksHandle);
        
    if (error < 0) 
        ReportAppAutomationError (error);
    
    return 0;
}

To use the example, modify the code as stated, open the document in Excel, run the program and select "Connect to Excel", next select "Open file" (the modified callback) and you can read / write to the file both from interactively and from the program.

[Shako]

Thanks Roberto

It works well.

One other thing is, if there are many opened Excel files how do you write to the one that you want?

[Shako]

Is there a way of checking all opened excel files ?

[RobertoBozzolo · Accepted solution]

This modified callback in excel2000dem sample project lists all opened files in Excel:

//----------------------------------------------------------------------------
// ConnectApp
//----------------------------------------------------------------------------
int CVICALLBACK ConnectApp (int panel, int control, int event,
        void *callbackData, int eventData1, int eventData2)
{
    long	ExcelWorkbooksCount;
    HRESULT	error = 0;
	int		i;
	char	*ExcelWorkbookName;

	switch (event)
        {
        case EVENT_COMMIT:
            // Launch App
            // Connect to existing application if available
            SetWaitCursor (1);
            error = Excel_ActiveApp (NULL, 1, LOCALE_NEUTRAL, 0, &ExcelAppHandle);
            SetWaitCursor (0);
            if (error<0) 
                goto Error;
    
            // Make App Visible
            error = Excel_SetProperty (ExcelAppHandle, NULL, Excel_AppVisible, CAVT_BOOL, appVisible?VTRUE:VFALSE);
            if (error<0) 
                goto Error;

	    // Get number of open files
	    error = Excel_GetProperty (ExcelAppHandle, NULL, Excel_AppWorkbooks, CAVT_OBJHANDLE, &ExcelWorkbooksHandle);
            if (error < 0) goto Error;
			error = Excel_GetProperty (ExcelWorkbooksHandle, NULL, Excel_WorkbooksCount, CAVT_LONG, &ExcelWorkbooksCount);
            if (error < 0) goto Error;

	    // Iterate through files and get file name
	    for (i = 1; i <= ExcelWorkbooksCount; i++) {
		error = Excel_WorkbooksItem (ExcelWorkbooksHandle, NULL, CA_VariantInt(i), &ExcelWorkbookHandle);
	        if (error<0) 
    	            goto Error;
		error = Excel_GetProperty (ExcelWorkbookHandle, NULL, Excel_WorkbookName, CAVT_CSTRING, &ExcelWorkbookName);
                // Print file name
		DebugPrintf ("Workbook %d: %s\n", i, ExcelWorkbookName);
                // Free resources
                ClearObjHandle (&ExcelWorkbookHandle);
		CA_FreeMemory (ExcelWorkbookName);
                if (error<0) 
            	    goto Error;
	    }

            UpdateUIRDimming(panelHandle);
            MakeApplicationActive ();
            excelLaunched = 0;
            break;
        }
    return 0;   
Error:    
    if (error < 0) 
        ReportAppAutomationError (error);
        
    return 0;
}

[Shako]

Thanks alot Roberto.

I had something similar but i didnt not know how to iterate to the next workbook.

Thanks again.

[David_Lee]

Hi Roberto,

When finishing the calling of the execl Activex, the execl.exe process will alway stay in the task manager list, how to make it quit when the calling finished?

David

[RobertoBozzolo]

While interfacing with Excel is crucial that each and every object you created in code is properly disposed of after use, as you can see in the Excel demo I was taking code from in this thread. If an instanvce of Excel remains active in the task manager after your program closes it means that there is an activeX object that is left unfreed in your application. You must double check that every call that obtains an handle or other activeX object is matched with the proper close or deallocate call. ActiveX is annoyingly fussy in this respect and nobody will take care of that for you: neither closing CVI will dispose of handles or other objects, you must do this in the code.