Click here to Skip to main content
15,512,303 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
I have an OLE Autmation c++ project where i'm using #import statements to interact with an Excel application.
I'm having trouble adding a new worksheet AFTER the current (active) worksheet.

the following call will prepend a new tab:
Add(vtMissing, vtMissing, (long)1, (long)xlWorksheet)

but I don't know what the 2nd argument should be for the new sheet to append.

The excel object model reference states it should be:

"An object that specifies the sheet after which the new sheet is added."

What does this mean? Anyone have any ideas?
Posted
Updated 23-Nov-16 19:54pm
Comments
[no name] 9-Jul-12 14:46pm    
Try replacing the 2nd parameter with the index of the sheet that you want to insert the new sheet. If your active sheet is index 1 then try 1. Alternatively you can use the Move function to move the sheet to where you want it to go.

Try to use something:

Excel::_ApplicationPtr XL;
....

Excel::_WorkbookPtr workbook = XL->Workbooks->Add(Excel::xlWorksheet);
Excel::_WorksheetPtr worksheet = XL->ActiveSheet;
worksheet->Name = "last page";

worksheet = XL->Worksheets->Add(); // adding worksheets!!
worksheet->Name = "other page";

worksheet = XL->Worksheets->Add();
worksheet->Name = "some page";

worksheet->SaveAs("c:\\test.xls");
workbook->Close();
XL->Quit();

More see here:
http://stackoverflow.com/questions/5308464/create-multiple-excel-sheet-in-vc[^]


Or you can read following articles, I hope it will help you:

Accessing Excel Spreadsheets via C++[^]

http://www.maths.manchester.ac.uk/~ahazel/EXCEL_C++.pdf[^]

http://blogs.technet.com/b/heyscriptingguy/archive/2005/12/15/how-can-i-add-additional-worksheets-to-an-excel-workbook.aspx[^]

http://msdn.microsoft.com/en-us/library/ff196568.aspx[^]
 
Share this answer
 
Comments
enhzflep 9-Jul-12 16:01pm    
Doesn't this code just add a new sheet after the active sheet - i.e between the first and second sheets?
Y.Desros 10-Jul-12 15:02pm    
Exact answers here
Thanks Alex for your inputs/examples!
Rather than indexes into the Sheets collection, you need to supply the function valid worksheet objects.

Firing up a new instance of excel and recording 2 macros, we get the following code:

1. Sheet added between Sheet2 & Sheet3 (right-click sheet 3, add new worksheet)
VB
Sheets("Sheet3").Select
Sheets.Add


2. Sheet added at the end, after all sheets
VB
Sheets.Add After:=Sheets(Sheets.Count)


A c++ snippet I just bashed together that achieves #2:
(remembering that we have to pass the args in reverse order for COM calls)
C++
// Get Workbooks collection
IDispatch *pXlBooks;
pXlBooks = getIDispatchVal(pXlApp, L"WorkBooks");

// Call Workbooks.Add() to get a new workbook...
 IDispatch *pXlBook;
 pXlBook = getIDispatchVal(pXlBooks, L"Add");

 IDispatch *pXlSheets;
 pXlSheets = getIDispatchVal(pXlBook, L"Worksheets");


 int curBookSheetCount = getCount(pXlSheets);
 IDispatch *aftrSheet = getItem(pXlSheets, curBookSheetCount);
 IDispatch *pNewSheet;
 {
     VARIANT result, var1,var2;
     VariantInit(&result);

     var1.pdispVal = NULL;
     var1.vt = VT_NULL;

     var2.pdispVal = aftrSheet;
     var2.vt = VT_DISPATCH;

     AutoWrap(DISPATCH_METHOD, &result, pXlSheets, L"Add", 2, var2, var1);
     pNewSheet = result.pdispVal;
 }


A couple of my helper functions:
C++
int getCount(IDispatch *pCollection)
{
    VARIANT result;
    VariantInit(&result);
    result.pdispVal = NULL;
    AutoWrap(DISPATCH_PROPERTYGET, &result, pCollection, L"Count", 0);
    return result.lVal;
}

IDispatch *getItem(IDispatch *pCollection, int index)
{
    VARIANT result, param1;
    VariantInit(&result);
    result.pdispVal = NULL;
    param1.vt = VT_I4;
    param1.lVal = index;
    AutoWrap(DISPATCH_PROPERTYGET, &result, pCollection, L"Item", 1, param1);
    return(result.pdispVal);
}

// EDIT: I forgot this one.
IDispatch *getIDispatchVal(IDispatch *pObject, wchar_t *valName)
{
    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pObject, valName, 0);
    return result.pdispVal;
}
 
Share this answer
 
v2
You will likely have many such type of questions. When working with automation, is better to make small test functions in VBA, just to see how it works. And after that do the same thing in C++. By doing like this you will understand much faster the documentation, the type library and what are exactly exactly the steps to do.
 
Share this answer
 
_variant_t varBefore = vtMissing;
_variant_t varAfter = vtMissing;
_variant_t varCount = vtMissing;

int nCurSheetCount = pBook->Sheets->GetCount();
int nTotalSheet = 5; // to be changed.

varCount = nTotalSheet - nCurSheetCount;
pSheet = pBook->Sheets->Item[nCurSheetCount];

varAfter.vt = VT_DISPATCH;
varAfter.pdispVal = pSheet;

pSheet = pBook->Sheets->Add(varBefore, varAfter, varCount);
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900