[Solved] .setContent stripping out exclamation point (!) from string

[arazi8921]

Hello,

If anyone can help, this is driving me crazy! I am trying to update a named range (named "Print_This") with a new range value.

The OpenOffice BASIC code below works great-- except that the exclamation point (!) in the concatenated string keeps getting STRIPPED AWAY no matter how I try to do it.

As viewed under the 'Manage Names' dialog:
DESIRED STRING: Printout_Page!R1C1:R175C9
ACTUAL STRING: Printout_Page R1C1:R175C9

Every time, the exclamation point in the string I create gets stripped away somehow. I have tried: (1) creating the text string with the exclamation point as part of the text, (2) using chr(33), (3) using a period instead of the exclamation point, and (4) adding a backslash \ in front of the exclamation point. NONE of these variations work.

The error I get running the code is the "Invalid Range" error, which makes sense when I look at the named ranges in the 'Manage Names' dialog and see that the range reference has the space instead of the exclamation point.

------ Begin Sample Code ---------------
dim tAllRanges
dim tChangeRange
dim tRangeString

tAllRanges = ThisComponent.NamedRanges
tChangeRange = tRanges.getByName("Print_This")
tRangeString = "Printout_Page"&chr(33)&"R1C1:R"&trim(str(some_integer))&"C9"

tChangeRange.setContent(tRangeString)
------ End Sample Code ---------------

If anyone can tell me the secret that I can't figure out I would be very grateful.... THANK YOU!!

[FJCC]

I believe that the correct separator between the sheet name and the cell range is a period and not an exclamation point. Try constructing Printout_Page.R1C1:R175C9
I don't know whether the RC syntax is valid in setContent(). It might be.

[arazi8921]

Thanks! I did try that earlier (before submitting my question) and unfortunately it just updated the range as: Printout_Page.R1C1:R175C9

It just kept the period and did not replace it with the needed exclamation point for a named range. And that range, Printout_Page.R1C1:R175C9, with the period, is not a valid range in the Manage Names dialog.

Thanks for the idea to try.

[FJCC]

This bit of code works for me in OpenOffice. Are you sure the RC notation works in setContent()? It does not work for me in OpenOffice.

Code: Select all

  oNamedRanges = ThisComponent.NamedRanges
  oObj1 = oNamedRanges.getByName("FirstRange")
  oObj1.setContent("Sheet1.H1:I3")

[arazi8921]

This bit of code works for me in OpenOffice. Are you sure the RC notation works in setContent()? It does not work for me in OpenOffice.

Code: Select all

  oNamedRanges = ThisComponent.NamedRanges
  oObj1 = oNamedRanges.getByName("FirstRange")
  oObj1.setContent("Sheet1.H1:I3")

Thank you again. Yes, I constructed the string in A1 Notation and it did register this time! Since I am using R1C1 notation all of the time I do think that was the problem.

In the Named Ranges (Manage Names) dialog it changed the reference from the A1 notation I used in the code (at your suggestion) to a Base 0 reference of: Print_List!RC:R[174]C[9]

Which is still not exactly like all of the other named ranges that are listed in "regular" R1C1 notation Base 1 (like Print_List!RC:R175C10)

But it doesn't matter how it is shown, the bottom line is that it definitely WORKS now!

Thanks again! I will mark this as SOLVED. And I'll remember in the future about the A1 vs R1C1 Notation differences that may crop up in the code sometimes when things aren't working.