I don't know is you have anything to do with developing these webpages but it
is extremely hard to extract the data you wanted. there weren't any unique
tags or ID's to get the tow dollar amont you were looking for. I had two
ID's that I was able to search for the text boxes but the dollar amounts were
not directly available using the IDs. I had to move up the web sheet struct
using the parent property to find the dollar amount.

Also if the navigate controls used the functionality of the control (Call
History) rather than a number 7 or 9 which was different on two web pages you
wouldn't of had the problem where you didn't get data.

"farid2001" wrote:

Joel

You are absolutely an MVP!!

What happened is that I loged into my master web page, and the link for call
history there is not ("ctl00_tvLeftNavn7"), it is ("ctl00_tvLeftNavn9"), that
is why I was not getting any data into my workbook.

I really apperciate your help.
Your code works to perfection!!

Thanks & regards
Farid

"Joel" wrote:

Make sure you copy the entire macro I posted. I had the same problem and
changed this section of code before I posted the macro

'to get new account must navigate using control on page
Set CallHistoryNavigate = _
objIE.document.getElementById("ctl00_tvLeftNavn7")
CallHistoryNavigate.onclick = CallHistoryNavigate.href
CallHistoryNavigate.Click

You had a navigate in your original macro that didn't work. It seems you
have to navigate through the webpage and not naviage by putting the URL iunto
the address box. When I put the URL into the address box I kept getting the
first account data over and over again.

"Joel" wrote:

I just copied my code from the posting and re-ran the macro and below is what
I got. I had to fix 1 line that was too long and wrapped. I put in sheet 1
the header row and the account numbers in column A. The macro filled in
columns B & C.


strServAcct Cash Balance Total Cash Used
1190535741 19.88 USD 29.18 USD
1615242148 5.38 USD 67.25 USD
3484690293 6.25 USD 3.35 USD
6689883508 25.95 USD 42.29 USD


"farid2001" wrote:

Hello Joel

Thank you very much for your quick responce.

I've tried your code but nothing happens in the workbook, the web page goes
thru every account but does not unload any data.
Looks like is not going into the Call History page.

Regards
farid

"Joel" wrote:

Try This.

Sub Net2Phone1() 'Credit to Joel
Dim objIE As Object
Dim strServAcct As String
Dim Password As Object

Set SumSht = Sheets("Sheet1")
RowCount = 2

Set objIE = CreateObject("InternetExplorer.Application")
objIE.Visible = True

Do While SumSht.Range("A" & RowCount) < ""
strServAcct = SumSht.Range("A" & RowCount)

objIE.Navigate "https://partner.net2phone.com/apps/account/search.aspx"
WaitForLoad objIE

Set UserId = objIE.document.getElementById("txtUserID")
If Not UserId Is Nothing Then
'Input user name and password
UserId.Value = "famaperu45"
Set Password = objIE.document.getElementById("txtPassword")
If Not Password Is Nothing Then
Password.Value = "my69car"

'submit the form by clicking "Login"
Set Login = objIE.document.getElementById("btnlogin")
Login.Click
WaitForLoad objIE
End If
End If
Set AccountBox = _
objIE.document.getElementById("ctl00$pageBody$txtS erviceAccount")
AccountBox.Value = strServAcct

Set SearchButton = _
objIE.document.getElementById("ctl00$pageBody$btnS earch")
SearchButton.Click
WaitForLoad objIE

'to get new account must navigate using control on page
Set CallHistoryNavigate = _
objIE.document.getElementById("ctl00_tvLeftNavn7")
CallHistoryNavigate.onclick = CallHistoryNavigate.href
CallHistoryNavigate.Click

WaitForLoad objIE
' in this web page are the "Cash Balance" and the "Total Cash Used"

Set CashBalance = _

objIE.document.getElementById("ctl00_pageBody_sctn TitleCtrl_lblTitle")
If Not CashBalance Is Nothing Then
'go back 2 tag tables
Set MyPoint = CashBalance
CountTables = 0
Do While CountTables < 2
Set MyPoint = MyPoint.ParentNode
If MyPoint.tagname = "TABLE" Then
CountTables = CountTables + 1
End If
Loop

SumSht.Range("B" & RowCount) = _
MyPoint.all(10).innertext
End If


Set TotalCash = _
objIE.document.getElementById("ctl00_pageBody_Labe l2")
If Not CashBalance Is Nothing Then
'go back 1 tag TR
Set MyPoint = TotalCash
CountTables = 0
Do While CountTables < 1
Set MyPoint = MyPoint.ParentNode
If MyPoint.tagname = "TR" Then
CountTables = CountTables + 1
End If
Loop

SumSht.Range("C" & RowCount) = _
MyPoint.all(5).innertext
End If

RowCount = RowCount + 1
Loop

objIE.Quit
Set objIE = Nothing
End Sub

Sub WaitForLoad(IE As Object)

Do While IE.Busy = True Or IE.readystate < 4
DoEvents
Loop
End Sub



"farid2001" wrote:

Dear Gentlemen

I need help for code to download for each strServAcct 2 items from web page
into cells in workbook. This 2 items are "Cash Balance" and "Total Cash Used"

This is how worksheet should look like:

Col A Col B Col C
strServAcct Cash Balance Total Cash Used
1190535741 xx.xx USd xx.xx USD
1615242148 xx.xx USd xx.xx USD
3484690293 xx.xx USd xx.xx USD
6689883508 xx.xx USd xx.xx USD

This is what I have so far:

Private Sub Workbook_Open()
Net2Phone1
End Sub


Sub Net2Phone1() 'Credit to Joel
Dim objIE As Object
Dim strServAcct As String
Dim Password As Object

strServAcct = "1190535741"

Set objIE = CreateObject("InternetExplorer.Application")
objIE.Visible = True
objIE.Navigate "https://partner.net2phone.com/apps/account/search.aspx"
WaitForLoad objIE

Set UserId = objIE.document.getElementById("txtUserID")
If Not UserId Is Nothing Then
'Input user name and password
UserId.Value = "famaperu45"
Set Password = objIE.document.getElementById("txtPassword")
If Not Password Is Nothing Then
Password.Value = "my69car"

'submit the form by clicking "Login"
Set Login = objIE.document.getElementById("btnlogin")
Login.Click
WaitForLoad objIE
End If
End If
Set AccountBox = _
objIE.document.getElementById("ctl00$pageBody$txtS erviceAccount")
AccountBox.Value = strServAcct

Set SearchButton = _
objIE.document.getElementById("ctl00$pageBody$btnS earch")
SearchButton.Click
WaitForLoad objIE

objIE.Navigate "https://partner.net2phone.com/apps/account/calls.aspx"
WaitForLoad objIE
' in this web page are the "Cash Balance" and the "Total Cash Used"
End Sub

Sub WaitForLoad(IE As Object)

Do While IE.Busy = True Or IE.readystate < 4
DoEvents
Loop
End Sub

Your help will be greatly appreciated.

Thanks & regards
farid2001