| View previous topic :: View next topic |
| Author |
Message |
satabau
General User
Joined: 12 Mar 2012
Posts: 34
|
 Posted: Wed Aug 01, 2012 8:51 am Post subject: [Solved]Know the referenced cells by formulas in other cells |
 |
|
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 |
|
 |
patel
Power User
Joined: 14 Apr 2012
Posts: 54
Location: Italy
|
| Posted: Wed Aug 01, 2012 9:16 pm Post subject: |
|
|
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 |
|
|
B Marcelly
Super User
Joined: 12 May 2004
Posts: 1414
Location: France
|
| Posted: Fri Aug 03, 2012 4:11 am Post subject: |
|
|
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 / OpenOffice.org 3.4.1 en-US + langpacks, MS-Windows XP Home SP3
This forum is unusable, use instead Apache OpenOffice forums |
|
| Back to top |
|
|
Keith Wild
General User
Joined: 17 Jan 2011
Posts: 15
Location: London, UK
|
| Posted: Fri Aug 03, 2012 12:04 pm Post subject: |
|
|
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 |
|
|
satabau
General User
Joined: 12 Mar 2012
Posts: 34
|
|
| Back to top |
|
|
B Marcelly
Super User
Joined: 12 May 2004
Posts: 1414
Location: France
|
| Posted: Mon Aug 06, 2012 11:03 am Post subject: |
|
|
Good find. 
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 / OpenOffice.org 3.4.1 en-US + langpacks, MS-Windows XP Home SP3
This forum is unusable, use instead Apache OpenOffice forums |
|
| Back to top |
|
|
Keith Wild
General User
Joined: 17 Jan 2011
Posts: 15
Location: London, UK
|
| Posted: Wed Aug 08, 2012 4:10 am Post subject: |
|
|
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 |
|
|
satabau
General User
Joined: 12 Mar 2012
Posts: 34
|
| Posted: Thu Aug 30, 2012 11:55 am Post subject: |
|
|
| Ok using XFormulaQuery is the easiest way. Thank you all for the answers |
|
| Back to top |
|
|
|
|
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
|
