"error : Reference is not valid" while using hyperlinks on excel files in "windows "

[NikunjBadjatya]

Hi,
I am currently working on centos 5.4 as my linux os. I have used xlwt,
xlrd and xlutils modules in my project to make excel files through
python. I can open xl files easily in centos, but when i open them in
windows , the hyperlinks of the sheets is not working the way it
should. it flags an error "Reference is not valid" .

Any idea as to how to proceed on this??

Thanks a lot !!

[John Machin]

On 5/01/2010 12:27 AM, NikunjBadjatya wrote:
> Hi,
> I am currently working on centos 5.4 as my linux os. I have used xlwt,
> xlrd and xlutils modules in my project to make excel files through
> python. I can open xl files easily in centos,

Open using what? xlrd? OpenOffice.org's Calc? Gnumeric?

> but when i open them in
> windows ,

Open using what? xlrd? Excel? OpenOffice.org's Calc? Gnumeric?

> the hyperlinks of the sheets is not working the way it
> should.

Please say what are "the hyperlinks of the sheets". In particular, do
you have any hyperlinks that refer to a different XLS file using paths
e.g. /usr/data/foo.xls ?

> it flags an error "Reference is not valid" .

and what exactly is the complained-about reference?

> Any idea as to how to proceed on this??

Cut a copy of your code down to the bare minimum needed to create an XLS
file that reproduces the problem. Publish the resulting code. State what
you believe "working the way it should" looks like. Run the script and
state what happens with (a) centos (b) windows. Answer the 4 questions
above.

[NikunjBadjatya]

Hi,

> Open using what? xlrd? OpenOffice.org's Calc? Gnumeric?

in CentOS:
Openoffice
in windows :
MSoffice

> the hyperlinks of the sheets is not working the way it should

I have 5 sheets in the same xls file. hyperlinks that are used : for
going from one sheet to another ( say sheet1 to sheet5) and hyperlinks
that direct to the different parts of the same sheet. ( row3 to row 6,
etc. ). No other type of links.

> it flags an error "Reference is not valid"

>working the way it should

when i open xls file in CentOS through openoffice, the hyperlinks are
working fine.
but when i open the same xls file in windows through MSoffice,
everything else is ok apart from the hyperlinks. when i click on them,
it flags an error : "reference is not valid "

code:
n="HYPERLINK"
cp.write(4,1,xl.Formula(n+'("#SheetName.A13";"Linked
Here")'),linkfontstyle)
# links on the same sheet on (column A and row 13)

mysheet1.write(9, 2,xl.Formula(k + '("#SheetName";"Linked Here")'),
linkfontstyle)
# links on diff sheet.

I hope i have given sufficient information now..!!

Thanks for your concern

[John Machin]

On 5/01/2010 3:27 PM, NikunjBadjatya wrote:
> Hi,
>> Open using what? xlrd? OpenOffice.org's Calc? Gnumeric?
>
> in CentOS:
> Openoffice
> in windows :
> MSoffice
>
> > the hyperlinks of the sheets is not working the way it should
>
> I have 5 sheets in the same xls file. hyperlinks that are used : for
> going from one sheet to another ( say sheet1 to sheet5) and hyperlinks
> that direct to the different parts of the same sheet. ( row3 to row 6,
> etc. ). No other type of links.
>
>> it flags an error "Reference is not valid"
>> working the way it should
> when i open xls file in CentOS through openoffice, the hyperlinks are
> working fine.
> but when i open the same xls file in windows through MSoffice,
> everything else is ok apart from the hyperlinks. when i click on them,
> it flags an error : "reference is not valid "
>
> code:
> n="HYPERLINK"
> cp.write(4,1,xl.Formula(n+'("#SheetName.A13";"Linked

That seems somewhat obfuscatory compared with

cp.write(4,1,xl.Formula('HYPERLINK("#SheetName......

> Here")'),linkfontstyle)
> # links on the same sheet on (column A and row 13)

The behaviour difference that you describe is nothing to do with
different operating systems (and nothing to do with xlwt, either); it's
caused by the difference between applications and is quite expected.

Go back to our discussion of last month:

John> Even better, you can do this in the HYPERLINK() function, too! Enclose
John> it in quotes i.e. HYPERLINK("#Sheet2!D7", "Try this!")

Nikunj> Hey, Thanks alot for your reply..!! i successfully tried this.
Nikunj> working just fine..!!

Excel expects the sheet-name to be separated from the
column-row-reference by a "!". OOo Calc is happy with a "!" but also
permits "." -- tested on the Windows implementation of OOo Calc but I'd
be very surprised if "!" didn't work with other implementations.

>
> mysheet1.write(9, 2,xl.Formula(k + '("#SheetName";"Linked Here")'),
> linkfontstyle)
> # links on diff sheet.

What is k??? Why not use n??

You have only a sheet name, without a column-row-reference. Excel
doesn't support this. OOo calc appears to interpret this as referring to
the current cursor position on the named sheet. This may well be A1 when
the XLS is first opened, but it's highly unlikely to remain so unless
you manage somehow to constrain the user. It doesn't seem to be very
useful functionality to me, and in any case Excel won't do it ... use
"#SheetName!A1"

General comments:

(1) If you want your XLS files to work with more that one UI, you need
to restrict yourself to the intersection of the functionality sets of
those UIs.

(2) Even with only 1 UI to consider, before writing lots of code calling
xlwt, try new things out in the UI(s) first -- if you can't get all the
UIs to do something, then xlwt won't do it either.

HTH
John

[NikunjBadjatya]

Hey,
I Really forgot to reply back to this thread..!! My apologies..!

> mysheet1.write(9, 2,xl.Formula(k + '("#SheetName";"Linked Here")'),
> linkfontstyle)
> # links on diff sheet.
>What is k??? Why not use n??

I have changed the code with "n"

John > it in quotes i.e. HYPERLINK("#Sheet2!D7", "Try this!")

If I do like the one mentioned above(using an ! symbol), it wont work
in CentOS with OPENOFFICE.!!
While if i do like ("#sheet2.D7","try this!") using an (.) symbol. it
wont work with MSExcel in windows..
I really cant figure out what needs to be done if i want to use it in
both...!!

Thanks a lot..!!

[John Machin]

On 8/01/2010 7:58 PM, NikunjBadjatya wrote:
> Hey,
> I Really forgot to reply back to this thread..!! My apologies..!

>

> John > it in quotes i.e. HYPERLINK("#Sheet2!D7", "Try this!")
>
> If I do like the one mentioned above(using an ! symbol), it wont work
> in CentOS with OPENOFFICE.!!

When I start up OOo Calc on my (Windows XP) box, and click on Help then
About, I see:

OpenOffice.org 3.0.1
OOO300m15 (build: 9379)

What do you get with OOo Calc on your Centos box?

[NikunjBadjatya]

> What do you get with OOo Calc on your Centos box?

After clicking on help -> About OO,
I get to see :
OpenOffice.org 2.3
6.11.el5_4.1

[John Machin]

I'm looking forward to hearing from you again after you've upgraded.

[NikunjBadjatya]

> I'm looking forward to hearing from you again after you've upgraded.

Hi John,
I have successfully upgraded to latest OO.
The hyperlinks are working fine now..!!
Cheers,
Thanks alot !!

Nikunj
Bangalore, INDIA