OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

how to detect a cell range if it is empty.(Macro)

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
topher
General User
General User


Joined: 29 Sep 2004
Posts: 12

PostPosted: Wed Sep 29, 2004 7:45 pm    Post subject: how to detect a cell range if it is empty.(Macro) Reply with quote

can anyone give me a sample code on how to detect if a cell range is empty. in macro
Back to top
View user's profile Send private message
pitonyak
Administrator
Administrator


Joined: 09 Mar 2004
Posts: 3655
Location: Columbus, Ohio, USA

PostPosted: Sat Oct 02, 2004 3:18 am    Post subject: Reply with quote

I do not have time for a complete answer, but I noticed yesterday that no answer had been given so I took the time to look this up before I leave (I have to hang some things at church).

if you can find a copy of my published book, go to page 338 Listing 13, NonEmptyCellsInRange(). The basic idea is as follows:

* Obtain the CellRange that interests you from the sheet
* Use the queryContentCells(flags) method of the cell range object.

The trick with this is that this allows you to find the cells that are NOT empty. You, on the other hand, want to know if the entire range is empty. If the query returns any cells that contain something, then the range is not empty.

I hope that this at least gets you started. I just did not want this to be lost even if I lack the time at the moment.
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php
Back to top
View user's profile Send private message Send e-mail Visit poster's website AIM Address
SergeM
Super User
Super User


Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France

PostPosted: Sat Oct 02, 2004 8:25 am    Post subject: Reply with quote

here is a OOoBasic example :
Code:

Dim MonDocument As Object, LesFeuilles As Object
Dim MaFeuille As Object, UneCellule As Object
Dim ContTexte As Integer, ContValeur As Integer
Dim ContFormule As Integer, ContVide As Integer

MonDocument = ThisComponent
LesFeuilles = MonDocument.Sheets
MaFeuille = LesFeuilles.getByName("Feuille2")
UneCellule =MaFeuille.getCellByPosition(2,9) ' cellule C10

rem   constantes des types de contenu
ContVide=    com.sun.star.table.CellContentType.EMPTY
ContValeur=  com.sun.star.table.CellContentType.VALUE
ContTexte=   com.sun.star.table.CellContentType.TEXT
ContFormule= com.sun.star.table.CellContentType.FORMULA

Select Case UneCellule.Type
Case ContVide
  Print "case vide !"
Case ContFormule
  Print "Formule"
Case ContValeur
  Print "Valeur"
Case ContTexte
  Print "Texte"
End Select

Sorry the variable's names are in French : this code is from Bernard Marcelly French Document
Hope that help
_________________
Linux & Windows OOo3.0
UNO & C++ : WIKI
http://wiki.services.openoffice.org/wiki/Using_Cpp_with_the_OOo_SDK
In French
http://wiki.services.openoffice.org/wiki/Documentation/FR/Cpp_Guide
Back to top
View user's profile Send private message Visit poster's website
pitonyak
Administrator
Administrator


Joined: 09 Mar 2004
Posts: 3655
Location: Columbus, Ohio, USA

PostPosted: Mon Oct 04, 2004 5:31 pm    Post subject: Reply with quote

This is the solution that I would use!
Code:
Option Explicit

Sub TestEmpty
  Dim oSheet
  Dim oRange
 
  oSheet = ThisComponent.Sheets(0)
  oRange = oSheet.getCellRangeByName("A1:F8")
 
  If IsCellRangeEmpty(oRange) Then
    Print "The cell range is empty"
  Else
    Print "The cell range is NOT empty"
  End If 
End Sub

Function IsCellRangeEmpty(oRange) As Boolean
  Dim oRanges      'Ranges returned after querying for the cells
  Dim oAddrs()     'Array of CellRangeAddress
 
  oRanges = oRange.queryContentCells(_
    com.sun.star.sheet.CellFlags.VALUE OR _
    com.sun.star.sheet.CellFlags.DATETIME OR _
    com.sun.star.sheet.CellFlags.STRING OR _
    com.sun.star.sheet.CellFlags.FORMULA)
  oAddrs() = oRanges.getRangeAddresses()
 
  IsCellRangeEmpty = UBound(oAddrs()) < 0
End Function

_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php
Back to top
View user's profile Send private message Send e-mail Visit poster's website AIM Address
topher
General User
General User


Joined: 29 Sep 2004
Posts: 12

PostPosted: Tue Nov 09, 2004 12:25 am    Post subject: YOUR REPLY ON HOW TO DETECT IF A CELL RANGE IS EMPTY Reply with quote

