ActiveX Excel - Add A New Worksheet To An Open Workbook

[scottrod]

Looking for example code to add a new worksheet to an open workbook in Excel 2013. Using CVI 2015.

I programatically got the Excel app opened, a workbook file loaded with multiple sheets, I'm reading from those existing sheets and indexing them for access, etc., I just can't figure out how to add a new sheet and then name it so I can write data to it.

Sadly, none of the CVI examples have that particular functionality in the code that I can find.

The help info for the exel2000.fp for the Excel_NewWorksheet( ) function states:

NOTE: Due to an unusual behavior in Microsoft Excel the object handle obtained from this function actually refers to the workbook object that contains the worksheet. Passing this workbook object handle to worksheet functions will result in a "No such interface supported." error (-2147467262). You must index into the worksheets collection of the workbook object to obtain an object handle for the worksheet. Use this function to create a new _Worksheet object, and obtain a handle to the object.

As helpful as that is, I can't get it to work. Can someone please provide a solution?

TIAA!

[saki.BOMB]

Hi scottrod,

I found these posts, it might be helpful....or not.

Generating Microsoft Excel Reports with LabWindows/CVI

How to Create a New Microsoft Excel Worksheet in LabWindows/CVI

Cheers,

[RobertoBozzolo · Accepted solution]

Hi scottrod,

the examples provided by Saki both refers to Excel Report instrument, which it seems to me you are not using.

If this is true, then you can use the following lines to add a sheet to an open workbook:

// Get Active Workbook Sheets
errChk (Excel_GetProperty (ExcelAppHandle, NULL, Excel_AppSheets, CAVT_OBJHANDLE, &ExcelSheetsHandle));

// Get a handle to the first sheet
errChk (Excel_SheetsItem (ExcelSheetsHandle, NULL, CA_VariantInt(1), &ExcelWorksheetHandle));
errChk (Excel_WorksheetActivate (ExcelWorksheetHandle, NULL));

// Operate on the first sheet
// ...

// Close the reference to previously opened sheet
errChk (ClearObjHandle (&ExcelWorksheetHandle));

// Add a new sheet
errChk (Excel_SheetsAdd (ExcelSheetsHandle, NULL, CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL, &ExcelWorksheetHandle));
errChk (Excel_WorksheetActivate (ExcelWorksheetHandle, NULL));

// Operate on the new sheet
// ...

[scottrod]

Thanks Roberto, perfect.