Hello all, I have an excel sheet where a column is full of hyperlinks. Whenever I grab this column, it only gets the displayed text , no matter what settings I have the read range activity as. But I need the full hyperlink.
Example:
Currently I’m only able to return Test_MasterProject_210_20240222131619699.edi
but I need to return the full hyperlink into the datatable.
Any ideas?
You can try this:
Greetings!!
I have had something of the sorts in one of my automation.
Can you use this piece of code in an Invoke Code Activity?
xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(FileName.Trim(),ReadOnly: false);
Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = (Microsoft.Office.Interop.Excel._Worksheet)xlWorkbook.Sheets[1];//Check for sheet Number, or look for a logic to get the correct sheet if you have many in the file.
//Iterate the rows in the range
Microsoft.Office.Interop.Excel.Range range =(Microsoft.Office.Interop.Excel.Range) xlWorksheet.UsedRange.EntireColumn["E"];//Mention correct column label
Microsoft.Office.Interop.Excel.Hyperlinks hyperLinks = range.Hyperlinks;
foreach (Microsoft.Office.Interop.Excel.Hyperlink lin in hyperLinks)
{
DataRow dr =dtResult.NewRow();
dr[2]=lin.Name;
dr[3]= lin.Address;// This will get the URL.
dtResult.Rows.Add(dr);
}
xlWorkbook.Close(false,FileName.Trim(),false);
Argument Values:
You might have to change column Label and Sheet index.
But this works.
So you have to use one read range which would read the normal data of the whole Excel Sheet
and one invoke code activity which will extract the hyperlinks along with names, later you can use a join if you have to use the other data as well.
Else just use Invoke Code.
Happy Automation! 
I found the issue, even though the cells were operating as hyperlinks, technically they were formulas (=hyperlink(X,y,z)).
I used the get formulas activity and parsed out what I needed.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
