Is there a simple way to determine if an open workbook has any data?

Maybe the used range = nothing?

Hi Nigel,

Try this, from an other workbook using the name of the workbook you
want to check as parameter

Public Function IsWorkBookEmpty(aWorkbook As Workbook) As Boolean
Dim sht As Worksheet
Dim bln As Boolean

bln = True
For Each sht In aWorkbook.Sheets
If sht.UsedRange.Cells.Count 1 Then
bln = False
Exit For
Else
If Not IsEmpty(sht.Range("A1")) Then
bln = False
Exit For
End If
End If
End If

If bln Then
If aWorkbook.Charts.Count 0 Then
bln = False
Exit For
End If
End If
End Function


HTH,

RadarEye

On 22 sep, 13:06, Nigel RS wrote:
Is there a simple way to determine if an open workbook has any data?

Maybe the used range = nothing?

Thanks with modification it works ok.

I had to assign the bln to the function name to get the returned value,
change the usedrange count logical test to 0 not 1, fix the for next loop
and remove the extra code in the charts count area. So I ended up with

Public Function IsWorkBookEmpty(aWorkbook As Workbook) As Boolean
Dim sht As Worksheet
Dim bln As Boolean

bln = True
For Each sht In aWorkbook.Sheets
If sht.UsedRange.Cells.Count 0 Then
bln = False
Exit For
Else
If Not IsEmpty(sht.Range("A1")) Then
bln = False
Exit For
End If
End If
Next
If bln And aWorkbook.Charts.Count 0 Then bln = False
IsWorkBookEmpty = bln
End Function




"RadarEye" wrote:

Hi Nigel,

Try this, from an other workbook using the name of the workbook you
want to check as parameter

Public Function IsWorkBookEmpty(aWorkbook As Workbook) As Boolean
Dim sht As Worksheet
Dim bln As Boolean

bln = True
For Each sht In aWorkbook.Sheets
If sht.UsedRange.Cells.Count 1 Then
bln = False
Exit For
Else
If Not IsEmpty(sht.Range("A1")) Then
bln = False
Exit For
End If
End If
End If

If bln Then
If aWorkbook.Charts.Count 0 Then
bln = False
Exit For
End If
End If
End Function


HTH,

RadarEye

On 22 sep, 13:06, Nigel RS wrote:
Is there a simple way to determine if an open workbook has any data?

Maybe the used range = nothing?