Empty Word/Excel Document

[Jacques Cooper]

Hello,

When the user clicks our toolbar button, how
can we determine if a document or spreadsheet
is empty? We don't want to process an empty
document.

We tried this, but it did not solve the problem:

If oApp.ActiveDocument = " " Then
nResponse = MsgBox("The Word document is Empty", _
vbMsgBoxSetForeground, PRODUCT_NAME)
Exit Sub
End If

TIA,
Jacques

 

[Jonathan West]

Hi Jacques,

For Word, try this

If Len(oApp.ActiveDocument.Content) < 2 Then
nResponse = MsgBox("The Word document is Empty", _
vbMsgBoxSetForeground, PRODUCT_NAME)
Exit Sub
End If

 

[Jacques Cooper]

Hi Jonathan,

Thank you for replying to my posting.

Your suggestion worked for Word, but it
did not work for Excel.

How do I determine if a spreadsheet is empty?

This did not work:

If Len(oApp.ActiveWorkbook.Content) < 2 Then
nResponse = MsgBox("The Excel spreadsheet is Empty", _
vbMsgBoxSetForeground, PRODUCT_NAME)
Exit Sub
End If

Thanks,
Jacques

 

[Jonathan West]

I don't know Ecxel so well. Hopefully an Excel expert will be along soon. Of
not, you might like to re-post this question in
microsoft.public.excel.programming, which is where the Excel VBA experts
hang out.

 

[Wei-Dong Xu [MSFT]]

Hi Jacques,

Thank you for posting in MSDN managed newsgroup!

So far as I know for checking the empty of one excel workbook, I'd suggest you may check the UsedRange property of each worksheet in one
workbook. I write one sample code for you.

'Code begin-------------------------------------------------------------

Dim objS As Worksheet

Set objS = Application.Sheets(1)

if objS.UsedRange.Cells.Count < 2 then
msgbox "Empty worksheet"
end if
'Code end---------------------------------------------------------------

The default count of cells in one worksheet is 1 so we should check whether the value returned is less than 2. However, this way, if your worksheet
only contains one value in the worksheet, it will also be considered as empty. For most application, one used worksheet will contains more than 1 cell
in the usedrange. This workaround may help us to check most worksheet.

Please feel free to let me know if you have any further questions.

Does this answer your question? Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.