Solved

need to dynamically open links, changing excel vba code

Posted on 2014-09-30
3
117 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 48

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 48

Expert Comment

by:Rgonzo1971
ID: 40354600
pls send dummy
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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 simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

706 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

15 Experts available now in Live!

Get 1:1 Help Now