Update hyperlink in Excel using Invoke Code

Help
,
1

Hi Friends,
I am trying to update hyperlink in my excel using invoke code in uiPath.

Please find below the code for the same.

Dim xlApp As Microsoft.Office.Interop.Excel._Application = Nothing
Dim xlWorkBooks As Microsoft.Office.Interop.Excel.Workbooks=Nothing
Dim xlWorkBook1 As Microsoft.Office.Interop.Excel.Workbook=Nothing
Dim xlWorkSheet1 As Microsoft.Office.Interop.Excel.Worksheet=Nothing
Dim xlWorkSheets As Microsoft.Office.Interop.Excel.Sheets=Nothing
Dim xlRange As Microsoft.Office.Interop.Excel.Range=Nothing
Dim xlRanges As Microsoft.Office.Interop.Excel.Ranges=Nothing

xlApp=New Microsoft.Office.Interop.Excel.ApplicationClass
xlApp.DisplayAlerts=False
xlWorkBooks=xlApp.Workbooks

On Error Resume Next
xlWorkBook1=xlWorkBooks.Open(WorkbookPath1,UpdateLinks:=False)
If Err.Number>0 Then ErrorMsg = “Exception while opening source excel” : GoTo ErrExt
xlApp.Visible = True
On Error GoTo 0

On Error Resume Next
xlWorkSheet1=CType(xlWorkBook1.Sheets(SheetName1),Microsoft.Office.Interop.Excel.Worksheet)
If Err.Number>0 Then ErrorMsg = “Sheet " + SheetName1 + " does not exist.” & Err.Description : GoTo ErrExt
On Error GoTo 0

xlWorkBook1.ChangeLink(Name:= “C:\Users\Path1.xlsx”, NewName:= “C:\Users\Path2.xlsx” , Type:=Microsoft.Office.Interop.Excel.XlLinkType)

ErrorMsg = “No Error”

ErrExt:
xlApp.DisplayAlerts=True
xlWorkBook1.Save
xlWorkBook1.Close()

xlApp.Quit()

I get the below error after writing the above code.

image

Please help in resolving the same !!

Thanks,
Devi

2

Devi,

XlLinkType is an Enum. You have to use one of the fields of XlLinkType:

  • xlLinkTypeExcelLinks
  • xlLinkTypeOLELinks

Assuming you want the first type, then your code should look like:

xlWorkBook1.ChangeLink(Name:= "C:\Users\Path1.xlsx", NewName:= "C:\Users\Path2.xlsx" , Type:=Microsoft.Office.Interop.Excel.XlLinkType.xlLinkTypeExcelLinks)

Since it is an Enum, you could also put a number (1 or 2):

xlWorkBook1.ChangeLink(Name:= "C:\Users\Path1.xlsx", NewName:= "C:\Users\Path2.xlsx" , Type:=1)

Although personally I prefer the first option since it is more explicit.

You can find more info here:

3 Likes
3

Thanks @oddrationale. it worked :slight_smile:

4

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.