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

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" <vsn_hotmail_kom wrote in message
...
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

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

"Otto Moehrbach" schreef in bericht
...
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" <vsn_hotmail_kom wrote in message
...
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

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 (MVP - Excel)


"Vsn" <vsn_hotmail_kom wrote in message
...
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

"Otto Moehrbach" schreef in bericht
...
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" <vsn_hotmail_kom wrote in message
...
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

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


--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Vsn" <vsn_hotmail_kom wrote in message
...
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

"Otto Moehrbach" schreef in bericht
...
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" <vsn_hotmail_kom wrote in message
...
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

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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Vsn" <vsn_hotmail_kom wrote in message
...
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

"Otto Moehrbach" schreef in bericht
...
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" <vsn_hotmail_kom wrote in message
...
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

For the OP:

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

Tim

"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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 (MVP - Excel)


"Vsn" <vsn_hotmail_kom wrote in message
...
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

"Otto Moehrbach" schreef in bericht
...
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" <vsn_hotmail_kom wrote in message
...
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

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

"Rick Rothstein" schreef in bericht
...
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 (MVP - Excel)


"Vsn" <vsn_hotmail_kom wrote in message
...
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

"Otto Moehrbach" schreef in bericht
...
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" <vsn_hotmail_kom wrote in message
...
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