retrieving links in excel sheet

Question

I need a VB code that can detect any word with http,.com,http,links in excel sheet?

Thanks in Advance

Code sample from comments

Sub ListLinks() 
  Dim aLinks As Variant aLinks = ActiveWorkbook.LinkSources(xlExcelLinks) 
  If Not IsEmpty(aLinks) Then 
    Sheets.Add 
    For i = 1 To UBound(aLinks) 
      Cells(i, 1).Value = aLinks(i) 
    Next i 
  End If 
End Sub

Sub ListLinks() Dim aLinks As Variant aLinks = ActiveWorkbook.LinkSources(xlExcelLinks) If Not IsEmpty(aLinks) Then Sheets.Add For i = 1 To UBound(aLinks) Cells(i, 1).Value = aLinks(i) Next i End If End Sub — Maisam Mohsen, Jan 26 '20 at 11:47
Please modify your question to reflect your attempt and where you are failing. — Yash, Jan 26 '20 at 11:48
The a bove code can retrive hayperlinks only ,I need tospecify exactly what to retrive such like any sentence ends with .com — Maisam Mohsen, Jan 26 '20 at 11:49
@Yash ,I need VB Code that can retrive any cell has the following : Amazon, Eco-friendly, Environmentally-friendly, Eco friendly, Ecologically Friendly Also any cell that has https, www, html — Maisam Mohsen, Jan 26 '20 at 11:56
You can use `Instr` function in your code. But your question is not clear. Check how to create an [MRE](https://stackoverflow.com/help/minimal-reproducible-example) — Ricardo Diaz, Jan 26 '20 at 18:04

CDP1802 · Answer 1 · 2020-01-27T08:13:13.693

0

Use a Regular Expression

  Sub search()
    Dim Regex
    Set Regex = CreateObject("vbscript.regexp")

    With Regex
      .Global = True
      .MultiLine = False
      .IgnoreCase = True
       .Pattern = "http|www|html|Amazon|(Eco|Environ).*friendly"
     End With

    Dim cell
    For Each cell In UsedRange.Cells
      If Regex.test(cell) Then
        cell.Interior.Color = vbYellow
        ' or save cell.value to sheet
        Count = Count + 1
      End If
    Next
    MsgBox Count & " found"
  End Sub

edited Jan 27 '20 at 08:13

answered Jan 26 '20 at 22:10

CDP1802

13,871
2
7
17

When I ran the a bove code I got run time error 424 (Object required) – Maisam Mohsen Jan 27 '20 at 06:01
@maisam What version of excel ? Can you single step the program with debug/F8 and identify the line. – CDP1802 Jan 27 '20 at 08:16
@maisam If you have put this code in a module rather than in a sheet then add the relevant sheet reference to Used.Range eg Sheets(1).UsedRange.Cells – CDP1802 Jan 27 '20 at 09:30
I have added the sheet name and the code was run but it always resulted with 9 found and nothing is highlighted!any idea? – Maisam Mohsen Jan 27 '20 at 14:33
Add msgBox cell.address just before count = count +1 and it should give your the location of the match – CDP1802 Jan 27 '20 at 14:56
Or try cell.Interior.ColorIndex = 6 rather than using the vb constant names. – CDP1802 Jan 27 '20 at 15:04
How to inherit the opened sheet name – Maisam Mohsen Feb 10 '20 at 13:08
@Maisam Mohsen Sorry, I don't understand what you mean by inherit. Do you mean the sheet name that is create by Sheets.Add ? – CDP1802 Feb 10 '20 at 13:58
I mean the abve code worked and got the defected cells highlighted but whenever I tried the code in different worksheet it give error because I need to change the sheet name manually.so I need a code that can read the worksheet name each time I open excel sheet – Maisam Mohsen Feb 11 '20 at 07:07
Maybe [activesheet.name](https://learn.microsoft.com/en-us/office/vba/api/excel.application.activesheet) – CDP1802 Feb 11 '20 at 08:28

retrieving links in excel sheet

1 Answers1