Macro to copy a data from web

Emily00

New Member
Joined
Mar 28, 2019
Messages
2
I am new to macros and VBA, so may ask some stupid question. I want to create a macro that will automatically enter data to a webpage and copy the result and paste to anther web. Now, I am able to let macro to loop through the result which is a table, but can't locate to the data I want.

The result table It's like below:

'<table class="contentContainer">
' <tr class="heading">…</tr>
' <tr class="odd">
'<td class="value">xxx-xxx-xxx-xx</td>
'<td class="value"></td>
'<td class="money">$999.00</td>
….


How ever my code only can locate the whole table, not the 11 digits number(xxx-xxx-xxx-xx) I need.

Function BillingSearch(ByRef IE As Object) As String
'Get policies in the user's queue
Dim TableNum, NextRow, CellIncre, blnFlag As Boolean, blnStartTable As Boolean
Dim intRemove As Integer, strPolicy As String
Dim doc As Object, intFlag As Integer
On Error GoTo Err
TableNum = 0
NextRow = 0
CellIncre = 1
Set doc = IE.Document
'Loop through each table
For Each Table In doc.getElementsByTagName("TABLE")
TableNum = TableNum + 1
'Loop through each table row
For Each TableRow In Table.Rows
NextRow = NextRow + 1
'Loop through each cell
For Each TableCell In TableRow.Cells
CellIncre = CellIncre + 1
Debug.Print TableCell.innerText
'Flag on Past Due
If Trim(TableCell.innerText) = "PastDue" Then
blnFlag = True
MsgBox "stop"
End If
If blnFlag = True Then 'And CellIncre = 10
MsgBox "stop"
BillingSearch = TableCell.innerText
Exit Function
End If
Next TableCell
CellIncre = 0
Next TableRow
NextRow = 0
Next Table
'Show all the policies to allow for user selection
BillingSearch = ""
Exit Function

Thanks in advance for your help!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
I am new to macros and VBA, so may ask some stupid question. I want to create a macro that will automatically enter data to a webpage and copy the result and paste to anther web. Now, I am able to let macro to loop through the result which is a table, but can't locate to the data I want.

The result table It's like below:

'table class="contentContainer"
tr class="heading".../tr
tr class="odd"
td class="value" xxx-xxx-xxx-xx /td
td class="value" /td
td class="money"$100 /td

….


How ever my code only can locate the whole table, not the 11 digits number(xxx-xxx-xxx-xx) I need.

Function BillingSearch(ByRef IE As Object) As String
'Get policies in the user's queue
Dim TableNum, NextRow, CellIncre, blnFlag As Boolean, blnStartTable As Boolean
Dim intRemove As Integer, strPolicy As String
Dim doc As Object, intFlag As Integer
On Error GoTo Err
TableNum = 0
NextRow = 0
CellIncre = 1
Set doc = IE.Document
'Loop through each table
For Each Table In doc.getElementsByTagName("TABLE")
TableNum = TableNum + 1
'Loop through each table row
For Each TableRow In Table.Rows
NextRow = NextRow + 1
'Loop through each cell
For Each TableCell In TableRow.Cells
CellIncre = CellIncre + 1
Debug.Print TableCell.innerText
'Flag on Past Due
If Trim(TableCell.innerText) = "PastDue" Then
blnFlag = True
MsgBox "stop"
End If
If blnFlag = True Then 'And CellIncre = 10
MsgBox "stop"
BillingSearch = TableCell.innerText
Exit Function
End If
Next TableCell
CellIncre = 0
Next TableRow
NextRow = 0
Next Table
'Show all the policies to allow for user selection
BillingSearch = ""
Exit Function

Thanks in advance for your help!


[TABLE="class: contentContainer"]
<tbody>[TR="class: heading"]
[/TR]
[TR="class: odd"]
[TD]class="value">xxx-xxx-xxx-xx[/TD]
[TD="class: value"][/TD]
[TD="class: money"]$999.00[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Forum statistics

Threads
1,223,879
Messages
6,175,145
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top