Using Script to download from secure site

[Greg@food family]

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

 

[Dave Miller]

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

 

[XL Baby]

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.

 

[Dave Miller]

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

 

[XL Baby]

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

 

[Dave Miller]

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

 

[XL Baby]

Thanks, Dave.
Now, how do I use this function? By itself, as part of a WebQuery or inside
of a Macro? Also, what is the problem with wordwrap?

 

[Dave Miller]

All this function does is get you through the door. I can not go any
further with this because I do not have access to this site.

To use:
-Paste the function into a module
-Then call it from another procedure:

Sub OpenWebSite()
Call Get_Online_Report
End Sub


go back to the worksheet and press Alt + F8 then select OpenWebSite
and click Run.


Regards,
David Miller

 

[XL Baby]

Dave,

I tried to implement your suggestion above, but I keep getting a "Syntax
Error" as soon as I run it. Can you tell me what is going on please? Tks a
lot.

 

[Randy Harmelink]

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.

 

[Dave Miller]

If you post the entire code you are trying to run I will be able to
help you.

Regards,
Dave

 

[edgar_0164]

If you post the entire code you are trying to run I will be able to
help you.

Regards,
Dave

Hi my name is edgar. I've got the same question or looks alike:
I'm opening a intranet webpage as a workbook. With the command:
woorkbooks.open("http//intranet.net/id=XX")
In that very moment a Dialog box prompts me asking for UserId and
Password, I provide them and the workbook is created ok.
Question: How can I insert code to provide this information to the
dialog box? and avoid the manual part, cause I have some pages to open
this way.

I was trying the function written in lines before working with an ie
object.
Now I can open the ie object webpage, even without provide UserId and
password
I'm thinking I could get the specific information I need from this ie
Object, But,
One thing is that I don't know how the ie object methods and
properties
work, basically cause I don't have it referenced in Excel Library, I
don't
see a file or dll for that in the references library. For example to
use a
find method in the ie object and then to refer to the field/cell next
to the
parameter that I'm requesting with the find method.

In a sheet i could do it, but again when i execute
workbooks.open("http//aaaa/.bbb.com"), in that moment and part of the
code it
prompts me with the logon dialog box. I guess Excel got a cookie like
Randy
mentioned before cause now I see the values already provided into the
form,
but the i have the Enter command. I mean do not have an idea how to
work it
around, even when I open the webpage with the ie object function.

But If you have any idea or know who could help on this, I mean Very
much Thank you really.

 

[Randy Harmelink]

You need to submit the form through the IE object, typically using a
"click" method. There are many examples on the Internet (I had to
find them using Google when I first learned how to do it). Here is a
short example I posted on the newsgroup before:

http://groups.google.com/group/micr..._frm/thread/a2bf7e5dc3266558/c94359a6cdfd76ef

 

[Dave Miller]

Edgar,

In the VBE you need to use the Locals window to view properties of the
ie object. Step through your code until you have the ie object
initiated. Go to the locals window start drilling down from the ie
object --> Document --> Forms --> Item 1 If this form item has a value
in the id property, it can be referenced directly in code, e.g. Set
oForm = ie.document.all.item("FormID")

If not you will need to loop through all the forms to identify it by
name;

with ie.document.forms
for i = 0 to .length
if .item(i).name like "Your Form Name"
set oForm = .item(i)
end if
next
end with


The locals window will give you a treeview of any objects properties,
learn to use it and you will be amazed by how much easier programming
is.

Regards,
David Miller