Getting Excel to Logon to a Website

[Guest]

I need to automate a process inwhich Excel does a web query to get data
from a web site. However, before getting the data I must be logged on. If
I logon via a web browser before running my macro, the site still does not
recognize me as logged on when Excel tries to access it.

I can use web querys from Excel (not VBA) to get a logon screen, and
manually enter the user Id/Password info. Once that is done, my macro runs
fine.

Is there any way I can automate the logon process? That is, a macro gets
the logon screen and neters and submits the user id/password info?

Many thanks.

 

[ryguy7272]

You can try this:
Module1:
Sub kk()
ActiveWorkbook.FollowHyperlink Address:="
http://xxxxxyour-web-site-herexxxxx", NewWindow:=False, AddHistory:=True
Application.WindowState = xlNormal
' Application.SendKeys ("{%TAB}")
Module2.Macro1
Application.SendKeys ("%{TAB}")
Application.SendKeys (Cells(1, 2).Value)
Application.SendKeys ("{TAB}")
Application.SendKeys (Cells(2, 2).Value)
Application.SendKeys ("~")
End Sub

Module2:
Sub Macro1()
Dim r As Integer
For r = 1 To 10
Cells(2, 4).Value = Time
Cells(3, 4).Value = r
Application.Wait (Now + TimeValue("00:00:01"))
Next r
End Sub

Put your username in Cell B1 and your password in Cell B2.

That MIGHT work. I had a similar issue recently. The import process never
worked; never really got it straightened out,...but I did successfully log
into the web site using the code above.

Regards,
Ryan---

 

[oitbso]

I need to automate a process inwhich Excel does a web query to get data
from a web site.  However, before getting the data I must be logged on. If
I logon via a web browser before running my macro, the site still does not
recognize me as logged on when Excel tries to access it.

I can use web querys from Excel (not VBA) to get a logon screen, and
manually enter the user Id/Password info.  Once that is done, my macro runs
fine.  

Is there any way I can automate the logon process?  That is, a macro  gets
the logon screen and neters and submits the user id/password info?

Many thanks.

One way to accomplish this is as follows...Ron

' Open IE and go to the desired web page
Set ie = CreateObject("InternetExplorer.Application")

With ie
.Visible = True
.Navigate "your url"
.Top = 50
.Left = 530
.Height = 400
.Width = 400

' Loop until the page is fully loaded
Do Until Not ie.Busy And ie.ReadyState = 4
DoEvents
Loop

' Make the desired selections on the Login web page and click the
submit button

You'll need to check the source code for your web page and determine
the correct name for both the username and password and then
substitute the correct expressions in the "Set ipf" lines. The enter
your username and pw in the "ipf.value" lines. Finally, depending if
there is a button to click or form to submit, you'll need to submit
the info. I'd need the url to say exactly how to do this, but you can
most likely figure it out from the source code

Set ipf = ie.document.all.Item("userid")
ipf.Value = "rhlevin"

Set ipf = ie.document.all.Item("Password")
ipf.Value = "rhlev926"

ie.document.all.Item("frmlogin").submit

' Loop until the page is fully loaded
Do Until Not ie.Busy And ie.ReadyState = 4
DoEvents
Loop
End With

 

[Guest]

Thanks for your reply. I tried the code and Excel starts a instance of my
default browser (FireFox). Then when using application.SendKeys, it pastes
the characters into my macro's code rather than into the browser.

A second "pissible" problem is that if I start a brwoser and enter the
logoninfo, then the site understands that I am logged in when I access it
from the browser but it does not think I am logged in when I try to use it
from Excel.

 

[gimme_this_gimme_that]

Study this.

