CODEWITHSUNDEEP
excelvba
Korliki
Korliki

Reputation: 27

Excel VBA extract url form src or href

I'm trying to create a script that will swap all the cells in the active tab with the url from the text in that cell that are right after the src or href attribute, so that it swaps it in the same cell it was in originally.

In addition, the url are in random places, so the code would have to look for the last row and column and in the whole selected section replace. but to only replace the cells in which the url occurs.

The url ending also changes and does not end with .com, I was looking for code that is able to pull the url between the two "url" right after the src or href attribute

example:

From this
From this > to this to this

I searched the whole internet, I found only codes for downloading the url directly from the sites. I would be very grateful for help.

Upvotes: 0

Views: 129

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

You can use something like below to extract the URL:

Public Function ExtractURL(ByVal InputVal As String) As String
    
    Dim StartPos As Long
    StartPos = InStr(1, InputVal, "http")
    
    If StartPos > 0 Then
        Dim EndPos As Long
        EndPos = InStr(StartPos + 1, InputVal, """")
    
        If EndPos > 0 Then
            ExtractURL = Mid$(InputVal, StartPos, EndPos - StartPos)
        End If
    End If
    
End Function

Test it with

Public Sub example()
    Debug.Print ExtractURL("<a herf=""https://www.example.com"">link</a>")
End Sub

Now just loop over all cells in the used range and apply the function

Dim RetVal As String
Dim Cell As Range
For Each Cell In Worksheets("Sheet1").UsedRange.Cells
    RetVal = ExtractURL(Cell.Value)
    If RetVal <> vbNullString Then
        Cell.Value = RetVal
    End If
Next Cell

If that is too slow turn off calculation/screenupdating or use arrays. You will find many tutorials for that.

Upvotes: 1

Related Questions