Insert hyperlink to a local folder in Excel with Python
Question: The piece of code reads an Excel file. This excel file holds information such
as customer job numbers, customer names, sites, works description ect..

What this code will do when completed (I hope) is read the last line of the
worksheet (this is taken from a counter on the worksheet at cell 'P1'), create
folders based on cell content, and create a hyperlink on the worksheet to open
the lowest local folder that was created.

I have extracted the info I need from the worksheet to understand what folders
need to be created, but I am not able to write a hyperlink to the cell on the
row in column B.

    
    
    #Insert Hyperlink to 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 # This is just to check the value
        # The statement below should insert hyperlink in eps.xlsm > worksheet jobnoeps at column B and last completed row.
        ws.cell(final_position).hyperlink = destination
    

The complete code is below but here is the section that is meant to create the
hyperlink. I have also tried the 'xlswriter' package with no joy. Searched the
internet and the above snippet is the result of what I found.

Anyone know what I am doing wrong?

    
    
     __author__ = 'Paul'
    
    import os
    import openpyxl
    from openpyxl import load_workbook
    import xlsxwriter
    
    site_info_root = 'C:\\Users\\paul.EPSCONSTRUCTION\\PycharmProjects\\Excel_Jobs\\Site Information\\'
    
    # This function returns the last row on eps.xlsm to be populated
    def get_last_row(cell_ref = 'P1'): #P1 contains the count of the 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 from that worksheet
        return last_row
    
    
    # This function will read the job number in format EPS-XXXX-YR
    def read_last_row_jobno(last_row_position):
        last_row_data = []
        for 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 will return the 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 will return the 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 will return the 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 to 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 # This is just to check the value
        # The statement below should insert hyperlink in eps.xlsm > worksheet jobnoeps at column B and last completed row.
        ws.cell(final_position).hyperlink = destination
    
    
    
    folder_location = site_info_root + job_customer + "\\" + job_dir
    
    
    print folder_location # This is just to check the value
    folder_hyperlink(last_row, folder_location)
    

* * *

Now my hyperlink function looks like this after trying xlsxwriter as advised.

    
    
    ##Insert Hyperlink to 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 # This is just to check the 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 the exsisting eps.xlsx, creates a jobnoeps table and
then inserts the hyperlink. I have played with the following lines but don't
know how to get it to open the existing xlsx and existing jobnoeps tab and
then enter the hyperlink.

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


Answer: The XlsxWriter
[`write_url()`](http://xlsxwriter.readthedocs.org/worksheet.html#worksheet-
write-url) method allows you to link to folders or other workbooks and
worksheets as well as internal links and links to web urls. For example:

    
    
    import xlsxwriter
    
    workbook = xlsxwriter.Workbook('links.xlsx')
    worksheet = workbook.add_worksheet()
    
    worksheet.set_column('A:A', 50)
    
    # Link to a Folder.
    worksheet.write_url('A1', r'external:C:\Temp')
    
    # Link to a workbook.
    worksheet.write_url('A3', r'external:C:\Temp\Book.xlsx')
    
    # Link to a cell in a worksheet.
    worksheet.write_url('A5', r'external:C:\Temp\Book.xlsx#Sheet1!C5')
    
    workbook.close()
    

See the docs linked to above for more details.