Sub Main(logon,password)
WScript.Sleep(1000)
Set IE = CreateObject("InternetExplorer.Application")
WScript.Sleep(1000)
IE.Visible = False
title = Navigate("http://www.somesite.com")
If DoMatch(title,"Login") Then
Set doc = IE.Document
Set form = doc.forms("login_form")
form.elements("logon").value = logon
form.elements("password").value = password
form.submit
Do while IE.Busy
Loop
title = IE.Document.title
If DoMatch(title,"Home") Then
' user logged in normally
PeruseHome
Navigate ("http://www.somesite.com/LOGOUT/")
Else
' user can't log in
Notify()
End If
Else
' user is probably already logged in and is on the
home page
If DoMatch(title,"Home") Then
PeruseHome
'logout
Navigate ("http://www.somesite.com/LOGOUT/")
End If
End If
IE.Quit
Set IE = Nothing
End Sub
Sub WriteToFile(fileName,text)
Set wFSO = CreateObject("Scripting.FileSystemObject")
Set wFile = wFSO.OpenTextFile(fileName, 2 , True)
wFile.WriteLine text
wFile.Close
End Sub
Function DoMatch(strInput, strPattern)
Dim re : Set re = New RegExp
re.IgnoreCase = True
re.Global = True
re.Pattern = strPattern
DoMatch = re.Test(strInput)
re.Pattern = ""
End Function
Function Navigate(url)
IE.Navigate url
Do while IE.Busy
Loop
WScript.Sleep(2000)
Navigate = IE.Document.title
End Function
Sub PeruseHome
text = doc.body.innerText
If DoMatch(text,"Navigation") OR DoMatch(text,"error has
occurred")
Then
'Do Nothing when there is an error
Else
'Write file when things are okay
WriteToFile "C:\_sps.txt",text
End If
End Sub
Sub Notify()
Set msg = CreateObject("CDO.Message")
msg.Subject = "Monitoring username/password on
sps.accountemps.com is
failing"
msg.From = "(e-mail address removed)"
msg.To = "(e-mail address removed)"
msg.TextBody = "This message was sent from an automated
script."
msg.Configuration.Fields.Item ("http://schemas.microsoft.com/
cdo/
configuration/sendusing") = 2
msg.Configuration.Fields.Item ("http://schemas.microsoft.com/
cdo/
configuration/smtpserver") = "hqpwtload.corp.rhalf.com"
msg.Configuration.Fields.Item ("http://schemas.microsoft.com/
cdo/
configuration/smtpserverport") = 25
msg.Configuration.Fields.Update
msg.Send
End Sub
Call Main("someusername","somepassword")

 

[Spiggy Topes]

Another option might be to use cURL. I've used this successfully from
Excel to upload files to a password protected site. I used Shell to
run cURL, and waited for it to complete before continuing. The cURL
website (http://curl.haxx.se/) was a pain to navigate at the time, but
it may have become easier. A good starting point is http://curl.haxx.se/libcurl/vb/.

The cURL command line looks something like:

lShell = Shell_And_Wait("C:\Installs\cURL\Binaries
\curl-7.16.0\curl.exe -u useridassword -F action=upload -F
uploaded_file=@""" & _
EXPORT_DIR & "\" & strArchiveDate & "\Your_File.zip""" &
_
" -F submit=Upload http://www.destination_web_site/index.php",
240)

The ShellAndWait function was picked up from
http://www.visualbasic.happycodings.com/Other/code38.html. Nice thing
is, I could use it to launch WinZip prior to launching cURL to cut
down on the file size. There's a perl script runs at the other end to
unzip and relocate received content.

 

[Guest]

Thanks for the reply, it will be very helpful to me in outhe situations.
However, when I use a web query, the website in question does not consider
me to be logged on unless I have logged on via another web query first.
Logging on via a web browser does not work.

No one has yet suggested a way to automate logging on via a web query. I
can do it manually and that works, but I would like to automate the
process.

 

[gimme_this_gimme_that]

The useridassword in that curl statement is a username/password for
a proxy server - not for a website.

 

[Spiggy Topes]

The useridassword in that curl statement is a username/password for
a proxy server - not for a website.

No, according to the documentation, that's -U, unless there's been a
change between versions. Here's the relevant section of the "man
page":

-u/--user <userassword>
Specify user and password to use for server
authentication.
Overrides -n/--netrc and --netrc-optional.

If you use an SSPI-enabled curl binary and do NTLM
autentica-
tion, you can force curl to pick up the user name and
password
from your environment by simply specifying a single
colon with
this option: "-u :".

If this option is used several times, the last one will
be used.

-U/--proxy-user <userassword>
Specify user and password to use for proxy
authentication.

If you use an SSPI-enabled curl binary and do NTLM
autentica-
tion, you can force curl to pick up the user name and
password
from your environment by simply specifying a single
colon with
this option: "-U :".

If this option is used several times, the last one will
be used.

 

[gimme_this_gimme_that]

Don't hold your breath for an answer....

You haven't told us about the web site.

We don't know if you're having to deal with a username and password on
a form and then a submit - or whether you need to provide username/
password information into an Apache .htaccess popup. Or whether, like
Spiggy is thinking, you want to pass along your NT credentials.

You need to provide more information about what you seen on when you
log in or your just wasting people's time.

Aside from that - your not knowing about different kinds of log ins -
and your preoccupation with using WebQuery - it kind of suggests that
if someone provided a solution to you - you don't have the technical
chops to take a tip and translate it into a solution.

It's amazing that you even have a job.

 

[Guest]

Don't hold your breath for an answer....

You haven't told us about the web site.

We don't know if you're having to deal with a username and password on
a form and then a submit - or whether you need to provide username/
password information into an Apache .htaccess popup. Or whether, like
Spiggy is thinking, you want to pass along your NT credentials.

You need to provide more information about what you seen on when you
log in or your just wasting people's time.

Aside from that - your not knowing about different kinds of log ins -
and your preoccupation with using WebQuery - it kind of suggests that
if someone provided a solution to you - you don't have the technical
chops to take a tip and translate it into a solution.

It's amazing that you even have a job.

Writing stuff in VBA is not something I do for a living, but I have written
VBA code that actually trades stocks on line successfully & others that
manipulate a lot of real time data. My coding is normally in real
languages like C++ and assembly.

I have written compilers used by advanced aero-space researchers, large
chunks of operating systems including pagers, file systems, etc. I was on
the software design team for what was, at the time, the world's largest
fastest computer (think Seymour Cray). I am the only person I know of
who has been decorated (medal hanging from a ribbon) by the US goverment
for software design. (I'm sure there are others - I just do not know about
them).

This particular website works when I logon via a query and does not when I
log on via a browser and they try to use a query to get the data I need. I
am trying to write some code to help a friend and I was hoping someone
would know how to do this part of it without my having to spend a lot of
time finding out what is really going on. My work on the project is now
complete except for this small part of getting logged on which he is doing
manually via a query. My "preoccupation" with doing it via a query is
simple because that is what works. I'm sure I could write some Jaca code
to do this & call it from VBA, but I really don't want to learn to do that.

 

[Guest]

Oops - can't type too well. Jaca=Java

 

[Don Guillett]

gimme should be somewhat embarrassed and perhaps it should learn that it is
not nice to degrade people so personally in a group like this. An apology
should be issued.

 

[gimme_this_gimme_that]

Why should I be embarassed? I posted a tip. Two other people posted as
well.

I explained that he will never get an answer to his question because
he didn't provide information about the log in mechanism.

We still don't know if there is a username/password on a form, or an
credential popup, or if he is passing along his NT credentials.

Don, you're the "MVP* and you haven't provided a tip. I did. Where's
your tip?

Guess what? The user ignored every tip he received.

Not only that, but the tool he's using WebQuery isn't the tool for the
job.

 

[Defender384]

Why should I be embarassed? I posted a tip. Two other people posted as
well.

In addition you went to great lengths to denigrate the gentleman or lady
asking the question, thus exposing your character rather than the attackee.

That's why you should be embarressed and you already knew it.

 

[gimme_this_gimme_that]

In addition you went to great lengths to denigrate the gentleman or lady
asking the question, thus exposing your character rather than the attackee.

You're fabricating. I was terse but not insulting. I provided a useful
tip - provided that he was logging into a site using a form with a
username and a password.

What I told the OP was true. He wasn't going to get an answer to his
question and he needed to make clarifications.

I provided the OP to favors.

And you. And Don Guillet. Both of you guys bent on playing microsoft
web cop. Neither of you bothered to provide a tip.

What I said was right on. No one is going to give that guy a tip on
how to do that WebQuery thing he wants to do.