Web Queries

[farid2001]

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

 

[meh2030]

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

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

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

 

[farid2001]

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

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

' wait for Service Account page to load
WaitForLoad objIE

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

Sub WaitForLoad(IE As Object)
'wait until current page is loaded
Application.Wait (Now + TimeValue("0:00: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