trying to add a excel worksheet

Question

5.00/5 (1 vote)

See more:

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 9-Jul-12 8:28am

zlazic

Updated 23-Nov-16 18:54pm

Add a Solution

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.

4 solutions

Solution 4

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.

Posted 9-Jul-12 10:59am

armagedescu

Solution 5

_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);

Posted 23-Nov-16 18:54pm

JeongChan.Kim

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Volynsky Alex · Accepted Answer · 2012-07-09T09:39:00

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[^]

enhzflep · Accepted Answer · 2012-07-09T09:53:00

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;
}

trying to add a excel worksheet

4 solutions

Solution 1

Solution 3

Solution 4

Solution 5

Add your solution here

Preview 0