Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Download data from secure website into new Worksheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Download data from secure website into new Worksheet
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" '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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Download data from secure website into new Worksheet
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 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" '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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Download data from secure website into new Worksheet
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 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" |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Download data from secure website into new Worksheet
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 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" |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Download data from secure website into new Worksheet
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. "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 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Download data from secure website into new Worksheet
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 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Download data from secure website into new Worksheet
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$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) '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 "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 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Download data from secure website into new Worksheet
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$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" 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 "joel" wrote: 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$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) '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 "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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Download data from secure website into new Worksheet
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$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" 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 "farid2001" wrote: 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$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" 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 "joel" wrote: 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$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) '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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Download data from secure website into new Worksheet
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 "joel" wrote: 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$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" 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 "farid2001" wrote: 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$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" 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 "joel" wrote: 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Download data from secure website into new Worksheet
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$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" 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 "farid2001" wrote: 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 "joel" wrote: 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$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" 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 "farid2001" wrote: 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$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" 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 "joel" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Login into secure web site and download data into worksheet? | Excel Programming | |||
Import data from a secure website to Excel | Excel Programming | |||
How do I download data into MS Excel from a Website that takes a l | Excel Worksheet Functions | |||
can Excel connect and download data from a Stockmarket Website? | Excel Programming | |||
Download data from website | Excel Programming |