Download data from web page

F

farid2001

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$txtServiceAccount")
AccountBox.Value = strServAcct

Set SearchButton = _
objIE.document.getElementById("ctl00$pageBody$btnSearch")
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
 
J

Joel

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$txtServiceAccount")
AccountBox.Value = strServAcct

Set SearchButton = _
objIE.document.getElementById("ctl00$pageBody$btnSearch")
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_sctnTitleCtrl_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_Label2")
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
 
F

farid2001

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
 
J

Joel

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
 
J

Joel

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.
 
F

farid2001

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
 
J

Joel

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.
 
F

farid2001

Joel

Thanks a million for your help with the code.
I'm a user of the webpages, and your code is helping me with reports that
would otherwise take hours to make.

Would it be possible to add a column with the date of the last call of each
account? That is actually the first Call Date/Time that appears on the "Grid
View" Call History table.

I really appreciate your help.

Thanks & regards
Farid
 
J

Joel

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

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


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

Set AccountBox = _
objIE.document.getElementById("ctl00$pageBody$txtServiceAccount")
AccountBox.Value = strServAcct

Set SearchButton = _
objIE.document.getElementById("ctl00$pageBody$btnSearch")
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_sctnTitleCtrl_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_Label2")
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
Set CallHistoryTable = _
objIE.document.getElementById("ctl00_pageBody_gvCall")
If Not CallHistoryTable Is Nothing Then
If CallHistoryTable.Rows.Length >= 2 Then
Set FirstEntry = CallHistoryTable.Rows(1)
CallDate = FirstEntry.Cells(1).innertext
SumSht.Range("D" & RowCount) = CallDate
End If

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
 
F

farid2001

Hello Joel

Thanks for your prompt response.

As soon as the date downloads into the workbook, I get error message 91 in
execution time: "Object variable or block With not established"

Thanks & regards
Farid
 
J

Joel

I tested the code only with one service account. I also only added the
following lines

Set CallHistoryTable = _
objIE.document.getElementById("ctl00_pageBody_gvCall")
If Not CallHistoryTable Is Nothing Then
If CallHistoryTable.Rows.Length >= 2 Then
Set FirstEntry = CallHistoryTable.Rows(1)
CallDate = FirstEntry.Cells(1).innertext
SumSht.Range("D" & RowCount) = CallDate
End If

End If



Either remove these lines and see if the code stil runs, or add these lines
into a prevvious verion of the code and see if it works. I may of accidently
changed something that i'm not aware of. There is nothing in the above
change that would cuase this error.

I'm also wondering if the problem may have to do with the format of the
cells. Writing a date into a cell that is formated as general, text, or data
is ok. Yo may wqant to change the "D" column format to date and try again
and see if this curese the problem.
 
F

farid2001

Joel

It worked!!
I took a previous Code and just added the new code as you indicated and it
worked perfectly.

I really appreciate all your help given.

Thanks & regards
Farid
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

HELP!! Download data to workbook 4
Download data from secure website into new Worksheet 12
Pick data from web page (no query) 14
Help pulling data from web page 1

Top