I am using Read Range from excel, Filter it & Paste in Different excel workbook with Hyperlinks

Help Robot
,
1

I am reading data from once excel by using filter data table & Read range.

Then I use Write range workbook but I get this error:

“Write Range Workbook: A malformed URI was found in the document. Please provide a OpenSettings.RelationshipErrorRewriter to handle these errors while opening a package.”

Can someone please help?

2

@Raks_K,

There is no straight or readymade solution for this but you can utilize the ClearHyperlinks method available in the Excel Application Scope. Here’s how you can do it:

  1. Use Excel Application Scope: Drag and drop an “Excel Application Scope” activity onto your workflow. Specify the Excel file you want to work with.
  2. Invoke VBA Macro: Within the Excel Application Scope, use the “Invoke VBA” activity to execute a VBA macro that clears the hyperlinks.
  3. Create VBA Macro: Create a VBA macro within your Excel file that clears hyperlinks from the desired cells.

Here’s a step-by-step guide:

  1. Excel Application Scope:
  • Drag and drop an “Excel Application Scope” activity onto your workflow.
  • Configure it to specify the Excel file you want to work with.
  1. Invoke VBA Macro:
  • Within the Excel Application Scope, add an “Invoke VBA” activity.
  • In the “Code” field of the activity properties, enter the VBA macro code to clear hyperlinks.
  1. Create VBA Macro:
  • Open your Excel file.
  • Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
  • Insert a new module by right-clicking on “Modules” in the Project Explorer and selecting “Insert” > “Module”.
  • Copy and paste the following VBA macro code into the module:
Sub ClearHyperlinks()
    Dim rng As Range
    For Each rng In ActiveSheet.UsedRange
        If rng.Hyperlinks.Count > 0 Then
            rng.Hyperlinks.Delete
        End If
    Next rng
End Sub
  1. Run the Workflow: Execute your workflow. The VBA macro will be invoked within the Excel Application Scope, and it will clear the hyperlinks from the specified Excel file.

This approach ensures that hyperlinks are removed from the Excel file without affecting other data or formatting. Make sure to adjust the Excel file path and any other parameters as needed for your specific scenario.

  1. Now Use Red Range → Filter it and paste

Thanks,
Ashok :slight_smile:

3

Thanks Ashok.

But I need the Hyperlinks there as they are mainly Index & Summary links.

Is there any work around that can help to just copy paste from one sheet to another?

4

Hi @Raks_K

May be here you can change your excel settings

Just do the Steps

In Your Excel

Click File > Options > Proofing.
Click AutoCorrect Options, and then click the AutoFormat As You Type tab.
Clear the Internet and network paths with hyperlinks check box.

5

@Raks_K,

You will have to use Excel activities for everything like filtering and stuff. No DataTable involvement.

This would be little bit slow but if you can afford little bit slowness then this is the best approach for your problem statement.

Thanks,
Ashok :slight_smile:

6

Thanks Sanjay it does work but it seems Index Formula have gone missing from there.(works without any lag)

Also when I close the file I get the below error. Any idea on how to remove this?

image
image937×145 3.52 KB

7

@Raks_K

File > Options > Customize Ribbon and search for “Macros” or “View Macro”
Disable or Remove Macros

8

Hi @Raks_K

Please check below

image
image900×1384 59.9 KB

Cheers!!

9

Hi @Raks_K

Check this thread.

Regards