pitonyak wrote:
This is the solution that I would use!
Code:
Option Explicit

Sub TestEmpty
  Dim oSheet
  Dim oRange
 
  oSheet = ThisComponent.Sheets(0)
  oRange = oSheet.getCellRangeByName("A1:F8")
 
  If IsCellRangeEmpty(oRange) Then
    Print "The cell range is empty"
  Else
    Print "The cell range is NOT empty"
  End If 
End Sub

Function IsCellRangeEmpty(oRange) As Boolean
  Dim oRanges      'Ranges returned after querying for the cells
  Dim oAddrs()     'Array of CellRangeAddress
 
  oRanges = oRange.queryContentCells(_
    com.sun.star.sheet.CellFlags.VALUE OR _
    com.sun.star.sheet.CellFlags.DATETIME OR _
    com.sun.star.sheet.CellFlags.STRING OR _
    com.sun.star.sheet.CellFlags.FORMULA)
  oAddrs() = oRanges.getRangeAddresses()
 
  IsCellRangeEmpty = UBound(oAddrs()) < 0
End Function


Helo,

thanks for the code that you gave me before.
it worked with cell ranges that contains no strings, numbers, formulas and etc.
but it did not worked for ranges with formulas.
I want to use it with cellranges with formulas to detect if it contains no numbers or values.

I tried the combinations of using AND instead of OR and removing some of com.sun.star.... but to no avail i failed.

hope u give me an answer.
Tnx
Back to top
View user's profile Send private message
topher
General User
General User


Joined: 29 Sep 2004
Posts: 12

PostPosted: Tue Nov 09, 2004 12:34 am    Post subject: Reply with quote

pitonyak wrote:
This is the solution that I would use!
Code:
Option Explicit

Sub TestEmpty
Dim oSheet
Dim oRange

oSheet = ThisComponent.Sheets(0)
oRange = oSheet.getCellRangeByName("A1:F8")

If IsCellRangeEmpty(oRange) Then
Print "The cell range is empty"
Else
Print "The cell range is NOT empty"
End If
End Sub

Function IsCellRangeEmpty(oRange) As Boolean
Dim oRanges 'Ranges returned after querying for the cells
Dim oAddrs() 'Array of CellRangeAddress

oRanges = oRange.queryContentCells(_
com.sun.star.sheet.CellFlags.VALUE OR _
com.sun.star.sheet.CellFlags.DATETIME OR _
com.sun.star.sheet.CellFlags.STRING OR _
com.sun.star.sheet.CellFlags.FORMULA)
oAddrs() = oRanges.getRangeAddresses()

IsCellRangeEmpty = UBound(oAddrs()) < 0
End Function



Helo,

thanks for the code that you gave me before.
it worked with cell ranges that contains no strings, numbers, formulas and etc.
but it did not worked for ranges with formulas.
I want to use it with cellranges with formulas to detect if it contains no numbers or values.

I tried the combinations of using AND instead of OR and removing some of com.sun.star.... but to no avail i failed.

hope u give me an answer.
Tnx
Back to top
View user's profile Send private message
pitonyak
Administrator
Administrator


Joined: 09 Mar 2004
Posts: 3655
Location: Columbus, Ohio, USA

PostPosted: Tue Nov 09, 2004 7:36 pm    Post subject: Reply with quote

The code works for me as is. I entered a single formula into the range and it indicated that the range was not empty... How does this fail for you?
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php
Back to top
View user's profile Send private message Send e-mail Visit poster's website AIM Address
topher
General User
General User


Joined: 29 Sep 2004
Posts: 12

PostPosted: Wed Nov 10, 2004 10:45 pm    Post subject: Reply with quote

helo,

here are the conditions that it failed::
1. the cellranges all contains formulas.
2. the cellranges will show a value or nothing due to the formulas.

the problem is, the macro says not empty even if the cell ranges shows no value.
it should say empty even it has formula since there is no value.
Back to top
View user's profile Send private message
pitonyak
Administrator
Administrator


Joined: 09 Mar 2004
Posts: 3655
Location: Columbus, Ohio, USA

PostPosted: Fri Nov 12, 2004 8:27 am    Post subject: Reply with quote

The query functions are not used to notice that no value is displayed, they are used to notice that a formula is in the cell. What you really want to notice is that a cell is displaying nothing, and that is a different question.
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php
Back to top
View user's profile Send private message Send e-mail Visit poster's website AIM Address
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group