Download data from secure website into new Worksheet
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
|