Solved

need to dynamically open links, changing excel vba code

Posted on 2014-09-30
3
121 Views
Last Modified: 2014-10-01
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
0
Comment
Question by:finnstone
  • 2
3 Comments
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40354038
Hi,

in Col A

pls try

=MID(B1,SEARCH("=",B1)+1,SEARCH("&s",B1)-SEARCH("=",B1)-1)

Regards
0
 

Author Comment

by:finnstone
ID: 40354592
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
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40354600
pls send dummy
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now