Link to home
Start Free TrialLog in
Avatar of finnstone
finnstone

asked on

need to dynamically open links, changing excel vba code

i need to change the id number in the below url each time for 1000 rows in excel

in column B
https://www.linkedin.com/profile/view?id=45106579&snapshotID=&authType=name&authToken=P61v&ref=NUS&trk=NUS-body-member-name

45106579 =  what i need to make a variable

I also need an excel formula that takes the url above and puts the number 45106579 in column A (the variable)

here is the code i need manipulated to run row A and change the value each time
(     If Not (blnShell_Execute("http://www.google.com/search?hl=en&q=%22" & objCell & "%22")) Then)
above is line needed to change for linkedin and its varible

below is code

Option Explicit

' ----------------------------------------------------------------------------

Private Declare Function ShellExecute _
Lib "shell32.dll" _
Alias "ShellExecuteA" _
(ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Private Function blnShell_Execute(ByVal strURL As String, _
Optional ByVal strParameters As String = vbNullString, _
Optional lngShow_Cmd As Long = vbNormalFocus) As Boolean

Dim blnReturn As Boolean
Dim lngHandle As Long

On Error GoTo Err_blnShell_Execute

blnReturn = False

lngHandle = ShellExecute(0&, vbNullString, strURL, strParameters, vbNullString, lngShow_Cmd)

blnReturn = (lngHandle > 31)

Exit_blnShell_Execute:

On Error Resume Next

blnShell_Execute = blnReturn

Exit Function

Err_blnShell_Execute:

blnReturn = False

Resume Exit_blnShell_Execute

End Function
Public Sub Open_Google_Searches()

Dim objCell As Range

For Each objCell In [a136:a165]

DoEvents

If Not IsEmpty(objCell) Then
If Not (blnShell_Execute(objCell)) Then
MsgBox "Could not launch:" & vbCrLf & vbLf & objCell, _
vbExclamation Or vbOKOnly, _
ActiveWorkbook.Name
End If
End If

Next objCell

End Sub

' ...End of Code




Public Sub Open_Google_Searches()

  Dim objCell                                           As Range
 
  For Each objCell In [A2:A5]
 
      DoEvents
     
      If Not IsEmpty(objCell) Then
         If Not (blnShell_Execute("http://www.google.com/search?hl=en&q=%22" & objCell & "%22")) Then
            MsgBox "Could not launch:" & vbCrLf & vbLf & objCell, _
                    vbExclamation Or vbOKOnly, _
                    ActiveWorkbook.Name
         End If
      End If
     
  Next objCell
               
End Sub

' ...End of Code
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of finnstone
finnstone

ASKER

thanks that got me closer and then this worked

=MID(B1,SEARCH("=",B1)+4,SEARCH("&",B1)-SEARCH("=",B1)-4)

BUT, still need to change the actual code to dynamically load column A
pls send dummy