2020spencer
New Member
- Joined
- Apr 18, 2020
- Messages
- 4
- Office Version
- 2007
- Platform
- Windows
Here is my code
Sub po()
'
' po Macro
'
Dim i As Integer
Dim doc, hcol, idoc, text As Variant
Dim currentInnerHtml As String
Dim objIE As Object
Dim post As Object, elem As Object
Set ie = CreateObject("internetexplorer.application")
ie.navigate "恒生指數期貨及期權"
ie.Visible = True
Do While ie.Busy
Do: Loop Until ie.readyState = 8 Or Not ie.Busy
Loop
Set idoc = ie.document.getElementsByTagName("input")
For Each idoc In idoc
If idoc.getAttribute("aria-label") = "Strike From" Then
idoc.Value = "15,000"
Exit For
End If
Next idoc
Set idoc = ie.document.getElementsByTagName("input")
For Each idoc In idoc
If idoc.getAttribute("aria-label") = "Strike To" Then
idoc.Value = "32,000"
Exit For
End If
SendKeys "{ENTER}"
Set doc = ie.document
Set hTable = doc.getElementsByTagName("table")
y = 2 'Column B in Excel
Z = 7 'Row 7 in Excel
For Each tb In hTable
Set hHead = tb.getElementsByTagName("thead")
For Each hh In hHead
Set hTR = hh.getElementsByTagName("tr")
For Each Tr In hTR
Set hTD = Tr.getElementsByTagName("th")
y = 1 ' Resets back to column A
For Each th In hTD
Cells(Z, y).Value = th.innerText
y = y + 1
Next th
DoEvents
Z = Z + 1
Next Tr
Exit For
Next hh
'Exit For
Set hBody = tb.getElementsByTagName("tbody")
For Each bb In hBody
Set hTR = bb.getElementsByTagName("tr")
For Each Tr In hTR
Set hTD = Tr.getElementsByTagName("td")
y = 1 ' Resets back to column A
For Each Td In hTD
Cells(Z, y).Value = Td.innerText
y = y + 1
Next Td
DoEvents
Z = Z + 1
Next Tr
Exit For
Next bb
Z = Z + 1
'Exit For
Next tb
Next
End Sub
The above set will copy and paste table data except hidden parts/.
Sub po()
'
' po Macro
'
Dim i As Integer
Dim doc, hcol, idoc, text As Variant
Dim currentInnerHtml As String
Dim objIE As Object
Dim post As Object, elem As Object
Set ie = CreateObject("internetexplorer.application")
ie.navigate "恒生指數期貨及期權"
ie.Visible = True
Do While ie.Busy
Do: Loop Until ie.readyState = 8 Or Not ie.Busy
Loop
Set idoc = ie.document.getElementsByTagName("input")
For Each idoc In idoc
If idoc.getAttribute("aria-label") = "Strike From" Then
idoc.Value = "15,000"
Exit For
End If
Next idoc
Set idoc = ie.document.getElementsByTagName("input")
For Each idoc In idoc
If idoc.getAttribute("aria-label") = "Strike To" Then
idoc.Value = "32,000"
Exit For
End If
SendKeys "{ENTER}"
Set doc = ie.document
Set hTable = doc.getElementsByTagName("table")
y = 2 'Column B in Excel
Z = 7 'Row 7 in Excel
For Each tb In hTable
Set hHead = tb.getElementsByTagName("thead")
For Each hh In hHead
Set hTR = hh.getElementsByTagName("tr")
For Each Tr In hTR
Set hTD = Tr.getElementsByTagName("th")
y = 1 ' Resets back to column A
For Each th In hTD
Cells(Z, y).Value = th.innerText
y = y + 1
Next th
DoEvents
Z = Z + 1
Next Tr
Exit For
Next hh
'Exit For
Set hBody = tb.getElementsByTagName("tbody")
For Each bb In hBody
Set hTR = bb.getElementsByTagName("tr")
For Each Tr In hTR
Set hTD = Tr.getElementsByTagName("td")
y = 1 ' Resets back to column A
For Each Td In hTD
Cells(Z, y).Value = Td.innerText
y = y + 1
Next Td
DoEvents
Z = Z + 1
Next Tr
Exit For
Next bb
Z = Z + 1
'Exit For
Next tb
Next
End Sub
The above set will copy and paste table data except hidden parts/.