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

[Solved]Know the referenced cells by formulas in other cells

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


Joined: 12 Mar 2012
Posts: 34

PostPosted: Wed Aug 01, 2012 8:51 am    Post subject: [Solved]Know the referenced cells by formulas in other cells Reply with quote

Which is a _programmatic_ way to obtain all the referenced cells by another range of cells?
I don't care (and don't want) to show arrows on the sheet in the style of
the "detective"/Auditing functions do.
For example if in cell E3 there's a formula "=A3+B3"
I want to know that E3 references cells A3 and B3


Last edited by satabau on Thu Aug 30, 2012 11:55 am; edited 2 times in total
Back to top
View user's profile Send private message
patel
Power User
Power User


Joined: 14 Apr 2012
Posts: 54
Location: Italy

PostPosted: Wed Aug 01, 2012 9:16 pm    Post subject: Reply with quote

I don't understand what you mean
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
B Marcelly
Super User
Super User


Joined: 12 May 2004
Posts: 1453
Location: France

PostPosted: Fri Aug 03, 2012 4:11 am    Post subject: Reply with quote

Hi,
There is no easy way, in the general case. You will have to get and analyse the formula tokens of the cell (E3 in your example).

From your cell, yourCell.getTokens() returns an array of Formula tokens. They are the result of the analysis of the formula.

See IDL documentation
http://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/XFormulaTokens.html#getTokens
Then follow link FormulaToken that shows you the structure.
Then follow link XFormulaOpCodeMapper, read, follow all links, etc.

Good luck.
_________________
Bernard

OpenOffice.org 1.1.5 fr / Apache OpenOffice 4.0.1 / LibreOffice 4.1.0
MS-Windows 7 Home SP1
This forum is spammed, use instead Apache OpenOffice forums
Back to top
View user's profile Send private message Visit poster's website
Keith Wild
General User
General User


Joined: 17 Jan 2011
Posts: 15
Location: London, UK

PostPosted: Fri Aug 03, 2012 12:04 pm    Post subject: Reply with quote

This could be a start.

Code:
Option Explicit

Sub FindReferencedCells

Dim i As Integer
Dim Doc As Object
Dim Sheet As Object, Sheets As Object
Dim CellRanges As Object, Cell As Object, Cells As Object
Dim CAN As String, CellText As String, Result As String, t$
Dim TargetCellName As String

Doc = ThisComponent
Sheet = Doc.Sheets(0)
TargetCellName = "C7"      'References to this cell required
CellRanges = Sheet.queryFormulaCells(7)
Cells = CellRanges.getCells
For each Cell in Cells
   CAN = Cell.AbsoluteName
   CellText = Cell.Formula
   i = Instr(CellText,TargetCellName)
   If i > 0 Then
   t = "Cell " & CAN & " refers to " & TargetCellName &CHR$(10)
   Result = Result & t
   End If   
Next
MsgBox(Result,, "Cell References")
End Sub   


It doesn't deal with a number of things:
    More than one cell
    More than one sheet
    Absolute reference , i.e. those containing $ signs, such as $C7, C$7 , $C$7


I don't understand Bernard's references to tokens, but would be interested to know what they are .
_________________
AOO 3.4.1
Windows Vista Home Premium SP2
Back to top
View user's profile Send private message
satabau
General User
General User


Joined: 12 Mar 2012
Posts: 34

PostPosted: Mon Aug 06, 2012 7:21 am    Post subject: Reply with quote

Thanks you all for the answers. I was very busy and I haven't tried yet.
But now I've found this: http://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/XFormulaQuery.html
Do you think is the best way to get what I want?
Back to top
View user's profile Send private message
B Marcelly
Super User
Super User


Joined: 12 May 2004
Posts: 1453
Location: France

PostPosted: Mon Aug 06, 2012 11:03 am    Post subject: Reply with quote

Good find. Cool

With the cell containing the formula, use methods queryDependents or queryPrecedents.
With the returned object, method getRangeAddresses will provide an array of CellRangeAddress.
_________________
Bernard

OpenOffice.org 1.1.5 fr / Apache OpenOffice 4.0.1 / LibreOffice 4.1.0
MS-Windows 7 Home SP1
This forum is spammed, use instead Apache OpenOffice forums
Back to top
View user's profile Send private message Visit poster's website
Keith Wild
General User
General User


Joined: 17 Jan 2011
Posts: 15
Location: London, UK

PostPosted: Wed Aug 08, 2012 4:10 am    Post subject: Reply with quote

Yes, good spot. I tried to use this and came up with the macros below. I passed two parameters to the macros because I thought if you are going to be cycling through a number of cells it would be easier to do it this way, i.e. TargetCell in the sub Main can be put in a For...Next loop or something similar.

There still seems to be a few of problems with the queryDependents method.



I know you said you didn't want blue arrows, but I put a sub in below to show them so it can be seen that an off sheet link does exist if you test it, although not detected by the queryDependents method (although even the blue arrows don't actually trace where it is, just that it exists).

Code:

Option Explicit

Sub Main
Dim Doc As Object, TargetSheet As Object, TargetCell As Object
Doc = ThisComponent
TargetSheet = Doc.Sheets(0)
TargetCell = TargetSheet.getCellByPosition(2,6)'C7
ListDependents (TargetCell, TargetSheet)
ShowDependents (TargetCell, TargetSheet)
End Sub


Sub ListDependents (TC As Object, TS As Object)
Dim i As Integer
Dim Cell As Object, CellRanges As Object
Dim RangeAddresses As Object
Dim RangeAddressesAsString As String
Dim CN As String, s$
Dim CellAddress As New com.sun.star.table.CellAddress
Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress

CN = TC.AbsoluteName
CellRanges = TC.queryDependents(false)
RangeAddresses() = CellRanges.getRangeAddresses()
RangeAddressesAsString = CellRanges.getRangeAddressesAsString()

For i = 0 to uBound(RangeAddresses())
CellRangeAddress = RangeAddresses(i)
'Do something with Addresses if required
Next i
s = "Cell " & CN & " is refered to by "
MsgBox( s & RangeAddressesAsString,, "Cell References")   'get references on screen
Cell = TS.getCellRangeByName("A1")                  'paste references in empty spreadsheet cell
Cell.String = s & RangeAddressesAsString

End Sub


Sub ShowDependents (TC As Object, TS As Object)

Dim CellAddress As New com.sun.star.table.CellAddress
Dim Shown As Boolean

CellAddress = TC.getCellAddress()
Shown = TS.showDependents(CellAddress)
MsgBox(Shown,,"Dependent Arrows Shown?")

End Sub

_________________
AOO 3.4.1
Windows Vista Home Premium SP2
Back to top
View user's profile Send private message
satabau
General User
General User


Joined: 12 Mar 2012
Posts: 34

PostPosted: Thu Aug 30, 2012 11:55 am    Post subject: Reply with quote

Ok using XFormulaQuery is the easiest way. Thank you all for the answers
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