Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Queries
Dear Gentlemen
I have the following problem: I am running this code successfully from a module in a workbook: Private Declare Function ShellExecute Lib "shell32.dll" _ Alias "ShellExecuteA" (ByVal hWnd As Long, _ ByVal lpOperation As String, ByVal lpFile As String, _ ByVal lpParameters As String, ByVal lpDirectory As String, _ ByVal nShowCmd As Long) As Long __________________________________________________ _____ Sub OpenURL() Dim URL As String Dim Result As Long URL = "https://partner.net2phone.com/apps/account/calls.aspx" Result = ShellExecute(0&, vbNullString, URL, _ vbNullString, vbNullString, vbNormalFocus) If Result < 32 Then MsgBox "Error" End Sub Private Sub Workbook_Open() OpenURL End Sub The problem is that the web page will only open the calls report of the Service Account that is in the web page at that particular moment, what I need is the proper code so that it would open the calls report for the Service Account of the workbook, since I have a different workbook for every Service Account. Login URL: https://partner.net2phone.com/apps/common/login.aspx the login is: famaperu45 the password is: my69car Service Account to test: 3484690293 4211117659 2965282207 Your help will be greatly appreciated. Thanks & regards farid2001 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Queries
On May 6, 5:32*pm, farid2001
wrote: Dear Gentlemen I have the following problem: I am running this code successfully from a module in a workbook: Private Declare Function ShellExecute Lib "shell32.dll" _ * * Alias "ShellExecuteA" (ByVal hWnd As Long, _ * * ByVal lpOperation As String, ByVal lpFile As String, _ * * ByVal lpParameters As String, ByVal lpDirectory As String, _ * * ByVal nShowCmd As Long) As Long __________________________________________________ _____ * * Sub OpenURL() * * Dim URL As String * * Dim Result As Long * * URL = "https://partner.net2phone.com/apps/account/calls.aspx" * * Result = ShellExecute(0&, vbNullString, URL, _ * * * * vbNullString, vbNullString, vbNormalFocus) * * If Result < 32 Then MsgBox "Error" End Sub Private Sub Workbook_Open() * * OpenURL End Sub The problem is that the web page will only open the calls report of the Service Account that is in the web page at that particular moment, what I need is the proper code so that it would open the calls report for the Service Account of the workbook, since I have a different workbook for every Service Account. Login URL:https://partner.net2phone.com/apps/common/login.aspx the login is: famaperu45 the password is: my69car Service Account to test: 3484690293 4211117659 2965282207 Your help will be greatly appreciated. Thanks & regards farid2001 Farid2001, There is some sample IE automation code below. The real credit belongs to Tim Williams. Though I haven't done any IE automation in over 18 months, Tim's code has always stuck with me. The code should do the trick for you. I had to add in "Application.Wait (Now + TimeValue("0:00:02"))" into the WaitForLoad procedure because the program was tripping over itself when I ran it (i.e. the program thinks the internet page is loaded when it is not really loaded), so you may have to play around with the timing. (Even when I placed DoEvents prior to the loop execution in WaitForLoad, the program would trip up, so .Wait was my solution). Best, Matthew Herbert 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 End Sub Sub WaitForLoad(IE As Object) 'wait until current page is loaded Application.Wait (Now + TimeValue("0:00:02")) Do While IE.Busy And Not IE.ReadyState = 4 DoEvents Loop End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Queries
Matthew
Thank you very much, your code worked perfectly. I added a couple lines to your code, so that it would take me to the Call History page. This is what I ended up with: Private Sub Workbook_Open() Net2Phone End Sub Sub Net2Phone() Dim objIE As Object Dim strServAcct As String 'set the service account number strServAcct = "3275474783" '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 = "temprm1" objIE.document.all("txtPassword").Value = "weterrds23" '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:06")) Do While IE.Busy And Not IE.ReadyState = 4 DoEvents Loop End Sub This worked to perfection for all the accounts. Is there a way to transfer the data from the Call History into the workbook? Thanks & regards Farid " wrote: On May 6, 5:32 pm, farid2001 wrote: Dear Gentlemen I have the following problem: I am running this code successfully from a module in a workbook: Private Declare Function ShellExecute Lib "shell32.dll" _ Alias "ShellExecuteA" (ByVal hWnd As Long, _ ByVal lpOperation As String, ByVal lpFile As String, _ ByVal lpParameters As String, ByVal lpDirectory As String, _ ByVal nShowCmd As Long) As Long __________________________________________________ _____ Sub OpenURL() Dim URL As String Dim Result As Long URL = "https://partner.net2phone.com/apps/account/calls.aspx" Result = ShellExecute(0&, vbNullString, URL, _ vbNullString, vbNullString, vbNormalFocus) If Result < 32 Then MsgBox "Error" End Sub Private Sub Workbook_Open() OpenURL End Sub The problem is that the web page will only open the calls report of the Service Account that is in the web page at that particular moment, what I need is the proper code so that it would open the calls report for the Service Account of the workbook, since I have a different workbook for every Service Account. Login URL:https://partner.net2phone.com/apps/common/login.aspx the login is: famaperu45 the password is: my69car Service Account to test: 3484690293 4211117659 2965282207 Your help will be greatly appreciated. Thanks & regards farid2001 Farid2001, There is some sample IE automation code below. The real credit belongs to Tim Williams. Though I haven't done any IE automation in over 18 months, Tim's code has always stuck with me. The code should do the trick for you. I had to add in "Application.Wait (Now + TimeValue("0:00:02"))" into the WaitForLoad procedure because the program was tripping over itself when I ran it (i.e. the program thinks the internet page is loaded when it is not really loaded), so you may have to play around with the timing. (Even when I placed DoEvents prior to the loop execution in WaitForLoad, the program would trip up, so .Wait was my solution). Best, Matthew Herbert 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 End Sub Sub WaitForLoad(IE As Object) 'wait until current page is loaded Application.Wait (Now + TimeValue("0:00:02")) Do While IE.Busy And Not IE.ReadyState = 4 DoEvents Loop End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Queries | Excel Discussion (Misc queries) | |||
Web Queries | Excel Discussion (Misc queries) | |||
Web Queries | Excel Programming | |||
Queries | Excel Discussion (Misc queries) | |||
Web Queries | Excel Discussion (Misc queries) |