Trouble creating new worksheet in Excel file

I am trying to create a new sheet in my Excel file, but keep getting an "OLE method failed" error.  I had found another function that added sheets and I tried to use that method, but I still get the error.  Here is the code I'm using:

if (numSheetsCreated >= 3) {
    checkRes(oleGet(objExcel, cPropertyWorkbooks, objWorkbooks));
    checkRes( oleMethod( objWorkbooks, cMethodAdd ) );
}

That is the extent of the method to create a sheet.  I check numSheetsCreated for >= 3 because a default Excel file automatically gives you 3 sheets to start with, but I get the error on the oleMethod function call and I don't know what the problem is.  I'm hoping someone can help me figure this out.

Chris

Re: Trouble creating new worksheet in Excel file
Mike.Scharnow - Thu May 16 14:53:46 EDT 2019

hm, probably the same problem as here? https://www.ibm.com/developerworks/community/forums/html/topic?id=e7749444-1edd-4592-ba22-f71c9588d0a6&permalinkReplyUuid=cec1bf68-04b5-461e-9e61-19260c381e9d, i.e. you have to add a sheet to the sheets, not to the workbooks

Re: Trouble creating new worksheet in Excel file
Christopher Cote - Thu May 16 15:27:12 EDT 2019

Actually, this is different from the other post.  So how would I add a sheet to the sheets collection?  Would it be:

if (numSheetsCreated >= 3) {
    checkRes(oleGet(objExcel, cPropertySheets, objSheets));
    checkRes(oleMethod(objSheets, cMethodAdd));
}

or am I writing the add function wrong for sheets?

Chris

Re: Trouble creating new worksheet in Excel file
Mike.Scharnow - Fri May 17 05:50:54 EDT 2019

if (numSheetsCreated >= 3) {
    checkRes(oleGet(objExcel, cPropertySheets, objSheets));
    checkRes(oleMethod(objSheets, cMethodAdd));
}

No, have a look again at the script at https://www.ibm.com/developerworks/community/forums/html/topic?id=e7749444-1edd-4592-ba22-f71c9588d0a6&permalinkReplyUuid=90fb57dd-eb50-495d-b1cf-9649a803b1b9

The chain is: Excel -> Workbooks -> Workbook -> Sheets -> Add -> getActiveSheet -> post data to the sheet

The only differences to the script in that post are that you open an existing workbook and there, a new workbook is created.

Oh, by the way: I am not completely sure whether a workbook created by an OLE method also contains 3 Sheets from the beginning onwards, I saw newly created workbooks with only one sheet, but I might confuse the use case here.

Re: Trouble creating new worksheet in Excel file
Christopher Cote - Fri May 17 08:44:42 EDT 2019

Well, I've been able to create new sheets with my code.  However, the new sheets appear before the third sheet instead of after, so when I go to rename the newly created sheets, I end up renaming one of the sheets many times and the new sheets stay named "Sheet#".  Here's the code:

int numSheetsCreated = 0;

void processSheet() {
    string sheetName;
    if (numSheetsCreated >= 3) {
        checkRes(oleGet(objExcel, "Sheets", objSheet));
        checkRes(oleMethod(objSheet, "Add"));
    }
    changeSheet(numSheetsCreated + 1);
    sheetName = "Module " (numSheetsCreated +1) "";
    checkRes(olePut(objSheet, "Name", sheetName));
    createColHeadings();
    numSheetsCreated++;
}

void init() {
    for (n=1; n<=10; n++) {
        processSheet();
    }
}

Notice the names of the sheets in the attached image.  Between Module 2 and Module 10, the other sheets still have the default "Sheet#" names.  This is because when I'm on the 3rd sheet, adding a sheet adds it in front of this sheet, then the last sheet gets renamed using the numSheetsCreated variable.  I thought about having 2 separate functions for creating and renaming the sheets and starting the second loop back at 1, but if I end up needing, say 50 new sheets, this small part of the function could take a rather long time to run and this doesn't include the rest of the functionality I still need to perform for each module.  So that option is probably off the table.

Chris

Re: Trouble creating new worksheet in Excel file
Mike.Scharnow - Mon May 20 17:50:32 EDT 2019

int numSheetsCreated = 0;

void processSheet() {
    string sheetName;
    if (numSheetsCreated >= 3) {
        checkRes(oleGet(objExcel, "Sheets", objSheet));
        checkRes(oleMethod(objSheet, "Add"));
    }
    changeSheet(numSheetsCreated + 1);
    sheetName = "Module " (numSheetsCreated +1) "";
    checkRes(olePut(objSheet, "Name", sheetName));
    createColHeadings();
    numSheetsCreated++;
}

void init() {
    for (n=1; n<=10; n++) {
        processSheet();
    }
}

I have not been able to run your code.and I don't understand it completely.

Assuming that your code begins with 

#include <utils/ole.inc>


OleAutoObj objExcel = oleCreateAutoObject("Excel.Application")
OleAutoObj objSheet = null

// Make it visible
olePut(objExcel, "Visible", true)
init()

and that your functions changeSheet and createColHeadings don't do anything at the moment.

Then:

in the first run of init(): objSheet is null -> there is nothing that shall get a name. So, I  get an error null OleAutoObj parameter was passed into argument position 1

