CODEWITHSUNDEEP
excelvba
prm
prm

Reputation: 25

Extract value from URL and set it as variable

I want to double-click a cell in Excel to open a URL.

I've been using VBA for this aspect, but I am facing an issue.
I want to extract a value from URL and use it as variable in VBA.

Here is part of the script:

Dim ID As String
          
ID = ActiveSheet.Range("S" & Target.Cells.Row & "").Value
    
rptUrl = "http://...=" + ID
         
If (ID <> "") Then
          
    ThisWorkbook.FollowHyperlink (rptUrl)

In such case, if the ID is at the end of the URL, it works.

What happens if the ID that I want to extract is somewhere in the middle of the URL, and not at the end?

For example:

rptUrl = "http://..**ID**..="

I tried the following:

rptUrl = "http://.. + **ID** + ..="

Upvotes: 0

Views: 435

Answers (1)

basodre
basodre

Reputation: 5770

If you want to use a regular expression, here's an option that packages the regular expression into a function that you can call. If the URL contains "ID", it will return the corresponding value; otherwise, it will just return a blank string

Function GetId(sInput) As String
    Dim oReg As Object
    Dim m As Variant
    Dim sOutput As String
    
    sOutput = ""
    
    Set oReg = CreateObject("VBScript.Regexp")
    
    With oReg
        .Global = False
        .ignorecase = True
        .MultiLine = False
        .Pattern = "id=(\w+)[&|$]"
    End With
    
    
    If oReg.Test(sInput) Then
        sOutput = oReg.Execute(sInput)(0).submatches(0)
    End If
    
    GetId = sOutput
End Function

Sub Test()
    Debug.Print GetId("mysrv.com/form.jsp?id=12345&cn=0")
End Sub

Upvotes: 1

Related Questions