I fixed two problems with the code

1) Added test to determine when you are already login.

these two line are equivalent
objIE.document.getElementById("txtUserID")
objIE.document.all("txtUserID")

I converted the old code to use getElementById and if the the code can't
find "txtUserID" on the 1st page you don't have to login.

2) I eliminate the timer from WaitForLoad. It seem to be working right now
not sure if it will work when the website is busy. I dont think basic like
you format for the loop. Visual basic has lots of syntax that just don't
work correctly.

The account number you gave me still has only 34 records. I,m going to
post a new version of the code that useds the export to excel option.

Private Sub Workbook_Open()
Net2Phone
End Sub

Sub Net2Phone()
Dim objIE As Object
Dim strServAcct As String

'strServAcct = "3787370105"

strServAcct = "3484690293"

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")
Password.Value = "my69car"

'submit the form by clicking "Login"
Set Login = objIE.document.getElementById("btnlogin")
Login.Click

WaitForLoad objIE
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"
WaitForLoad1 objIE
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("Hoja1")
Do While Done = False
' wait for History page to load
'or Next page when multiple pages
WaitForLoad1 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)


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

Sub WaitForLoad1(IE As Object)


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




"farid2001" wrote:

Joel

I got it to work, by creating:

Sub WaitForLoad1(IE As Object)
Application.Wait (Now + TimeValue("0:00:20"))

Do While IE.Busy And Not IE.ReadyState = 4
DoEvents
Loop
End Sub

So finally the code looks like this:

Private Sub Workbook_Open()
Net2Phone
End Sub

Sub Net2Phone()
Dim objIE As Object
Dim strServAcct As String

strServAcct = "3787370105"

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

objIE.document.all("ctl00$pageBody$txtServiceAccou nt").Value = strServAcct

objIE.document.all("ctl00$pageBody$btnSearch").Cli ck

WaitForLoad objIE

objIE.Navigate "https://partner.net2phone.com/apps/account/calls.aspx"
WaitForLoad1 objIE
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("Hoja1")
Do While Done = False
' wait for History page to load
'or Next page when multiple pages
WaitForLoad1 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)
Application.Wait (Now + TimeValue("0:00:04"))

Do While IE.Busy And Not IE.ReadyState = 4
DoEvents
Loop
End Sub

Sub WaitForLoad1(IE As Object)
Application.Wait (Now + TimeValue("0:00:20"))

Do While IE.Busy And Not IE.ReadyState = 4
DoEvents
Loop
End Sub

It works fine, but if the call history has 100 pages it will take forever,
is there a way of instead of going through each page in the call history,
that it can get it to click on the excel icon of the call's history first
page?

Thanks & regards
Farid

"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