This works for me:
wbook.active['A8'].hyperlink = "http://www.espn.com"
wbook.active['A8'].value = 'ESPN'
wbook.active['A8'].style = "Hyperlink"
This works for me:
wbook.active['A8'].hyperlink = "http://www.espn.com"
wbook.active['A8'].value = 'ESPN'
wbook.active['A8'].style = "Hyperlink"
If wanting to use Excel's built in hyperlink function directly, you can use the following to format as a link:
'=HYPERLINK("{}", "{}")'.format(link, "Link Name")
e.g. ws.cell(row=1, column=1).value = '=HYPERLINK("{}", "{}")'.format(link, "Link Name")
Extracting Hyperlinks From Excel (.xlsx) with Python - Stack Overflow
python - Create a hyperlink to a different Excel sheet in the same workbook - Stack Overflow
python 3.x - openpyxl: Return absolute path from excel hyperlink openpyxl windows environment - Stack Overflow
Setting Excel properties (Hyperlink Base) via python
Hello,
I have an excel file with hyperlinks (these hyperlinks lead to a website). I want to python to open one of the hyperlinks. When I execute the script I would expect the websites to open in Microsoft edge, but instead, nothing happens. Here is my code:
import openpyxl
wb = openpyxl.load_workbook("C:\\Users\\rfog\\OneDrive\\Documents\\Python Excel\\pythontest.xlsx")
ws = wb['Sheet1']
ws.cell(row=1, column=2).hyperlink.target
Any suggestions?
This is possible with openpyxl:
import openpyxl
wb = openpyxl.load_workbook('yourfile.xlsm')
ws = wb['Sheet1']
# This will fail if there is no hyperlink to target
print(ws.cell(row=2, column=1).hyperlink.target)
Starting from at least version openpyxl-2.4.0b1 this bug https://bitbucket.org/openpyxl/openpyxl/issue/152/hyperlink-returns-empty-string-instead-of was fixed. Now it's return for cell Hyperlink object:
hl_obj = ws.row(col).hyperlink # getting Hyperlink object for Cell
#hl_obj = ws.cell(row = r, column = c).hyperlink This could be used as well.
if hl_obj:
print(hl_obj.display)
print(hl_obj.target)
print(hl_obj.tooltip) # you can see it when hovering mouse on hyperlink in Excel
print(hl_obj) # to see other stuff if you need
I found a way to do it.
Assuming one .xlsx file named 'workbookEx.xlsx' with two sheets named 'sheet1' and 'sheet2' and needing a link from one cell(A1) of the 'sheet1' to another cell(E5) of the 'sheet2':
from openpyxl import load_workbook
wb = load_workbook(workbookEx.xlsx)
ws = wb.get_sheet_by_name("sheet1")
link = "workbookEx.xlsx#sheet2!E5"
ws.cell(row=1, column=1).hyperlink = (link)
The secret was the "#", Excel do not shows you but it uses the '#' for same file links, I just had to copy a same file link created in Excel to a Word document to see the '#'.
It is also possible to omit the filename, i.e. to link against a sheet of the active document just use: _cell.hyperlink = '#sheetName!A1'.
To name the link you just created, just set the cell value to the desired string: _cell.value = 'Linkname'.
As an addendum to Marcus.Luck's answer, if wanting to use Excel's built-in hyperlink function directly, you may need to format as:
'=HYPERLINK("{}", "{}")'.format(link, "Link Name")
Without this formatting, the file didn't open for me without needing repair, which removed the cell values when clicking the links.
e.g. ws.cell(row=1, column=1).value = '=HYPERLINK("{}", "{}")'.format(link, "Link Name")
I am using python to generate a daily report, which is published as an Excel 2016 file. One column of the report will have links to .pdf files on a network share.
Attempts to hardcode the network path/file name (e.g., "T:\District\Parks\GIS\PDFs") generate a "Cannot open the specified file" error message. I have checked and the path and filenames are correct.
According to Bill Manville's answer on https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other-mso_archive/excel-hyperlink-cannot-open-specified-file/77c6ef20-f472-453e-a88d-71e9a7a23138 , "Hyperlinks are sometimes stored as relative to the folder that contains the workbook. " This seems to be the case here. When I followed Mr. Manville's suggestion of "setting File > Properties > Summary > Hyperlink base to\\NoServer\Nofolder or some other non-existent location.", without changing anything in the hyperlinks, the hyperlinks work.
How do I access the Hyperlink Base property programmatically? Can it be done programmatically? I'll do it manually every day if necessary, but it would obviously be more consistent/faster to do it through code.
I have gone through the openpyxl documentation and have not seen anything helpful. Google/DDG searches are equally fruitless.
Thanks.
You have to change style attribute
cell.style = "Hyperlink"
import openpyxl
from openpyxl.styles import Font, Color, colors
#...
# alternative 1: set hyperlink property to cell
def link_1(cell, link, display=None):
cell.hyperlink = link
cell.font = Font(u='single', color=colors.BLUE)
if display is not None:
cell.value = display
# alternative 2: use Excel formula HYPERLINK
def link_2(cell, link, display='link'):
cell.value = '=HYPERLINK("%s", "%s")' % (link, display)
cell.font = Font(u='single', color=colors.BLUE)
# examples
link_1(ws['B2'], '#sheet3!A1', 'link_text') # internal link
link_2(ws['B3'], '#sheet3!A1', 'link_text') # internal link
link_1(ws['B4'], 'https://www.google.com/', 'Google') # web link