Issue 31766

Summary: .getReferencePosition() returns invalid data
Product: App Dev Reporter: robberbaron <robberbaron>
Component: apiAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: diane, issues
Version: 3.3.0 or older (OOo)   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---
Attachments:
Description Flags
Files to replicate error
none
3rd try upload of file for robberbaron. last try produced apache/tomcat error. none

Description robberbaron 2004-07-20 12:18:18 UTC
The .getReferencePosition() returns invalid data when used on an Calc workbook 
that was converted from Excel97.  Function works correctly on a newly created 
Calc sheet.  

There are about 10 named ranges in this book on various sheets. When doing 
an 'insert names' to list their details, there are 3 range names that have no 
range details; these appear to be the names of user functions defined. Note 
that the original Excel document doesnt have these range names, just the 
functions.

--------------------------------
  REM The oNamedRange object supports the XNamedRange interface
  oNamedRange = ThisComponent.NamedRanges.getByName(rngname)
  Print "Named range content = " & oNamedRange.getContent()   
   '#### CORRECT VALUE PRINTED

  REM a com.sun.star.table.CellAddress service is available from
  REM getReferencePosition(). 
  oCellAddr = oNamedRange.getReferencePosition()
  print oCellAddr.Sheet,oCellAddr.Row,oCellAddr.Column
   '#####  Prints 0  0  0
------------------------------------

 I can send the Excel & Calc files if desired.
Comment 1 robberbaron 2004-07-20 12:27:58 UTC
Created attachment 16610 [details]
Files to replicate error
Comment 2 stephan.wunderlich 2004-07-21 14:37:23 UTC
sw->robberbaron: the attached file seems to be corrupted, could you try to
attach it again please ? 
Comment 3 robberbaron 2004-07-22 01:07:13 UTC
Updated the scx file to remove the macro errors.  It will show Msgboxes on 
load as it recalc formulas that have the problem.
Comment 4 robberbaron 2004-07-22 01:34:35 UTC
Sorry, cant update the attachment.....  how do I do this ?

Also should lower priority to 4 as there is a workaround.
Comment 5 diane 2004-07-22 05:24:33 UTC
Rec'd via direct email, as robberbaron shows difficulty in creating additional
attachment. (likely due to glitch reported in Issue 23650)

Paste of robberbaron's comments from mail message:

"I tried modifying the Issue on oo.org but could find how to add a new
attachement.

So replied to your msg. Hope this is Ok."
Comment 6 diane 2004-07-22 05:43:50 UTC
Created attachment 16668 [details]
3rd try upload of file for robberbaron. last try produced apache/tomcat error.
Comment 7 diane 2004-07-22 13:37:02 UTC
my experiences while trying to attach that file did not go very smoothly. The
first time my connection died in the midst of the upload after a long wait, the
second time the apache/tomcat error showed, and the third time there was a long
wait between when the file finished uploading and when OOo came back at me. If
it needs to be uploaded again, please give a shout. I'll try again. I was not
able to add these notes then either, as things weren't handshaking well at all.
Thought I'd try again in a few hours. Set priority from P2 to P3. Added myself
to cc, in case more aid is needed with that attachment.
Comment 8 stephan.wunderlich 2004-07-22 15:49:09 UTC
SW->NN: executing the macro in the attached document (its executed on load)
shows that for the named ranges "trans_latest_issue" and "trans_top" the method
"getContent" returns the correct value, but "getReferencePosition" returns 0,0,0 
Comment 9 yellek 2006-03-13 06:29:00 UTC
I'm seeing this as well. Named ranges on a sheet downloaded from
http://www.boardgamegeek.com/fileinfo.php?fileid=15693 return 0 as the sheet
number when calling getReferencePosition(). Deleting and re-adding the named
range causes the function to work correctly.

The function shown below causes an error when attempting to get the Cities named
range from that sheet which is on the AST tab. It incorrectly goes to the
instructions tab (tab 0) and fails to find the range.

Function getNamedRange(rngname As String, Optional oDoc)
	Dim oSheet 'Sheet containing the named range
	Dim oNamedRange 'The named range object
	Dim oCellAddr 'Address of the upper left cell in the named range
	Dim oRanges 'All of the named ranges

	If IsMissing(oDoc) Then oDoc = ThisComponent
	oRanges = oDoc.NamedRanges

	If NOT oRanges.hasByName(rngname) Then
		MsgBox "Sorry, the named range " & rngname & _
		" does not exist" & CHR$(10) & _
		"Current named ranges = " & CHR$(10) & _
		Join(oRanges.getElementNames(), CHR$(10))
		Exit Function
	End If

	REM The oNamedRange object supports the XNamedRange interface
	oNamedRange = oRanges.getByName(rngname)

	Print rngname & " = " & oNamedRange.getContent()

	oCellAddr = oNamedRange.getReferencePosition()

	REM Now, get the sheet that matters!
	oSheet = oDoc.Sheets.getByIndex(oCellAddr.Sheet)
	Print "Is on Sheet " & oSheet.Name


	REM You can then use the current controller
	REM to select what must be selected.
	REM select ( VARIANT )
	REM setActiveSheet ( OBJECT )
	REM setFirstVisibleColumn ( LONG )
	REM setFirstVisibleRow ( LONG )

	oDoc.getCurrentController().setActiveSheet(oSheet)
	REM The sheet can return the range based on the name
	REM oSheet.getCellRangeByName(rngname)
	REM The sheet can also return a range by position, if you know it.
	REM This selects the ENTIRE range
	Dim oRange
	oRange = oSheet.getCellRangeByName(rngname)
	getNamedRange = oRange
End Function
Comment 10 yellek 2006-03-13 06:33:28 UTC
I should add that this is on the 2.0.2 stable build running on Windows XP
Comment 11 Marcus 2017-05-20 11:27:38 UTC
Reset assigne to the default "issues@openoffice.apache.org".