jalexmiller0
New Member
- Joined
- Nov 10, 2017
- Messages
- 8
Hi there, I am a complete beginner working on this macro. The goal is to have it input pickup numbers from an excel spreadsheet, put them on the carrier's website form, submit, then pull the results into excel. Which is essentially what I have managed to figure out so far. Now my roadblock is figuring out how to format it into excel so it's easy to read. (If there are more efficient ways to write what I already have please let me know.)
There is a lot of stuff commented out because I have been trying different techniques from all across the web. With varying amounts of success. I would like to get the code to put the results into the same worksheet as the pickup numbers. In the column next to the pickup number column so it can quickly be determined if the shipment has been picked up yet or not.
If that's not possible then outputting to a new sheet also works. Just so long as the information lines up with the corresponding pickup number.
Now on the carriers website after the code submits the pickup numbers. I need it to take "Shipment spotted on trailer JBHUXXXXXX" and if that isn't displayed. I need it to output "Arrived at destination service center" with the time and location as well. An example of this result is under 145-5244858-2.
Everything is stored in tables inside tables with the main table having "boxTableBorder" as the class name.
There is a lot of stuff commented out because I have been trying different techniques from all across the web. With varying amounts of success. I would like to get the code to put the results into the same worksheet as the pickup numbers. In the column next to the pickup number column so it can quickly be determined if the shipment has been picked up yet or not.
If that's not possible then outputting to a new sheet also works. Just so long as the information lines up with the corresponding pickup number.
Now on the carriers website after the code submits the pickup numbers. I need it to take "Shipment spotted on trailer JBHUXXXXXX" and if that isn't displayed. I need it to output "Arrived at destination service center" with the time and location as well. An example of this result is under 145-5244858-2.
Everything is stored in tables inside tables with the main table having "boxTableBorder" as the class name.
Code:
Sub TracePickupNumbers()
Dim IE As New SHDocVw.InternetExplorer
Dim HTMLDoc As MSHTML.HTMLDocument
IE.Visible = True
IE.navigate "http://www.centraltransportint.com/confirm/trace.aspx"
Do While IE.ReadyState <> READYSTATE_COMPLETE
Loop
Dim str As String
Dim arr() As Variant
Dim tablerow As Integer
Dim tablecol As Integer
arr = Range("A2:A7")
For tablerow = LBound(arr) To UBound(arr)
For tablecol = LBound(arr, 2) To UBound(arr, 2)
str = str & arr(tablerow, tablecol) & vbTab
Next tablecol
str = str & vbNewLine
Next tablerow
With IE.Document
.all("_ctl0_lstType").Value = "PuN"
' .all("_ctl0_lstType").fireevent.onchange() 'Gives syntax error
.all("_ctl0:traceNumbers").innerText = "1"
.all("_ctl0:traceSubmit").Click
End With
Application.Wait (Now + TimeValue("00:00:01"))
With IE.Document
.all("_ctl0:traceNumbers").innerText = str
.all("_ctl0:traceSubmit").Click
End With
Application.Wait (Now + TimeValue("00:00:02"))
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim HTMLElement As MSHTML.IHTMLElement
Dim HTMLElements As MSHTML.IHTMLElementCollection
Dim HTMLBody As MSHTML.IHTMLElement
Dim HTMLRow As MSHTML.IHTMLElement
Dim HTMLhh As MSHTML.IHTMLElement
Dim HTMLdates As MSHTML.IHTMLElement
Dim HTMLss As MSHTML.IHTMLElement
Dim Doc As HTMLDocument
Dim RowNum As Long, ColNum As Integer
Dim count As Long
Dim erow As Long
Application.Wait (Now + TimeValue("00:00:01"))
Set HTMLElements = IE.Document.getElementsByClassName("boxTableBorder")
'count = 0
For Each HTMLElement In HTMLElements
' If HTMLElement.className = "boxTableBorder" Then
'
' erow = ThisWorkbook.Sheets("pickups").Cells(Rows.count, 2).End(xlUp).Offset(1, 0).Row
' Cells(erow, 2) = HTML.getElementsByClassName("hh")(count).innerText
' Cells(erow, 3) = HTML.getElementsByClassName("dates")(count).innerText
' Cells(erow, 4) = HTML.getElementsByClassName("ss")(count).innerText
'
' count = count + 1
'
' End If
' ThisWorkbook.Sheets("pickups").Range("B2").Value = HTMLElement.className
' Worksheets.Add
' Range("A1").Value = HTMLElement.className
'
' RowNum = 1
'
For Each HTMLBody In HTMLElement.getElementsByTagName("tbody")
' For Each HTMLRow In HTMLElement.getElementsByTagName("tbody")
Debug.Print vbTab & HTMLBody.innerText
'
' ColNum = 1
' For Each HTMLCell In HTMLBody.Children
' For Each HTMLCell In HTMLRow.Children
' For Each HTMLhh In HTMLBody.getElementsByClassName("hh")
' Debug.Print vbTab & HTMLhh.innerText
'
' Next HTMLhh
'
' For Each HTMLdates In HTMLBody.getElementsByClassName("dates")
' Debug.Print vbTab & HTMLdates.innerText
'
' Next HTMLdates
' For Each HTMLss In HTMLBody.getElementsByClassName("ss")
' Debug.Print vbTab & HTMLss.innerText
'' Cells(RowNum, ColNum) = HTMLCell.innerText
'' ColNum = ColNum + 1
' Next HTMLss
' RowNum = RowNum + 1
Next HTMLBody
' Next HTMLRow
Next HTMLElement
'Range("B2:C20").Select
'Columns("B:B").EntireColumn.AutoFit
'Columns("C:C").EntireColumn.AutoFit
IE.Quit
Set IE = Nothing
End Sub
