-3

Every month we downloaded over-time form from internet by clicking a link.

So i want to make a vba to get the URL from one of the link name in site. The attach image is the example. I want to get the URL encircled in red and paste into excel (filename otform.xlsm cell A1).

Example

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
200yrs
  • 3
  • 1
  • 5
  • Yes I made a code followed from youtube but it seems not working at all...please see the following. – 200yrs May 14 '16 at 02:28

1 Answers1

0

Below code will give you the first search result by google.
Code will search for the value in Cell A1 and will enter the search result in Cell B1.

Sub GetURL()
    Dim url As String
    Dim XMLHTTP As Object, html As Object, objResultDiv As Object, objH3 As Object, link As Object

    url = "https://www.google.co.in/search?q=" & Range("A1").Value & "&rnd=" & WorksheetFunction.RandBetween(1, 10000)

    Set XMLHTTP = CreateObject("MSXML2.serverXMLHTTP")
    XMLHTTP.Open "GET", url, False
    XMLHTTP.setRequestHeader "Content-Type", "text/xml"
    XMLHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:25.0) Gecko/20100101 Firefox/25.0"
    XMLHTTP.send

    Set html = CreateObject("htmlfile")
    html.body.innerHTML = XMLHTTP.ResponseText
    Set objResultDiv = html.getelementbyid("rso")
    Set objH3 = objResultDiv.getelementsbytagname("H3")(0)
    Set link = objH3.getelementsbytagname("a")(0)

    Range("B1").Value = link.href
    DoEvents

    MsgBox "Done"
End Sub

enter image description here

I guess this is what you want.

Got this from here.

EDIT#1: Using Internet Explorer ________________________________________________________________________________

Sub GetURL()
    Dim ie As SHDocVw.InternetExplorer  'Requires reference to "Microsoft Internet Controls"
    Dim searchString As String
    Dim lngStartAt As Long, lngResults As Long
    Dim doc As MSHTML.HTMLDocument      'Requires reference to "Microsoft HTML Object Library"
    Dim objResultDiv As Object, objH3 As Object, link As Object

    Set ie = New SHDocVw.InternetExplorer
    lngStartAt = 1
    lngResults = 100

    searchString = Range("A1").Value

    ie.navigate "https://www.google.co.in/search?q=" & searchString
    Do Until ie.readyState = READYSTATE_COMPLETE: DoEvents: Loop

    Set doc = ie.document
    Set objResultDiv = doc.getElementById("rso")
    Set objH3 = objResultDiv.getElementsByTagName("H3")(0)
    Set link = objH3.getElementsByTagName("a")(0)

    Range("B1") = link.href

    ie.Quit
End Sub

You'll have to add following two References from Tools menu:

  1. Microsoft Internet Controls
  2. Microsoft HTML Object Library
Community
  • 1
  • 1
Mrig
  • 11,612
  • 2
  • 13
  • 27
  • Hello Mrig...i got a timeout error...when i click debug it will point to --> XMLHTTP.Send...By the way, we are only using internet explorer....thanks – 200yrs May 17 '16 at 02:28
  • @200yrs - This is probably because of your internet speed. – Mrig May 17 '16 at 05:27
  • @Mrig...our internet is fast...oh i found a code working see below...but i dont want to get all URL. I want only the URL which having the link name of "Getting started with Excel VBA - I Programmer" `code` – 200yrs May 17 '16 at 06:39
  • Sorry i wast caught by 5min edit time....so our internet is really fast...is the code works using internet explorer? – 200yrs May 17 '16 at 06:48
  • @200yrs - I've added code that will fetch google results using internet explorer. – Mrig May 17 '16 at 07:58