Update the links in Excel File

Help Something Else
, , ,
1

Hi All, I need one help

I have an excel file in which i have to update the links.

Manually it is done by clicking on data-edit link-update Values and save.
Please help me on this using UiPath.

2

@Buvaneshwaran_R

Do you know the cell position the one you want to edit the link. If yes then use Write Cell activity inside Excel Application Scope activity and pass cell number and value to update it.

3

Hi @Buvaneshwaran_R ,

We could use OpenXml for Updating the External Links in the Excel.

Do Check the Below Workflow :
Excel_UpdateLinks.zip (21.7 KB)

The Code used in the Invoke Code Activity :

try
{
	using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheetDoc = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(in_FilePath,true))
	{
		
		DocumentFormat.OpenXml.Spreadsheet.Sheet ws = (DocumentFormat.OpenXml.Spreadsheet.Sheet)spreadsheetDoc.WorkbookPart.Workbook.Sheets.FirstOrDefault();
		
		DocumentFormat.OpenXml.Packaging.WorkbookPart wp = spreadsheetDoc.WorkbookPart;
		
		IEnumerable<DocumentFormat.OpenXml.Packaging.ExternalWorkbookPart> ewp = wp.ExternalWorkbookParts;
		
		ewp.ElementAt(0).DeleteExternalRelationship("rId1");
		
		ewp.ElementAt(0).AddExternalRelationship(@"http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath",new Uri(in_LinkFilePath),"rId1");
		
	}
	
	Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
	
	var wb = app.Workbooks.Open(in_FilePath,3,false,5);
	
	wb.RefreshAll();
	wb.Save();
	wb.Close();
	
}
catch(Exception e)
{
	out_Exception = e;
	Console.WriteLine(e.Message.ToString()+" "+e.Source.ToString());
}

In this case, We are deleting the External Link already present and Adding a New External Link.

rId1 is used to Identify the first Link present in the Excel sheet.

We then Refresh using Interop.Excel to update the Excel.

There are three Excel Files in the Workflow, where Excel_Main has the linkage to Excel_1. We could try specifying the Excel_2 as the file to be linked to Excel_Main and Check it’s result.

In the Workflow, keep in mind we would require to pass the full paths of the files.

There are cases where the file gets corrupted, but would need to check first and then solve the corruption if present.

Or

As an Alternate we could create the formula’s beforehand and write to each cell required by looping.

4

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