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

Trying to understand the perspective

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


Joined: 18 Sep 2008
Posts: 8

PostPosted: Tue Jan 13, 2009 1:59 am    Post subject: Trying to understand the perspective Reply with quote

Hi.

I "have to" use Open Office Calc in business cos the bank I work in enforce this. For one year I tussle with Calc; I need some macros but I can not write them. I can write VBA code. I have Pitonyak's book somehow but there is some questions on my mind. If an experienced one replies me; I'll be glad.

1) For clearing contents of a cell:
Sub ClearDefinedRange
Dim oDocument As Object, oSheet As Object, oSheets As Object
Dim oCellRange As Object
Dim nSheets As Long

oDocument = ThisComponent
oSheets = oDocument.Sheets
nSheets = oDocument.Sheets.Count
oSheet = oSheets.getByIndex(2)

oCellRange = oSheet.getCellRangeByName("A1:C4")
oCellRange.clearContents(_
com.sun.star.sheet.CellFlags.VALUE | _
com.sun.star.sheet.CellFlags.DATETIME | _
com.sun.star.sheet.CellFlags.STRING | _
com.sun.star.sheet.CellFlags.ANNOTATION | _
com.sun.star.sheet.CellFlags.FORMULA | _
com.sun.star.sheet.CellFlags.HARDATTR | _
com.sun.star.sheet.CellFlags.STYLES | _
com.sun.star.sheet.CellFlags.OBJECTS | _
com.sun.star.sheet.CellFlags.EDITATTR)
End Sub

is this functional? About 20 lines of code and the process is same as the button "Delete"?
in VBA this is 3 lines; or 2 if you work with active sheet.
Besides ; does Open Office clear value, datetime, string etc. seperately?
what for "com.sun.star"? is it a respect symbol for holy Sun Microsystems?


2)
Some says VBA doesn't work on Open Office cos it includes Excel objects.
For instance "Cell" is not a special Excel object; the programs which claim themselves as spreadsheet software have "cells". I think it must not be hard in code to refer a common cell. Everytime define variables; appoint sheets, cells...
This is not like an interior environment; as if Open Office basic is a third party software for Open Office.
Naturally if we say "sheet" we mean one of Calc sheets; when you describe an address to your friend every time you say the city? or begining with street? the default is clear; if I doesn't specify the city; I mean "this city".
is there another way to refer sheets ; cells?

3) Pitonyak's book (which is downloadable) is good; is there another book as good as Pitonyak's?[/b]
Back to top
View user's profile Send private message MSN Messenger
etfloyd
General User
General User


Joined: 12 Jan 2009
Posts: 5

PostPosted: Tue Jan 13, 2009 11:09 am    Post subject: Reply with quote

You can use -1 to indicate all bits for clearing, and you can string together the property and method references, thus:
Code:
Sub ClearDefinedRange
   Dim oSheet
   oSheet = ThisComponent.Sheets.getByIndex(2)
   oSheet.getCellRangeByName("A1:C4").clearContents(-1)
End Sub

..is equivalent to your subroutine. Also, you can get a sheet object by name, and refer to ranges by column / row numbers (zero-based!), which can be expressions, and you can string together as many references as you wish. Thus, the following one-liner is the equivalent to the above, assuming the second sheet is named "Sheet2":
Code:
Sub ClearDefinedRange
   ThisComponent.Sheets.getByName("Sheet2").getCellRangeByPosition(0, 0, 2, 3).clearContents(-1)
End Sub

getCellRangeByPosition's parameters define the range by the upper-left to lower-right corners: (leftcolumn, toprow, rightcolumn, bottomrow), zero-based, so (0, 0, 2, 3) is equivalent to "A1:C4".

You can also get/set data into individual cells by column and row number. The following subroutine adds 1 to the value of a cell:
Code:

Sub AddOne(ByVal sheetname as String, ByVal col as Long, ByVal row as Long)
   Dim oSheet
   Dim v as Double
   oSheet = ThisComponent.Sheets.getByName(sheetname)
   v = oSheet.getCellByPosition(col, row).getValue()
   v = v + 1
   oSheet.getCellByPosition(col, row).setValue(v)
End Sub

Note that all "by position" cell addresses are column first, then row, and are zero-based. Thus "AddOne("Sheet1", 3, 4)" would increment cell D5.
Back to top
View user's profile Send private message
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