Hyperlinks to particular sheet and cell in a different workbook

[ashu_104]

Hi All,

I am facing a small issue in defining the hyperlinks to a particular
cell number in a sheet of a different workbook.

For example i have a workbook book1.xls having 5
sheets(sheet1,sheet2, ...,sheet5)

now i want to create an hyperlink in book2.xls such that it opens -
book1.xls(sheet2 and defined cell)

currently i use the formula :

sheet.write(100,1,Formula('HYPERLINK("problem/Book1.xls";"link")'))

which provides me the link to the book1.xls but not to the particular
sheet and cell I want.

I will be greatful to any kind of help and suggestions made.

---
Kind Regards.
Ashu

[John Machin]

You type into your sourcefile editor exactly what you would type into Excel or OpenOffice Calc or Gnumeric to achieve what you want to do. If you have trouble getting manually inserted hyperlinks to do what you want to do, google is your friend. E.g. google("excel hyperlink function examples") and click on the first result.

[ashu_104]

Hey John,

Thanks for the reply i already tried doing that, as Google specifies
the formula for the hyperlink turns out to be:

=HYPERLINK("[book1.xls]Sheet2!A1","CLICK HERE")

which i write in my python code as:
sheet.write(100,1,xlwt.Formula('HYPERLINK("[book1.xls]sheet2!
A1";"CLICK")'))

But in this case book1.xls gets open and says Refrence is not valid.

so as i mentioned in my previous message i can link the xls file but
not the desired sheet and cell. So it will be really helpful if u can
provide me with an example for this.

----
Kind Regards,
Ashish

[John Machin]

On Sunday, February 26, 2012 9:36:16 AM UTC+11, ashu_104 wrote:

On Feb 25, 11:23 pm, John Machin <sjmac...@lexicon.net> wrote:
> You type into your sourcefile editor exactly what you would type into Excel
> or OpenOffice Calc or Gnumeric to achieve what you want to do. If you have
> trouble getting manually inserted hyperlinks to do what you want to do,
> google is your friend. E.g. google("excel hyperlink function examples") and
> click on the first result.

Hey John,

Thanks for the reply i already tried doing that, as Google specifies
the formula for the hyperlink turns out to be:

=HYPERLINK("[book1.xls]Sheet2!A1","CLICK HERE")

which i write in my python code as:
sheet.write(100,1,xlwt.Formula('HYPERLINK("[book1.xls]sheet2!
A1";"CLICK")'))

But in this case book1.xls gets open and says Refrence is not valid.

Consider the possibility that there should be a SPACE character between "sheet" and "2".

[ashu_104]

Tried but it still says that Reference is not valid :-(

[John Machin]

As far as I can tell, the only way that that can happen when the target file exists is that the sheet names don't match (comparison is NOT case-sensitive).

For example, with the following script and a Book1.xls that is what you get by saving a new empty XLS file (has 3 sheets  (Sheet 1, ..., Sheet 3)), only the first hyperlink fails.

import xlwt
b = xlwt.Workbook()
s = b.add_sheet('x')
s.write(1, 1, xlwt.Formula('HYPERLINK("[book1.xls]NoSuchSheet!D6","Click here")'))
s.write(2, 2, xlwt.Formula('HYPERLINK("[book1.xls]Sheet1!$E$5","Click here")'))
s.write(4, 4, xlwt.Formula('HYPERLINK("[book1.xls]Sheet1!F6","Click here")'))
s.write(10, 10, xlwt.Formula('HYPERLINK("[book1.xls]Sheet3!B2","Click here")'))
s.write(12, 12, xlwt.Formula('HYPERLINK("[book1.xls]sheet3!C3","Click here")'))
b.save('foo.xls')

Please supply the shortest script and smallest xls file that together illustrate your problem.

[John Yeung]

John Machin <sjmac...@lexicon.net> wrote:
>> > You type into your sourcefile editor exactly what
>> > you would type into Excel

ashu_104 <ashu.lo...@gmail.com> wrote:
>> Thanks for the reply i already tried doing that,
>> as Google specifies the formula for the hyperlink
>> turns out to be:
>>
>> =HYPERLINK("[book1.xls]Sheet2!A1","CLICK HERE")
>>
>> which i write in my python code as:
>> sheet.write(100,1,xlwt.Formula('HYPERLINK("[book1.xls]sheet2!
>> A1";"CLICK")'))

Why did you switch from a comma to a semicolon to separate the parameters?

John Y.

[John Machin]

On Monday, February 27, 2012 2:30:34 AM UTC+11, John Yeung wrote:

John Machin  wrote:
>> > You type into your sourcefile editor exactly what
>> > you would type into Excel

ashu_104  wrote:
>> Thanks for the reply i already tried doing that,
>> as Google specifies the formula for the hyperlink
>> turns out to be:
>>
>> =HYPERLINK("[book1.xls]Sheet2!A1","CLICK HERE")
>>
>> which i write in my python code as:
>> sheet.write(100,1,xlwt.Formula('HYPERLINK("[book1.xls]sheet2!
>> A1";"CLICK")'))

Why did you switch from a comma to a semicolon to separate the parameters?

Excel forces the user to use the locale's "list separator" as the arg separator in formulas. pyExcelerator forced the user to use semicolon. I changed xlwt to allow either or both.