Excel operating slow with office 13 and not supporting xlrd (not able to read hyperlink's content)

Question

I am facing primarily two problems:

1. I had an excel in xls format and I moved it to xlsm format. After this, xlrd is not able to read the hyperlinks. I have no other option but to use xlrd. Any solution?

2. And the xls is working too slow with Office 13. I converted it to xlsm format too. But still its too slow. Is there anything that can be done with xlrd? Actually my python script is used by lot of people to work with excel. So, I cant force each end user to configure xlsm (this will be the last option in case no other option is available).

Answer 1

1. For this, the issue is happening because the formatting text flag is not enabled in the case of xlsm. The only solution is to create a new column where we put the sheet name and read it, instead of getting it from the hyperlink.

   Sub Button1_Click()
       Dim a As Integer
       Dim b As String
       Dim c As Integer
   
       On Error Resume Next
       For Each hl In Sheets("TPD Sheet").Hyperlinks
           'Get the sheet name
           If Not IsNull(hl) Then
               c = Application.Evaluate(hl.Range.row)
               MsgBox "Aakash" & Application.Evaluate(hl.Range.row)
               Set r = Application.Evaluate(hl.SubAddress)
               'MsgBox "Row number being operated upon -> " & Application.Evaluate(hl.Range.row)
               'Get the cell where the sheet name to be put
               a = Application.Evaluate(hl.Range.row)
               b = Application.Evaluate(hl.Range.Column)
   
   
               Set TxtRng = Sheets("MySheet").Cells(a, "D")
               TxtRng.value = r.Parent.name
   
           End If
       Next hl
   
   End Sub
   

2. Select disable hardware graphics acceleration and it will be little faster. Still trying to make it faster.