Re: [libreoffice-users] Range names

Den tis 5 maj 2020 kl 02:07 skrev Steve Edmonds <steve.edmonds@ptglobal.com
> :

> On 04/05/2020 09:09, Johnny Rosenberg wrote:
>
> Den sön 3 maj 2020 kl 22:39 skrev Steve Edmonds <
> steve.edmonds@ptglobal.com>:
>
> > On 04/05/2020 08:24, Johnny Rosenberg wrote:
> >
> > Den sön 3 maj 2020 kl 21:39 skrev Steve Edmonds <
> > steve.edmonds@ptglobal.com>:
> >
> > > Hi Johnny.
> > > I came up against this issue last year after needing to open an excel
> > > sheet in Calc.
> > > Excel allows range names that Calc doesn't so the sheet wouldn't work. I
> > > wanted to rename the offending excel ranges.
> >
> > Out of curiosity, I would like to know what kinds of range names that is.
> > Can you give me some examples?
> >
> > I filed a bug; https://bugs.documentfoundation.org/show_bug.cgi?id=130577
>
> So Excel allows for a leading backslash, LibreOffice does not. I'l have a
> look at it tomorrow, if I remember to.
>
>
> > Does your name changing macro change the name through range name and
> > formulae using that range name to be changed?
> Do you mean the code I wrote below or the macro that I mentioned before
> that?
> The macro I mentioned first, the one with the obvious bug, is not finished
> yet, but I think it will be tomorrow (and hopefully bug free too, almost).
> It loops through all defined cell range names and cell names. For every
> name, it finds its corresponding reference, for instance ”$Sheet1.$A$1”.
> Then it search all sheets for that reference and replaces it with the cell
> range name. So if cell A1 has the name ”Length” and cell A2 is
> ”=$A$1*$A$1”, then the macro will change A2 to ”=Length*Length”. However,
> it will also change things like ”$A$123”, but I think I solved that one
> now. I'll try to finish it tomorrow.
>
> The code I wrote below is just a starting point if you want to create your
> own macro to change all the names in that Excel file. I don't know if
> that's even possible, maybe you need to do that in Excel, since those names
> are invalid in LibreOffice Calc. I don't know yet, I'll give it a go
> tomorrow, I think.
>
> I think I might have to do my replacement of range names in the excel file
> as by the time Calc opens it the import filter has messed it up when range
> names are offending.

I just had some time to check it out, the file you attached to that bug
report, and we were right, you can't fix it automatically in LibreOffice
Calc. The problem is that some of the names are broken (refers to #REF!).
Of course you could do it manually if you know what all the names are. In
this case there are 34 names, and not all of them are broken, so it
shouldn't take too long if you know the references for all the broken
names. But I guess it's easier to fix it in Excel, either manually or with
a VBA macro. You could, for instance, make a VBA macro that replace all
backslashes with something else, like yet another underscore (first make
sure there will be no doublets) or just something unique, like _Ch92_,
_Asc92_,  _bsol_ or whatever, making the risk of doublets minimal.


Kind regards

Johnny Rosenberg


> Kind regards
>
> Johnny Rosenberg
>
>
> > Regards, Steve
> >
> >
> > I actually wrote a macro that replaces range references with existing
> > range names, but I can see a few bugs that I don't have a solution for at
> > the moment.
> > At this stage, the macro goes through all existing range names (and cell
> > names), find their corresponding references and then do a search and
> > replace.
> > For instance, if there's a name ”MyName” for ”$Sheet1.$A$1:$B$7”, every
> > occurrence of ”$Sheet1.$A$1:$B$7” will be replaced with ”MyName”. That
> > means that if it, for instance, finds ”$Sheet1.$A$1:$B$70” (note the extra
> > ”0” at the end), the result will be ”MyName0”, which is not meaningful… But
> > I'm working on it.
> >
> > When dealing with ranges, I think this problem is rarely going to happen.
> > If ”$Sheet1.$A$1:$B$7” has a name, why would we find the reference
> > ”$Sheet1.$A$1:$B$70”? But it's probably a much bigger problem with single
> > cells.
> >
> > > I couldn't find a solution like you seek.
> >
> > If you want to edit names with a macro, you can find and edit all defined
> > names like so:
> >
> > *Sub CorrectNames *
> > * For Index=0 To ThisComponent.NamedRanges.getCount()-1*
> > *  Name=ThisComponent.NamedRanges.getByIndex(Index).getName()*
> >
> > *  DoSomething(Name) *
> > *  ThisComponent.NamedRanges.getByIndex(Index).setName(Name)*
> >
> > * Next Index *
> >
> > *End Sub *
> >
> >
> > *Sub DoSomething(ByRef Name) *
> >
> > *  ' Enter Name changing, mind blowing code below. *
> >
> > *End Sub *
> >
> > Something like that.
> >
> >
> > Kind regards
> >
> > Johnny Rosenberg
> >
> >
> > > steve
> > >
> > > On 03/05/2020 19:35, Johnny Rosenberg wrote:
> > > > Hi!
> > > >
> > > > I'm just wondering if I'm missing something.
> > > > Let's say I have a spreadsheet (I actually have several…) with a lot of
> > > > cells and ranges already filled with data and formulas. One day I
> > > discover
> > > > the range naming feature, so I name a few cell ranges. Shouldn't there
> > > be
> > > > some easy way to replace every occurrence of those cell ranges in all
> > > my
> > > > formulas? And when I say easy, I mean easier than doing search and
> > > replace
> > > > on each one of them one by one. I can't find such a feature. Is there
> > > an
> > > > extension for it?
> > > >
> > > > I'm going to write a macro for it, but I find it meaningless to write a
> > > > macro for something that's already there, so that's why I ask this
> > > question
> > > > in the first place.
> > > >
> > > >
> > > > Kind regards
> > > >
> > > > Johnny Rosenberg
> > >
> > >
> > >
> > > --
> > > To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
> > > Problems?
> > > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > > Posting guidelines + more:
> > > https://wiki.documentfoundation.org/Netiquette
> > > List archive: https://listarchives.libreoffice.org/global/users/
> > > Privacy Policy: https://www.documentfoundation.org/privacy

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy