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.

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---
--
RyGuy


" wrote:

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.

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 "****ible" 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.

On Sat, 14 Mar 2009 09:56:00 -0700, ryguy7272
wrote:

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---

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 = "
msg.To = "
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")

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 userid:password -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....er/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.

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

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.

On Sat, 14 Mar 2009 21:33:52 -0700 (PDT), "
wrote:

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 = "
msg.To = "
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")

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.

On Mar 14, 10:12*am, wrote:
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