Download data from secure website into new Worksheet

F

farid2001

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"

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

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

' wait for Service Account page to load
WaitForLoad objIE

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

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

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

Usually, I just click the excel icon on the website for the excel file to
download, is there an automated way to download the data into a new Worksheet
in the Workbook?

Your help will be greatly appreciated.

Thanks & regards
farid2001
 
F

farid2001

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
 
J

joel

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

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

' 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
 
J

joel

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

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

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

farid2001

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
 
F

farid2001

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

objIE.document.all("ctl00$pageBody$btnSearch").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)
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
 
J

joel

I'll try in the morning. When you save with excel a pop up window comes up
asking you if you wan to save he file asking the location. I'm not sure if I
can bypas this menu. The time it is taking is due to the 20 seconds you
added and he fact that you are trying to retrieve a large amount of data from
a database. I'm also going to try to eliminate the timer. Usually I dont
need to use the timer. Checking for 4 and ready is usually enough.
 
J

joel

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$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"
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
 
J

joel

I added to code previous code to export to excel. I left all the other code
intact so you can modifiy as required. the present code wil use my old code
to put one page into a work sheet and then call the Download to Excel option
and exit.



Private Sub Workbook_Open()
Net2Phone
End Sub

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

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$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"
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)
'code to download to excel option
If InStr(src, "EXCEL") > 0 Then
itm.Onclick = src
'gop to next page
itm.Click
Exit For
End If
'--------------------------------------
'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
 
F

farid2001

Joel

Thank you very much for your help.

I runned the following code successfully and for a 360 call's Call History
took 84 seconds, which is not bad at all.

Private Sub Workbook_Open()
Net2Phone
End Sub

Sub Net2Phone()
' PERFECT FOR UNLOADING MID-SIZE CALL_HISTORY TO WORKBOOK!!
Application.ScreenUpdating = False
Dim StartTime As Date, EndTime As Date
StartTime = Timer

Dim objIE As Object
Dim strServAcct As String

strServAcct = "4872750789"

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.Value 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
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
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
EndTime = Timer
MsgBox Format(EndTime - StartTime, " 0.000 Segundos"), , "WHL Analisys
Tool"
End Sub

Sub WaitForLoad(IE As Object)
Do While IE.Busy = True Or IE.readystate <> 4
DoEvents
Loop
End Sub

What would the complete code be for just downloading the excel file, without
transferring the calls to the workbook at all?

Thanks & regards
Farid
 
J

joel

Private Sub Workbook_Open()
Net2Phone
End Sub

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

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$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"
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"
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)
'code to download to excel option
If InStr(src, "EXCEL") > 0 Then
itm.Onclick = src
'gop to next page
itm.Click
Exit For
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
 
F

farid2001

Hello Joel

Thanks for your help.

I gert the following error when I click on the Open button of the web
browser when the download call history web dialog pops up:

Error on Method "Busy" of the Object "IWebBrowser2"

Thanks & regards
farid
 
F

farid2001

Hello Joel

I really appreciate all your time and effort given to solve this problem.
Finally I have your code working perfectly:

This is how your code looks now:

Private Sub Workbook_Open()
Sheets.Add

Net2Phone
End Sub

Sub Net2Phone()

Dim objIE As Object
Dim strServAcct As String
Dim Password As Object

strServAcct = "2544214270"

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
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("B").NumberFormat = "dd/mm hh:mm:ss"
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
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

objIE.Application.Quit

Call Macro_Fama_Per_Ru_e

Call Macro_pegado_valores

Application.ActiveWorkbook.Save
End Sub

Sub WaitForLoad(IE As Object)

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

I used the ObjIE.Application.Quit since I plan to automate the procedure to
run automatically about 100+ accounts at night and I don't think that 4G's of
memory will be able to hold so many openned IE applications.

Again, I thank you very much for all the help you have given me with your
code.
Regards
farid2001
 

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 web page 11
Web Queries 2
Worksheet has corrupt name after download of csv 2
Help pulling data from web page 1

Top