Is Sheet Empty and unused

[Vsn]

Hi all,

I was thinking is there a methode in VBA to see if a sheet has been used or
that it is empty so that i can remove it from the workbook knowing i do not
loose data or better rename it an put the next bunch of data comming?

Thx,
Ludovic

 

[Otto Moehrbach]

Try this macro. HTH Otto
Sub IsSheetEmpty()
If Application.CountA(Cells) = 0 Then
MsgBox "It's empty."
Else
MsgBox "It's not empty."
End If
End Sub

 

[Vsn]

Unfortunatly I can't use this because i like to use the code from Access VBA
using late binding and that does not accept the 'Cells' in '.CountA(Cells)'

Are there any other methods which would be suitable to be used from Access.
I like to check if there are any empty sheets in the workbook to which i can
dump data, this before I just add a sheet to the collection.

Cheers,
Ludovic

 

[Rick Rothstein]

I've not controlled Excel from another program before, so this is just a
guess... what about if you explicitly qualify the Cells property with
ActiveSheet property?

If Application.CountA(ActiveSheet.Cells) = 0 Then

 

[Rick Rothstein]

Here is an all VBA method to do what you want...

 

[Rick Rothstein]

Here is an all VBA method to do what you want...

If ActiveSheet.Cells.Find(What:="*", LookIn:=xlFormulas) Is Nothing Then
MsgBox "All cells are empty"
Else
MsgBox "There is data on this worksheet"
End If

Note that this also uses ActiveSheet.Cells, but it does not try to use it
inside a worksheet function call. By the way, in both this and my previous
message, I am assuming that you are going to "vector" your calls to the
Excel objects through the variable that you have set up for referencing
Excel.

 

[Tim Williams]

For the OP:

....and don't forget to replace any Excel constants such as xlFormulas with
their values.

Tim

 

[Rick Rothstein]

For the OP... if you need it, the value of xlFormulas is the value -4123.

 

[Vsn]

Thanks for all advices. Finaly I managed like this:-

Dim objActiveWkbk As Object
Dim objXL As Object

Set objXL = CreateObject("Excel.Application")
Set objActiveWkbk = objXL.Application.ActiveWorkbook

If objXL.Application.CountA(objActiveWkbk.ActiveSheet.usedrange) = 0 then
..........


Cheers,
Ludovic