If I start your script with int numSheetsCreated = 4, then I get an error "An OLE method failed" in the line "oleGet(objExcel, "Sheets", objSheet). Which sounds logical as like posted above, the sheets belong to a workbook, not to the Excel object.

So, I'm not really sure how your script works and produces this result, some parts seem to be missing.

With the following script, gathered from Mathias' script from the above mentioned post, I am able to create a new workbook which at the beginning contains only one empty sheet.

I get this as the active sheet and am able to change it's name. Then in the next run, I create a new sheet, which is a method of "Sheets", which I got from the Active Workbook.

#include <utils/ole.inc>

void oleCheck (string s) { 
   if (!null s) {
      print "An Error occurred: " s
      halt;
   }
}

OleAutoObj objExcel = oleCreateAutoObject("Excel.Application")
OleAutoObj objSheet = null

// Make it visible
olePut(objExcel, "Visible", true)

// get Application.Workbooks
OleAutoObj objWorkbookCollection= null; 
oleCheck oleGet ( objExcel, "Workbooks", objWorkbookCollection)

// Add a workbook --> Application.Workbooks.add ()
oleCheck oleMethod(objWorkbookCollection, "Add")

// Get a handle to the newly added workbook --> Application.ActiveWorkbook
OleAutoObj objWorkbook = null
oleCheck oleGet (objExcel, "ActiveWorkbook", objWorkbook)

// Get a handle to the Sheets collection --> ActiveWorkbook.Sheets
OleAutoObj objSheetsCollection = null
oleCheck oleGet (objExcel, "Sheets", objSheetsCollection )



void processSheet(int n) {
        print n"..1\n"
        string sheetName;

        if (n>1) {
                print n"..2\n"
                // Add a sheet ... ActiveWorkbook.Sheets.Add
                oleCheck oleMethod(objSheetsCollection , "Add") 
        }

        print n"..3\n"

        // Get a handle to the sheet ...
        OleAutoObj objSheet = null
        oleCheck oleGet (objWorkbook, "ActiveSheet", objSheet)

        print n"..4\n"
        sheetName = "Module " (n) "";

        print n"..5\n"
        checkRes(olePut(objSheet, "Name", sheetName));

//    changeSheet(objSheet);
//    createColHeadings(objSheet);

}

void init() {
    for (n=1; n<=10; n++) {
        processSheet(n);
    }
}
init()

And yes, with this code, the new sheets are created before the current sheet.

This is in accordance with https://docs.microsoft.com/en-us/office/vba/api/excel.sheets.add?f1url=https://msdn.microsoft.com/query/dev11.query?appId=Dev11IDEF1&l=de-DE&k=k(vbaxl10.chm152073);k(TargetFrameworkMoniker-Office.Version=v16)&rd=true

As stated there, "If Before and After are both omitted, the new sheet is inserted before the active sheet". So, you need parameters for the "Add" function. The "After" - Parameter needs the predecessor sheet.

So, I put the last activeSheet into a temporary variable "latestSheet" and fill it before the next loop starts.

The script then looks like this (new lines denoted with //*)

#include <utils/ole.inc>

void oleCheck (string s) { 
   if (!null s) {
      print "An Error occurred: " s
      halt;
   }
}

OleAutoObj objExcel = oleCreateAutoObject("Excel.Application")
OleAutoObj objSheet = null

// Make it visible
olePut(objExcel, "Visible", true)

// get Application.Workbooks
OleAutoObj objWorkbookCollection= null; 
oleCheck oleGet ( objExcel, "Workbooks", objWorkbookCollection)

// Add a workbook --> Application.Workbooks.add ()
oleCheck oleMethod(objWorkbookCollection, "Add")

// Get a handle to the newly added workbook --> Application.ActiveWorkbook
OleAutoObj objWorkbook = null
oleCheck oleGet (objExcel, "ActiveWorkbook", objWorkbook)

// Get a handle to the Sheets collection --> ActiveWorkbook.Sheets
OleAutoObj objSheetsCollection = null
oleCheck oleGet (objExcel, "Sheets", objSheetsCollection )


OleAutoArgs autoArgs = create                   //*
OleAutoObj latestSheet = null                   //*

void processSheet(int n) {
        print n"..1\n"
        string sheetName;

        if (n>1) {
                print n"..2\n"
                // Add a sheet ... ActiveWorkbook.Sheets.Add (After:=latestSheet) //*



                clear(autoArgs)                 //*
                put (autoArgs,"After", latestSheet) //*

                oleCheck oleMethod(objSheetsCollection , "Add", autoArgs)  //*
        }

        print n"..3\n"

        // Get a handle to the sheet ...
        OleAutoObj objSheet = null
        oleCheck oleGet (objWorkbook, "ActiveSheet", objSheet)
        latestSheet = objSheet                  //*

        print n"..4\n"
        sheetName = "Module " (n) "";

        print n"..5\n"
        checkRes(olePut(objSheet, "Name", sheetName));

//    changeSheet(objSheet);
//    createColHeadings(objSheet);

}

void init() {
    for (n=1; n<=10; n++) {
        processSheet(n);
    }
}
init()

Hope this helps,
Mike