Topic
  • 3 replies
  • Latest Post - ‏2019-06-13T18:36:29Z by O.Wilkop
Christopher Cote
Christopher Cote
67 Posts

Pinned topic Determining number of sheets in Excel file

‏2019-06-07T18:54:41Z |

I have a script that I wrote that assumed that a new Excel workbook contains 3 sheets to begin with.  However, I have since found out that the supposition is incorrect.  One of the people who uses the script, has only one tab when he opens Excel.  So, I need to test the number of sheets to decide whether I need to add a new sheet before continuing.  I'm assuming that I can use the returned object from the oleGet method, but I need help figuring out what the correct syntax would be to get the number of sheets.  All help would be greatly appreciated.

 

Chris

  • O.Wilkop
    O.Wilkop
    125 Posts

    Re: Determining number of sheets in Excel file

    ‏2019-06-08T18:15:24Z  

    You need to oleGet your way through the Excel data model to get to the WorkSheets collection object and then check its Count property. How exactly you do that depends on your code and what you want to do, but here is the gist of it:

    1. Get the excel application object (from oleGetAutoObject or oleCreateAutoObject) I'll call it objExcelApplication

    2. Get the Workbook object, this is the excel "file" you are working with. There's different ways to do this, depending on what exactly you want to do, for example if you add a new workbook you'd get it's handle, or you can get the Workbooks collection and check which workbooks are open and pick one or you can open a existing file, we go with the easiest and use the "ActiveWorkbook" property, which is the currently open and shown "file" in the Excel Application:

    oleGet(objExcelApplication, "ActiveWorkbook", 
    objExcelWorkbook);
    

    3. Once you have the workbook object you want its sheets collection object:

    oleGet(objExcelWorkbook, "Sheets", 
    objExcelSheets);
    

    4. And finally get the Count property of the sheets collection object:

    oleGet(objExcelSheets, "Count", iCount);
    

     

    If you want to know what other properties/methods there are available its a good idea to look at Microsofts documentation: https://docs.microsoft.com/en-us/office/vba/api/overview/excel/object-model (check the left side for the object model and what is available and which magic words you have to use). You can also record macros in Excel and look at the generated code in the VBA code editor or use the editor to explore the object model in Excel itself. That being said the generated macro code usually doesn't follow "best practices", but it might give you a hint on how to achieve certain things, of course you'll have to adjust the code for it to work in dxl.

     

    btw, I think with Office 365 Microsoft started only having a single sheet in new files, so that's probably the cause of your problem.

  • Christopher Cote
    Christopher Cote
    67 Posts

    Re: Determining number of sheets in Excel file

    ‏2019-06-10T13:54:54Z  
    • O.Wilkop
    • ‏2019-06-08T18:15:24Z

    You need to oleGet your way through the Excel data model to get to the WorkSheets collection object and then check its Count property. How exactly you do that depends on your code and what you want to do, but here is the gist of it:

    1. Get the excel application object (from oleGetAutoObject or oleCreateAutoObject) I'll call it objExcelApplication

    2. Get the Workbook object, this is the excel "file" you are working with. There's different ways to do this, depending on what exactly you want to do, for example if you add a new workbook you'd get it's handle, or you can get the Workbooks collection and check which workbooks are open and pick one or you can open a existing file, we go with the easiest and use the "ActiveWorkbook" property, which is the currently open and shown "file" in the Excel Application:

    <pre class="javascript dw" dir="ltr">oleGet(objExcelApplication, "ActiveWorkbook", objExcelWorkbook); </pre>

    3. Once you have the workbook object you want its sheets collection object:

    <pre class="javascript dw" dir="ltr">oleGet(objExcelWorkbook, "Sheets", objExcelSheets); </pre>

    4. And finally get the Count property of the sheets collection object:

    <pre class="javascript dw" dir="ltr">oleGet(objExcelSheets, "Count", iCount); </pre>

     

    If you want to know what other properties/methods there are available its a good idea to look at Microsofts documentation: https://docs.microsoft.com/en-us/office/vba/api/overview/excel/object-model (check the left side for the object model and what is available and which magic words you have to use). You can also record macros in Excel and look at the generated code in the VBA code editor or use the editor to explore the object model in Excel itself. That being said the generated macro code usually doesn't follow "best practices", but it might give you a hint on how to achieve certain things, of course you'll have to adjust the code for it to work in dxl.

     

    btw, I think with Office 365 Microsoft started only having a single sheet in new files, so that's probably the cause of your problem.

    Thanks Oliver.  This should be very helpful.  The problem was that the code I'm looking at was written by someone else, so I just needed a little help with the OLE functions to use within DXL.  My code is slightly different in that I have an init function that uses the following code:

    bool excelInit( bool instanceShouldExist) {
        string funcName = "excelInit()";
        
        if (instanceShouldExist) {
            //If the user has Excel opened with the sheet of interest currently active
            objExcel = oleGetAutoObject(cObjExcelApplication);
            if (null objExcel) {
                reportError( funcName, "Unable to obtain access to Excel - Is your Excel Worksheet bearing events open?", !cQuit, cAck);
                return false;
            } else {
                //Create new instance
                objExcel = connectToApp( cObjExcelApplication, "Excel");
                if (null objExcel) {
                    reportError( funcName, "Unable to create access to Excel - Do you have Excel installed?", !cQuit, cAck);
                    return false;
                }
                makeVisibile objExcel;
            }
            //get workbooks collection
            checkRes(oleGet(objExcel, cPropertyWorkbooks, objWorkbooks));
            if (null objWorkbooks) {
                reportError( funcName, "Unable to get workbooks collection", !cQuit, cAck);
                return false;
            }
            if (!instanceShouldExist) {
                //If instance shouldn't exist, it means we're writing to Excel,
                //not reading from it. Add a worksheet accordingly.
                checkRes(oleMethod(objWorkbooks, cMethodAdd));
            }
            //get active workbook
            checkRes(oleGet(objExcel, cPropertyActiveWorkbook, objWorkbook));
            if (null objWorkbook) {
                reportError(funcName, "Unable to get active workbook", !cQuit, cAck);
                return false;
            }
            
            //get active sheet (user must have sheet opened - or we just created it)
            checkRes(oleGet(objWorkbook, cPropertyActiveSheet, objSheet));
            if (null objSheet) {
                reportError(funcName, "Unable to get Events sheet", !cQuit, cAck);
                return false;
            }
            return true;
        }
    }
    

    I'm thinking of adding to this function some way to determine how many worksheets we'll need in total and add as many sheets needed to get that.

     

    Chris

  • O.Wilkop
    O.Wilkop
    125 Posts

    Re: Determining number of sheets in Excel file

    ‏2019-06-13T18:36:29Z  

    Thanks Oliver.  This should be very helpful.  The problem was that the code I'm looking at was written by someone else, so I just needed a little help with the OLE functions to use within DXL.  My code is slightly different in that I have an init function that uses the following code:

    <pre class="javascript dw" dir="ltr">bool excelInit( bool instanceShouldExist) { string funcName = "excelInit()"; if (instanceShouldExist) { //If the user has Excel opened with the sheet of interest currently active objExcel = oleGetAutoObject(cObjExcelApplication); if (null objExcel) { reportError( funcName, "Unable to obtain access to Excel - Is your Excel Worksheet bearing events open?", !cQuit, cAck); return false; } else { //Create new instance objExcel = connectToApp( cObjExcelApplication, "Excel"); if (null objExcel) { reportError( funcName, "Unable to create access to Excel - Do you have Excel installed?", !cQuit, cAck); return false; } makeVisibile objExcel; } //get workbooks collection checkRes(oleGet(objExcel, cPropertyWorkbooks, objWorkbooks)); if (null objWorkbooks) { reportError( funcName, "Unable to get workbooks collection", !cQuit, cAck); return false; } if (!instanceShouldExist) { //If instance shouldn't exist, it means we're writing to Excel, //not reading from it. Add a worksheet accordingly. checkRes(oleMethod(objWorkbooks, cMethodAdd)); } //get active workbook checkRes(oleGet(objExcel, cPropertyActiveWorkbook, objWorkbook)); if (null objWorkbook) { reportError(funcName, "Unable to get active workbook", !cQuit, cAck); return false; } //get active sheet (user must have sheet opened - or we just created it) checkRes(oleGet(objWorkbook, cPropertyActiveSheet, objSheet)); if (null objSheet) { reportError(funcName, "Unable to get Events sheet", !cQuit, cAck); return false; } return true; } } </pre>

    I'm thinking of adding to this function some way to determine how many worksheets we'll need in total and add as many sheets needed to get that.

     

    Chris

    in line 36 I'd add something like

    oleAutoObj objSheets;
    oleGet(objWorkbook, "Sheets", objSheets);
    
    int iSheetCount = 0;
    oleGet(objSheets, "Count", iSheetCount);
    
    while(iSheetCount < 3){
        oleMethod(objSheets, "Add");
        oleGet(objSheets, "Count", iSheetCount);
    }
    

    (you might have to adjust the syntax, I can't check it right now)

     

    I'm not sure where it adds the new sheets by default, so you might have to adjust what sheet you end up writing to work with afterwards.