locked
How to check if excel sheet is blank using excel interop (c++ or vb code please)? RRS feed

  • Question

  • How to check if excel sheet is blank using excel interop (c++ or vb code please)?
    Tuesday, May 31, 2011 10:58 AM

Answers

  • It depends what you mean by "blank", simply zero contents, or also none with any formats. I can't advise c++ but I'm sure you can adapt this VBA

    Sub test()
    Dim ws As Worksheet
       For Each ws In ActiveWorkbook.Worksheets
    Debug.Print SheetIsEmpty(ws), SheetIsBlank(ws), ws.Name
       Next
    End Sub
    
    Function SheetIsEmpty(ws As Worksheet) As Boolean
    Dim rng As Range
    ' zero data/formula cells and
    ' at most one cell might contain some formatting
       Set rng = ws.UsedRange
       If rng.Count = 1 Then
           SheetIsEmpty = (Len(rng) = 0)
       End If
    End Function
    
    Function SheetIsBlank(ws As Worksheet) As Boolean
    Dim rng As Range
    ' zero data/formula cells
    ' on errror resume next
       Set rng = ws.Cells.Find(What:="*", LookIn:=xlFormulas)
       SheetIsBlank = rng Is Nothing
    End Function

    I expect you would need to complete the missing arguments in the Find function

    Peter Thornton

    • Marked as answer by Pervez Rehman Wednesday, June 1, 2011 3:39 PM
    Tuesday, May 31, 2011 7:11 PM

All replies

  • the is no easy wasy of telling if a worksheet is empty without check each cell for data which may take a lot of time to execute.  th eused area of a worksheet grows as yo add dat to the rows and columns and never shrinks usless you delte rows and/or columns.  there may be better methods of achieving the same reults without checking the enditre worksheet.  for example if yo had data in column A yo can check if thge last row used in column A is row 1 which wilindicate if there is data on the worksheet which will execute much faster than checking the entire worksheet.  Usedarea is not a good method of checking if data is in a worksheet becasue the usedarea will include the highest row and columns that were used on the worksheet even if the data has been cleared from the worksheet.
    jdweng
    Tuesday, May 31, 2011 11:32 AM
  • Hello,

    You need to check Worksheet.UsedRange, see http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.usedrange.aspx. I'd experiment with Worksheet.UsedRange.Count. Note that it may be 1 in two cases: 1) A1 cell is empty and 2) A! cell isn't empty, so you'll need to check this, too. Also, depending on what you call a "blank sheet", you may need to check if Worksheet.Comments and Worksheet.Shapes are empty. Sorry, no code.


    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    Tuesday, May 31, 2011 1:45 PM
  • I'm using below login in c++ to check blank page. Any problem or performance issue with this logic..? bool ExcelHelper::IsBlankSheet (const Excel::_WorksheetPtr& pWorksheet) { bool bIsBlankSheet = false; if (pWorksheet != NULL) { Excel::RangePtr UsedRangePtr = pWorksheet->UsedRange; if (UsedRangePtr != NULL) { if (UsedRangePtr->Columns->Count <= 1 && UsedRangePtr->Rows->Count <= 1 && UsedRangePtr->Count <= 1 ) { _bstr_t bstrValue = UsedRangePtr->GetValue(); if (bstrValue.length() <= 0) { bIsBlankSheet = true; } } } } return bIsBlankSheet; }
    Tuesday, May 31, 2011 5:18 PM
  • It depends what you mean by "blank", simply zero contents, or also none with any formats. I can't advise c++ but I'm sure you can adapt this VBA

    Sub test()
    Dim ws As Worksheet
       For Each ws In ActiveWorkbook.Worksheets
    Debug.Print SheetIsEmpty(ws), SheetIsBlank(ws), ws.Name
       Next
    End Sub
    
    Function SheetIsEmpty(ws As Worksheet) As Boolean
    Dim rng As Range
    ' zero data/formula cells and
    ' at most one cell might contain some formatting
       Set rng = ws.UsedRange
       If rng.Count = 1 Then
           SheetIsEmpty = (Len(rng) = 0)
       End If
    End Function
    
    Function SheetIsBlank(ws As Worksheet) As Boolean
    Dim rng As Range
    ' zero data/formula cells
    ' on errror resume next
       Set rng = ws.Cells.Find(What:="*", LookIn:=xlFormulas)
       SheetIsBlank = rng Is Nothing
    End Function

    I expect you would need to complete the missing arguments in the Find function

    Peter Thornton

    • Marked as answer by Pervez Rehman Wednesday, June 1, 2011 3:39 PM
    Tuesday, May 31, 2011 7:11 PM
  • Hello Pervez,

    The function looks okay. Note that if you use it in a COM add-in, you should release every COM object created in your code.


    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    Wednesday, June 1, 2011 6:11 AM