Hello all! Is it possible to configure a script in Excel that will download
a dynamic report from a secure site? We have a list of vendors that is
updated regularly from our service supplier, and a report posted in Crystal
Reports to the secure extranet. I'd like to use a script in Excel to open
the appropriate page, sign in using my credentials, browse to the report and
import the results into a spreadsheet, then comare the newly generated list
against our internal list to see if any of the changes pertain to us. Is
this possible? MS says that the web query doesn't work from secure sites,
but I'm wondering if I can program the script to some kind of step-by-step
instructions that would enter the commands as if I were at my keyboard.
Appreciate any help,
GP

This can be done, but it is tricky. If you send me some details I
could help you out.

I would start by modifying this function I use:

Function Get_Online_Report()
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
.navigate "https://Your Site Name Here"
Do Until .readystate = 4
DoEvents
Loop
With .document.forms(0)
.Item("UserName").Value = "Your UserName Here"
.Item("Password").Value = "Your Password Here"
.Item("The Submit Button").Click
End With
End With
Set ie = Nothing
End Function

Dave,
I have the same problem as you were discussing below, downloading my account
data from my secure broker's site. I tried both Web Query and Macro and got
stopped right at the login step. Can I use your script and how do I run it in
my case, since I have to login then go to another page and download from that
page data that is only contained in one frame?

Thanks for your help.
--
XL Baby


"Dave Miller" wrote:

This can be done, but it is tricky. If you send me some details I
could help you out.

I would start by modifying this function I use:

Function Get_Online_Report()
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
.navigate "https://Your Site Name Here"
Do Until .readystate = 4
DoEvents
Loop
With .document.forms(0)
.Item("UserName").Value = "Your UserName Here"
.Item("Password").Value = "Your Password Here"
.Item("The Submit Button").Click
End With
End With
Set ie = Nothing
End Function

Again, I would need a specific site inorder to help you out.

Dave,
The specific site is:
www.schwab.com
Thanks.
--
XL Baby


"Dave Miller" wrote:

Again, I would need a specific site inorder to help you out.

XL Baby,

This should work for you, watch out for word wrap though:

-just change the variable to your username and password

Regards,
David Miller

Function Get_Online_Report()
Dim ie, Form As Object, _
sUserName, sPassword As String

Set ie = CreateObject("InternetExplorer.Application")

sUserName = "Your User Name"
sPassword = "Your Password"

With ie
.Visible = True
.navigate "https://investing.schwab.com/trading/start?kc=y"
Do Until .readystate = 4
DoEvents
Loop
On Error Resume Next
With .Document.Forms
For I = 0 To .Length - 1
If .Item(I).Name = "SignonForm" Then
Set Form = .Item(I)
End If
Next
With Form
For I = 0 To .Length - 1
With .Item(I)
Select Case .Name
Case "SignonAccountNumber"
.Value = sUserName
Case "SignonPassword"
.Value = sPassword
End Select
End With
Next
.Submit
End With
End With
End With
Set ie = Nothing
End Function

In many cases, I've found that manually doing a Web Query creates a
cookie that allows me to download data from the site after that with
little or no problem. I have an add-in that extracts financial data
from web pages, so I've had to use it on a few secure pages. But once
the cookie was established via the Web Query, all of the web pages
from that site have been available.

On May 11, 4:14 am, XL Baby wrote:

I have the same problem as you were discussing below, downloading my account
data from my secure broker's site. I tried both Web Query and Macro and got
stopped right at the login step. Can I use your script and how do I run it in
my case, since I have to login then go to another page and download from that
page data that is only contained in one frame?