Insert hyperlink to a local folder in Excel with Python -

the piece of code reads excel file. excel file holds information such customer job numbers, customer names, sites, works description ect..

what code when completed (i hope) read last line of worksheet (this taken counter on worksheet @ cell 'p1'), create folders based on cell content, , create hyperlink on worksheet open lowest local folder created.

i have extracted info need worksheet understand folders need created, not able write hyperlink cell on row in column b.

#insert hyperlink folder def folder_hyperlink(last_row_position, destination):     cols = 'b'     rows = str(last_row_position)     position = cols + rows     final_position = "".join(position)     print final_position # check value     # statement below should insert hyperlink in eps.xlsm > worksheet jobnoeps @ column b , last completed row.     ws.cell(final_position).hyperlink = destination 

the complete code below here section meant create hyperlink. have tried 'xlswriter' package no joy. searched internet , above snippet result of found.

anyone know doing wrong?

 __author__ = 'paul'  import os import openpyxl openpyxl import load_workbook import xlsxwriter  site_info_root = 'c:\\users\\paul.epsconstruction\\pycharmprojects\\excel_jobs\\site information\\'  # function returns last row on eps.xlsm populated def get_last_row(cell_ref = 'p1'): #p1 contains count of used rows     global wb     global ws     wb = load_workbook("eps.xlsm", data_only = true) #workbook     ws = wb["jobnoeps"] #worksheet     last_row = ws.cell(cell_ref).value #value of p1 worksheet     return last_row   # function read job number in format eps-xxxx-yr def read_last_row_jobno(last_row_position):     last_row_data = []     cols in range(1, 5):         last_row_data += str(ws.cell(column = cols, row = last_row_position).value)     last_row_data_all = "".join(last_row_data)     return last_row_data_all   #this function return customer def read_last_row_cust(last_row_position):     cols = 5     customer_name = str(ws.cell(column = cols, row = last_row_position).value)     return customer_name   #this function return site def read_last_row_site(last_row_position):     cols = 6     site_name = str(ws.cell(column = cols, row = last_row_position).value)     return site_name   #this function return job discription def read_last_row_disc(last_row_position):     cols = 7     site_disc = str(ws.cell(column = cols, row = last_row_position).value)     return site_disc   last_row = get_last_row() job_no_details = read_last_row_jobno(last_row) job_customer = read_last_row_cust(last_row) job_site = read_last_row_site(last_row) job_disc = read_last_row_disc(last_row)  cust_folder = job_customer job_dir = job_no_details + "\\" + job_site + " - " + job_disc   #insert hyperlink folder def folder_hyperlink(last_row_position, destination):     cols = 'b'     rows = str(last_row_position)     position = cols + rows     final_position = "".join(position)     print final_position # check value     # statement below should insert hyperlink in eps.xlsm > worksheet jobnoeps @ column b , last completed row.     ws.cell(final_position).hyperlink = destination    folder_location = site_info_root + job_customer + "\\" + job_dir   print folder_location # check value folder_hyperlink(last_row, folder_location) 

---

now hyperlink function looks after trying xlsxwriter advised.

##insert hyperlink folder def folder_hyperlink(last_row_position, destination):     import xlsxwriter     cols = 'b'     rows = str(last_row_position)     position = cols + rows     final_position = "".join(position)     print final_position # check value     workbook = xlsxwriter.workbook('eps.xlsx')     worksheet = workbook.add_worksheet('jobnoeps')     print worksheet     worksheet.write_url(final_position, 'folder_location')     workbook.close() 

the function overwrites exsisting eps.xlsx, creates jobnoeps table , inserts hyperlink. have played following lines don't know how open existing xlsx , existing jobnoeps tab , enter hyperlink.

workbook = xlsxwriter.workbook('eps.xlsx') worksheet = workbook.add_worksheet('jobnoeps') worksheet.write_url(final_position, 'folder_location') 

the xlsxwriter write_url() method allows link folders or other workbooks , worksheets internal links , links web urls. example:

import xlsxwriter  workbook = xlsxwriter.workbook('links.xlsx') worksheet = workbook.add_worksheet()  worksheet.set_column('a:a', 50)  # link folder. worksheet.write_url('a1', r'external:c:\temp')  # link workbook. worksheet.write_url('a3', r'external:c:\temp\book.xlsx')  # link cell in worksheet. worksheet.write_url('a5', r'external:c:\temp\book.xlsx#sheet1!c5')  workbook.close() 

see docs linked above more details.