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

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"

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