How To Move A Sheet In Excel

[scottrod]

I created a new sheet in Excel. The Excel app does not allow the creation of a new sheet after the last sheet, you have to create a new sheet (the default is 'next to the last') and move it into the last sheet position, which is what I'm trying to do.

The code below ads the new sheet and works fine until the last function call, which returns an HRESULT of -2147467262. Can someone have a look and see what's wrong here?

		// Determine the number of sheets in the workbook
		ret = Excel_GetProperty (ExcelSheetsHandle, NULL, Excel_SheetsCount, CAVT_LONG, &sheetCount);

		// Make a handle for the last sheet in the workbook
		ret = Excel_SheetsItem (ExcelSheetsHandle, NULL, CA_VariantInt(sheetCount), &ExcelSheetHandle);

		// The Move function wants a VARIANT type not an object, so make a variant from the object handle of the last sheet in the workbook.
		ret = CA_VariantSetObjHandle (&LastSheetV, ExcelSheetHandle, CAVT_DISPATCH);

		// Add the new sheet
		ret = Excel_SheetsAdd (ExcelSheetsHandle, NULL, CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL, CA_DEFAULT_VAL, &ExcelSheetHandle);

		// Now move it using the LastSheetV variant in the After position of the SheetsMove function call.
		ret = Excel_SheetsMove (ExcelSheetHandle, NULL, CA_DEFAULT_VAL, LastSheetV);

		// >> ERROR <<	Halt and catch fire.  

Anyone see what's wrong? Still learning ActiveX protocol.

And BTW, where does one find the HRESULT error codes?

[RobertoBozzolo]

(...) The Excel app does not allow the creation of a new sheet after the last sheet, you have to create a new sheet (the default is 'next to the last') and move it into the last sheet position, which is what I'm trying to do.

Hi scottrod,

I don't understand the above sentence: I have no problem in adding sheets after the last one already in the file. In effect, to adapt to various settings customers may have in their Excel (new files created with who knows how many sheets) when creating a new file I ultimately standardized to delete all but one existing sheets, write data in the last sheet remained and then progressively add new sheets as needed with the commmands I reported here, which results in sheets always added at the end of the list.

Are you intending something different?

Are you getting errors in adding new sheets? How many sheets are we speaking about?

[scottrod]

When running the code, it's adding a sheet fine, but at the "next to the last" position rather than the last (which is were I want it). I usually have 8 to 15 sheets in a Workbook.

In searching for an explanation of that behavior, I located the following link where under Remarks, the 'default' behavior is to do that. Sheets.Add Method

Looking further, this link indicated that to add a sheet as the last sheet in VB, you had to add it first, then move it. Figured that was what I needed to do. How To Add a Sheet After Last Sheet in a Single Action 

Now that I know it's possible, will try applying a Variant for the last sheet to the After position in the Excel_SheetsAdd function to see if I can get it to add a sheet after the last.

As it runs now with CA_DEFAULT_VAL in that position, it's adding as next-to-last when using  Excel 2013.

[scottrod]

Ok, got it.

It will add a sheet past the last sheet position when modifying the Excel_SheetsAdd call from above as follows:

		ret = Excel_SheetsAdd (ExcelSheetsHandle, NULL, CA_DEFAULT_VAL, LastSheetV, CA_DEFAULT_VAL, CA_DEFAULT_VAL, &ExcelSheetHandle);

[RobertoBozzolo]

Right! I was running the same exact path but you were faster! 

Just a little note: remember to close LastSheetV reference after you used it like any other handle.

For some reason I never focused that sheets were not created at the end of the workbook! 

Coming to your last question, to decode ActiveX errors you can use CA_GetAutomationErrorString () command. The following function issues a descriptive message for ActiveX errors (mainly taken from excel2000dem example):

//----------------------------------------------------------------------------
// ReportAppAutomationError
//----------------------------------------------------------------------------
void ReportAppAutomationError (HRESULT hr, int line)
{
    char errorBuf[256];
    
    if (hr < 0) {
        CA_GetAutomationErrorString (hr, errorBuf, sizeof (errorBuf));
        sprintf (errorBuf, "%s (line %d)", errorBuf, line);
        MessagePopup ("Severe error", errorBuf);
    }
    return;
}

[richferrara]

The HRESULT error codes are in winerror.h in the Windows SDK folder.  In my LabWindows installation, that's here:

C:\Program Files (x86)\National Instruments\Shared\MSDTRedistributables\SDKHeaderFiles\8.1

The error code you're seeing translates to 0x80004002 = E_NOINTERFACE ("No such interface supported").

You can also use the function CA_GetAutomationErrorString to retrieve the error text associated with any HRESULT code or CA_DisplayErrorInfo to show it in a dialog box.