Hyperlink!

Help Studio
1

Hi Everyone,

I would like to check if hyperlinks exists in an excel file once it’s opened.If hyperlinks exists then remove else save the workbook.I’m currently struggling to check if hyperlinks exists in the file once it’s opened.thanks

2

@tebogo_Moloto

Hello Tebogo,
try it with a code activity. Here an example in C# how to delete all hyperlinks in an Excel file.

//-Begin-------------------------------------------------------------

//css_reference Microsoft.Office.Interop.Excel.dll

using System;

public class Test {

  public static void Main() {

    Microsoft.Office.Interop.Excel.Application excel = 
      new Microsoft.Office.Interop.Excel.Application();
    excel.Visible = true;
    Microsoft.Office.Interop.Excel.Workbook workbook = 
      excel.Workbooks.Open("E:\\Dummy\\Excel\\WithHyperlinks.xlsx");

    for(int i = 1; workbook.Worksheets.Count >= i; i++) {
      Microsoft.Office.Interop.Excel.Worksheet worksheet = 
        (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(i);
      worksheet.Hyperlinks.Delete();
    }

    workbook.SaveAs("E:\\Dummy\\Excel\\WithoutHyperlinks.xlsx");
    excel.Quit();

  }

}

//-End---------------------------------------------------------------

Best regards
Stefan

2 Likes
3

Good Day @StefanSchnell,

Firstly happy new year and keeping safe.
Inside the code activity I keep getting errors on:

  • The class Test, reason been (expected semi-colon )
  • on the main function void, reason been ( expected semi-colon)
  • workbook.Worksheets.Count >= i, reason been (Only assignment, call,increment,decrement, and new object expressions can be used as statement).
    Any reason why these errors are appearing?

Regards,
Tebogo

4

Good Day @StefanSchnell,

Please see the below image…for the above explanation.

image
image656×584 22.2 KB

5

@tebogo_Moloto

Hello Tebogo,
my approach is a mock to show how you can realize your requirement. Use only the code from inside the Main routine and add the Excel Interop namespace to your project.
Best regards
Stefan

6

Hi @StefanSchnell,

Thanks it works!

Regards,
Tebogo

1 Like
7

Hi @StefanSchnell,

How will I rename a excel sheet in C# within the invoke code activity?

Regards,
Tebogo

8

Below is the code I have in the invoke code activity:
/* Microsoft.Office.Interop.Excel.Worksheet worksheet =
(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(1);
worksheet.Name=“BOT”;*/

9

Hi @StefanSchnell,

Any ideas for the above question?

Regards,
Tebogo

10

@tebogo_Moloto

Hello Tebogo,
as far as I can see looks your code good and should work.
Best regards
Stefan