Joel

Thank's for your prompt response, here is a Service Account that you can try
which has more than 2 pages:
1253250258
I am having a problem that it keeps constantly clicking and the page only
repeits the data from page1 in sheet1

Thanks & regards
farid2001

"joel" wrote:

This was tough to get right. I'm not sure if it wil work with more than two
pages since I had only one phone number with two pages. The bottom border of
the call history table has a few options like
Print
Export to Excel
Move to First page Next_PG_0
Move to Previous Page
Move to Next Page Next_PG_1
Move to Last page

I had to look for Next_PG_1 and I don't know if there are more than 2 pages
if you would get Next_PG_2 or something else. So I'm looking for the word
"NEXT" and ignoring "NEXT_PG_0" so I don't get stuck in a loop always going
back to the 1st page (PG_0).




Private Sub Workbook_Open()
Net2Phone
End Sub

Sub Net2Phone()

Dim objIE As Object
Dim strServAcct As String

'set the service account number
strServAcct = "3484690293"

'Open Internet Explorer
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Visible = True
objIE.Navigate "https://partner.net2phone.com/apps/common/login.aspx"
WaitForLoad objIE

'Input user name and password
objIE.document.all("txtUserID").Value = "famaperu45"
objIE.document.all("txtPassword").Value = "my69car"

'submit the form by clicking "Login"
objIE.document.all("btnlogin").Click
WaitForLoad objIE

'insert the service account number
objIE.document.all("ctl00$pageBody$txtServiceAccou nt").Value = strServAcct

'click the "Search" button
objIE.document.all("ctl00$pageBody$btnSearch").Cli ck

' wait for Service Account page to load
WaitForLoad objIE

' Go to Call History page
objIE.Navigate "https://partner.net2phone.com/apps/account/calls.aspx"

RowCount = 1
'format the phone number as text
'long phone number become scientific notsation 1.3E+14
'which don't look like phone numbers
Columns("C").NumberFormat = "@"

RowCount = 1
'Done indicats we have no more pages in the call history
Done = False
With Sheets("sheet3")
Do While Done = False
' wait for History page to load
'or Next page when multiple pages
WaitForLoad objIE

'the call history table start with class name equal gridview
State = "Find_GridView"
'get all items in html data
For Each itm In objIE.document.all

Select Case State

'skip everything until GRIDVIEW is found
Case "Find_GridView":
'when gridview is found output header row
If itm.classname = "gridview" Then
'set state to grid view to process
'call histor table
State = "Found_GridView"
'only put header row for 1st page
If RowCount = 1 Then
ColCount = 1
'put header row on worksheet
For Each cell In itm.Cells
.Cells(RowCount, ColCount) = cell.innertext
ColCount = ColCount + 1
Next cell
RowCount = RowCount + 1
End If
End If
Case "Found_GridView":
'Input tag is the table on spreadsheet
'moving to next and previous page
If itm.tagname = "INPUT" Then
src = UCase(itm.src)
'we are looking for item NEXT_PG_1
'to move to next page.
'not sure when there are more than two pages
'what the src is going to look like
'so I'm going to move to next page
'except under the case NEXT_PG_0
If InStr(src, "NEXT") 0 Then
If InStr(src, "NEXT_PG_0") = 0 Then
'found next page
'need to execute the command
'so I put the command in OnClick
'Then execute on click
itm.Onclick = src
'gop to next page
itm.Click
Exit For
End If
End If
End If

'If we don't find next page then we are at
'last page and the end of the gridview table
'is indicated by the ! - stop processing
If itm.tagname = "!" Then
'reached last page - stop
Done = True
Exit For
End If
'each item in call history has the tag TR
If itm.tagname = "TR" Then
'the end of the call history is a blank innertext
'we don't w\ant to put blank data into worksheet
'we can't end because we still need to check for
'a next page
If itm.innertext < "" Then
ColCount = 1
For Each cell In itm.Cells
.Cells(RowCount, ColCount) = cell.innertext
ColCount = ColCount + 1
Next cell
RowCount = RowCount + 1
End If
End If
End Select
Next itm
Loop
End With
End Sub

Sub WaitForLoad(IE As Object)

'wait until current page is loaded
Application.Wait (Now + TimeValue("0:00:05"))

Do While IE.readyState < 4
DoEvents
Loop

Do While IE.busy = True
DoEvents
Loop

End Sub





"farid2001" wrote:

Joel, thanks a million for your help!!!

I just tried your code and worked to perfection, I really appreciate your
help.
Hopefuly you'll have the rest of the code on Sunday.

I had given up and posted another message 5 minutes before I saw your reply.

Regards
farid2001

"joel" wrote:

I got the first page of data. If nobody else finishes the code I will work
on it over the weekend. Probably won't hav etime until Sunday morning.


Private Sub Workbook_Open()
Net2Phone
End Sub

Sub Net2Phone()

Dim objIE As Object
Dim strServAcct As String

'set the service account number
strServAcct = "3484690293"

'Open Internet Explorer
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Visible = True
objIE.Navigate "https://partner.net2phone.com/apps/common/login.aspx"
WaitForLoad objIE

'Input user name and password
objIE.document.all("txtUserID").Value = "famaperu45"
objIE.document.all("txtPassword").Value = "my69car"

'submit the form by clicking "Login"
objIE.document.all("btnlogin").Click
WaitForLoad objIE

'insert the service account number
objIE.document.all("ctl00$pageBody$txtServiceAccou nt").Value = strServAcct

'click the "Search" button
objIE.document.all("ctl00$pageBody$btnSearch").Cli ck

' wait for Service Account page to load
WaitForLoad objIE

' Go to Call History page
objIE.Navigate "https://partner.net2phone.com/apps/account/calls.aspx"

' wait for History page to load
WaitForLoad objIE

RowCount = 1
Columns("C").NumberFormat = "@"
State = "Find_GridView"
For Each itm In objIE.document.all

Select Case State

Case "Find_GridView":
If itm.classname = "gridview" Then
State = "Found_GridView"
ColCount = 1
For Each cell In itm.Cells
Cells(RowCount, ColCount) = cell.innertext
ColCount = ColCount + 1
Next cell
RowCount = RowCount + 1
End If
Case "Found_GridView":
If itm.tagname = "!" Then
Exit For
End If
If itm.tagname = "TR" Then
ColCount = 1
For Each cell In itm.Cells
Cells(RowCount, ColCount) = cell.innertext
ColCount = ColCount + 1
Next cell
RowCount = RowCount + 1
End If
End Select
Next itm

End Sub

Sub WaitForLoad(IE As Object)

'wait until current page is loaded
Application.Wait (Now + TimeValue("0:00:05"))

Do While IE.readyState < 4
DoEvents
Loop

Do While IE.busy = True
DoEvents
Loop

End Sub



"farid2001" wrote:

Dear Gentlemen

I need help in code to download data from a secure website into a new
Worksheet in a Workbook.
So far this is what I have, credit to Matthew Herbert, which takes me to the
page that has data I want to download:

Private Sub Workbook_Open()
Net2Phone
End Sub

Sub Net2Phone()

Dim objIE As Object
Dim strServAcct As String

'set the service account number
strServAcct = "3484690293"