Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Download data from web page
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 56
Default Download data from web page
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Download data from web page
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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 56
Default Download data from web page
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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Download data from web page
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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Download data from web page
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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 56
Default Download data from web page
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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Email (LDAP) data download into a single Excel cell - data separat MSA Excel Worksheet Functions 1 March 4th 08 05:14 PM
items chosen to download are NEVER all there after download comple jwhitehurst New Users to Excel 3 November 9th 07 04:00 PM
SAP download Web page to XLS Cathy Excel Programming 0 September 8th 07 01:58 AM
practice sessions won't download get error on page big bear New Users to Excel 0 June 14th 05 07:25 PM
when I try to download excel template, it says Error on page Debbie Excel Discussion (Misc queries) 0 February 27th 05 06:11 PM


All times are GMT +1. The time now is 08:07 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement