Error when using Python to create hyperlink within a worksheet in Excel

Question

I am trying add hyperlinking functionality in my Excel documents by being able to click on a cell and it will take me to another part of the Excel document. I have code below that should take me to cell A21 when I click on A1. The code executes fine, but when I click on the link, a window pops up saying "Cannot open the specified file." Is there a problem with the way I'm referencing the cell? Or is there a better way to accomplish this?

from win32com.client import Dispatch
excel = Dispatch('Excel.Application')

def main():
    CreateLink()

def CreateLink():
    cell_location = excel.Worksheets(1).Cells(1,1)
    cell_destination = excel.Worksheets(1).Cells(21,1)
    cell_text = "Cell A21"
    excel.Worksheets(1).Hyperlinks.Add(Anchor=cell_location, Address=cell_destination, TextToDisplay=cell_text)

if __name__ == '__main__':
    main()

NorthCat · Accepted Answer · 2014-07-01 17:51:21Z

2

Try this:

def CreateLink():
    excel.Worksheets(1).Cells(1,1).Value = '=HYPERLINK(A21,"Cell A21")'

answered Jul 1, 2014 at 17:51

NorthCat

10k16 gold badges51 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Shankar Kumar Over a year ago

Yes same message. Could there be a problem with the file path? Is it working on your machine?

Shankar Kumar Over a year ago

But if I keep the destination cell empty, I don't see the message. When I add text to the destination cell, the message comes. Of course I would prefer having text.

NorthCat Over a year ago

What do you expect to get when you click on a link?

Shankar Kumar Over a year ago

Excel should navigate to the cell. i.e. Make it the active cell, and move down or up so the cell is visible if needed.

NorthCat Over a year ago

Ok, try this: excel.Worksheets(1).Cells(20,1).Value = '=HYPERLINK("#Sheet1!A21","Cell A21")'

|

jmcnamara · Accepted Answer · 2017-10-02 11:39:54Z

Use xlsxwriter module to do it as simple as it is, have a look at the documentation

# Link to a cell on the current worksheet.
worksheet.write_url('A1',  'internal:Sheet2!A1')

# Link to a cell on another worksheet.
worksheet.write_url('A2',  'internal:Sheet2!A1:B2')

# Worksheet names with spaces should be single quoted like in Excel.
worksheet.write_url('A3',  "internal:'Sales Data'!A1")

# Link to another Excel workbook.
worksheet.write_url('A4', r'external:c:\temp\foo.xlsx')

# Link to a worksheet cell in another workbook.
worksheet.write_url('A5', r'external:c:\foo.xlsx#Sheet2!A1')

# Link to a worksheet in another workbook with a relative link.
worksheet.write_url('A7', r'external:..\foo.xlsx#Sheet2!A1')

# Link to a worksheet in another workbook with a network link.
worksheet.write_url('A8', r'external:\\NET\share\foo.xlsx')

James McComb · Accepted Answer · 2018-08-22 17:34:41Z

0

# Make sure you include single quotes when you reference another sheet in a Workbook hyperlink.

# example code to link the same cell on two different worksheet

import win32com.client as win32com

output_filename = 'MyExcelWorkbook.xlsx'
excel = win32com.gencache.EnsureDispatch('Excel.Application')
wb    = excel.Workbooks.Open(output_filename)

worksheet1name = wb.Worksheets(1).Name
worksheet2name = wb.Worksheets(2).Name
ws_out         = wb.Worksheets.(worksheet1name)

for rowIndex in range(numRows):
    rangeString = 'A' + str(rowIndex)
    cell_destination = '\'' + sheet2name + '\'' + '!' + 'A' + str(rowIndex)
    ws_out.Hyperlinks.Add(Anchor=ws_out.Range(rangeString), Address='', SubAddress=cell_destination)

answered Aug 22, 2018 at 17:34

James McComb

11

2 Comments

Vlastimil Ovčáčík Over a year ago

Hi James, consider adding some explanation of your solution. Its not immediately clear how this solves the Shankar's problem.

José Chamorro Over a year ago

The answer could have been redacted a little bit better, but this is the one that worked for me! Thanks!

Collectives™ on Stack Overflow

Error when using Python to create hyperlink within a worksheet in Excel

3 Answers 3

8 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related