excel - VBA 代码 - 连接到网页并检索值

Question

我有以下

A 列 == 联邦 express AWB #s

B栏==交货日期(空)

我想编写一个函数，它读取 A 列上的跟踪号并从网站上提取交货日期 - 所有 AWB # 都已交付 - 100% 肯定

我的代码将网站中的所有信息写入工作表 - 不确定如何仅提取交付日期。

Sub Macro1()
    With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;https://www.bing.com/packagetrackingv2? 
    packNum=727517426234&carrier=Fedex&FORM=PCKTR1" _
    , Destination:=Range("$A$1"))
    .Name = _
    "https://www.bing.com/packagetrackingv2? 
     packNum=727517426234&carrier=Fedex&FORM=PCKTR1"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
   .RefreshStyle = xlInsertDeleteCells
   .SavePassword = False
   .SaveData = True
   .AdjustColumnWidth = True
   .RefreshPeriod = 0
   .WebSelectionType = xlEntirePage
   .WebFormatting = xlWebFormattingNone
   .WebPreFormattedTextToColumns = True
   .WebConsecutiveDelimitersAsOne = True
   .WebSingleBlockTextImport = False
   .WebDisableDateRecognition = False
   .WebDisableRedirections = False
   .Refresh BackgroundQuery:=False
    End With

End Sub

Answer 1

一个函数，通过空运单号并返回日期就足够了:

Function GetDateFromAwb(awbNumber As String) As String

    Dim objIE As New InternetExplorer   'Microsoft Internet Controls library added
    objIE.Visible = False               'Or put True, if you want to see the IE

    objIE.navigate "https://www.fedex.com/apps/fedextrack/?tracknumbers=" & awbNumber

    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
    Application.Wait (Now + TimeValue("0:00:05"))

    GetDateFromAwb = objIE.Document.getElementsByClassName("redesignSnapshotTVC snapshotController_date dest").Item.InnerText
    objIE.Quit

End Function

该函数的思想是将空运单字符串编号附加到 URL 并打开相应的站点。然后，使用类“redesignSnapshotTVC snapshotController_date dest”，获取相应的日期。

这是调用函数的一种可能方式，在 MsgBox 中显示日期:

Sub Main()

    Dim awbNumber As String
    awbNumber = 727517426234#
    Dim awbDate As String

    awbDate = GetDateFromAwb(awbNumber)
    MsgBox awbDate

End Sub

确保从 VBE 菜单>Extras>References 添加库“Microsoft Internet Controls”: