Create Excel Hyperlinks in Python

Question

I am using win32com to modify an Excel spreadsheet (Both read and edit at the same time) I know there are other modules out there that can do one or the other but for the application I am doing I need it read and processed at the same time.

The final step is to create some hyperlinks off of a path name. Here is an Example of what I have so far:

import win32com.client


excel = r'I:\Custom_Scripts\Personal\Hyperlinks\HyperlinkTest.xlsx'

xlApp = win32com.client.Dispatch("Excel.Application")
workbook = xlApp.Workbooks.Open(excel)
worksheet = workbook.Worksheets("Sheet1")


for xlRow in xrange(1, 10, 1):
    a = worksheet.Range("A%s"%(xlRow)).Value
    if a == None:
        break
    print a

workbook.Close()

I found some code for reading Hyperlinks using win32com:

sheet.Range("A8").Hyperlinks.Item(1).Address

but not how to set hyperlinks

Can someone assist me?

SiHa · Accepted Answer · 2016-09-30 12:52:09Z

2

Borrowing heavily from this question, as I couldn't find anything on SO to link to as a duplicate...

This code will create a Hyperlink in cells A1:A9

import win32com.client

excel = r'I:\Custom_Scripts\Personal\Hyperlinks\HyperlinkTest.xlsx'

xlApp = win32com.client.Dispatch("Excel.Application")
workbook = xlApp.Workbooks.Open(excel)
worksheet = workbook.Worksheets("Sheet1") 

for xlRow in xrange(1, 10, 1):
    worksheet.Hyperlinks.Add(Anchor = worksheet.Range('A{}'.format(xlRow)),
                             Address="http://www.microsoft.com",
                             ScreenTip="Microsoft Web Site",
                             TextToDisplay="Microsoft")
workbook.Save()
workbook.Close()

And here is a link to the Microsoft Documentation for the Hyperlinks.Add() method.

answered Sep 30, 2016 at 12:52

SiHa

8,49713 gold badges39 silver badges48 bronze badges

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

4 Comments

John Smith Over a year ago

TextToDisplay is not working as intended. The address shows up as is without being masked by "Microsoft" text. Does anyone know why this is happening?

SiHa Over a year ago

You would be better off asking a new question, if this solution is not working for you. This answer is four years old and the office API's may well have moved on. If there is a new-version issue, an edit to this answer to that fact would be welcome,

John Smith Over a year ago

Well running your code as is produces excel file with http:/www.microsoft.com repeated 9 times without any text masking. If you don't mind, can you run it at your end and see if you are getting a different solution. I feel something minor is missing hence I didn't ask a new question. If you are getting a different solution (correct one with masking) then perhaps I will ask a new question..

SiHa Over a year ago

I'm sorry, I'm not in a position where I can run this code at the moment. It has clearly worked for at least two other people, so if it's not working for you then the best thing is honestly to ask another question stating how this answer is not working for you to avoid it getting possibly marked as a duplicate

Collectives™ on Stack Overflow

Create Excel Hyperlinks in Python

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related