使用vba从互联网链接名称获取URL [英] Get URL from internet link name using vba

问题描述

每个月我们通过点击一个链接从互联网上下载超时表格。

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

所以我想使一个vba从站点中的一个链接名称获取URL。附件图片就是这个例子。我想获取包含红色的URL并粘贴到excel（filename otform.xlsm cell A1）中。

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).

推荐答案

以下代码将为您提供谷歌的第一个搜索结果。

代码将搜索单元格A1 中的值，并在单元格B1 。

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

我想这是你想要的。

得到这个这里。

编辑＃1 ：使用Internet Explorer
________________________________________________________________________

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对象库

1. Microsoft Internet Controls
2. Microsoft HTML Object Library

这篇关于使用vba从互联网链接名称获取URL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！