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
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)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
pls send dummy
ASKER
=MID(B1,SEARCH("=",B1)+4,S
BUT, still need to change the actual code to dynamically load column A