jeudi 16 mai 2019

VBA code - connect to webpage and retrieve value

i am a newbie on VBA ... so apologies in advance if this is a stupid question

I have the following Column A == FEdEX AWB #s Coulum B == Delivery date (Empty)

I would like to write a function where it reads the tracking number on Column A and extracts the delivery date from the website - all AWB # are delivered - 100% sure

the code i have writes all the info found in the website into the sheet - not sure how to extract only the delivered date

Code shown below

Thank you!

    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




Aucun commentaire:

Enregistrer un commentaire