Web Query for password protected web-sites

[waldymar]

hello everyone,

I am trying to import data into Excel from a webpage using the web query function; however, the webpage requires a login and password. I have the required login and password, but I cannot figure out how to make excel automatically input these into the correct fields and login in order to import the data.

Since I am trying to make all of our databases update automatically, I don't want to enter this info everyday when the macro runs.

Any suggestions?
Thanks

Vadim

 

[waldymar]

Dave, some problems appeared:
1. When IE is loading my web-site, there appears a message box asking "do you want IE be ....". How to eliminate any query box?
2. Macro doesn't work when I'm already logged to that web-site. Please, suggest me how to create an if condition?

 

[dave3009]

1. Open IE and when prompted there should be a chack box that says something along the lines of 'Do not show this again'. Check this and click 'No'

2. My code should only log you into your site, once you are in you are in. Would you be accessing some type of database that you might be better connecting directly within Excel.

 

[waldymar]

1. Open IE and when prompted there should be a chack box that says something along the lines of 'Do not show this again'. Check this and click 'No'

Yes, I can do it. But, I give this file to others and eliminate such problem I thought to create a code. There is a similar code in excel: application.displayalerts

 

[dave3009]

I think you will need to provide instruction to your end users I'm afraid.

 

[waldymar]

Ok, I'll instruct them.
Now I see that macro doesn't log me to site. It's inserting my username and password, but then it opens some derivated site of the main one without logging in. Any solutions?

 

[dave3009]

I'm sure the site you cited is very good and you are a genuinely nice person but since I attempted to assist you connect to wifa.ru I have had an inbox full of spam. All with a TLD of .ru surprise surprise.

 

[waldymar]

Dave,
I appologize, but it's not my work at all. Frankly speaking, I have no idea what happened with your inbox.

 

[sandoz]

Try this, I have highlighted the bits I think you need to double check.

Sub WebLogin()
Dim a As String
    Set ie = CreateObject("InternetExplorer.Application")
    With ie
        .Visible = True
        .Navigate "http://www.wifa.ru"
        Do Until .ReadyState = 4
            DoEvents
        Loop
        .document.all.Item("username").Value = "Username"
        .document.all.Item("user_password").Value = "Password"
        .document.forms(0).submit
    End With
End Sub

As I have the same problem with the URL below,

http://www.paris-turf.com/reunion/pid57-course.html?table=pronostics_des_pros&idcourse=797282,

.document.all.item() do not accept strings like username & user_password,
only indexes from 0 to 255,
what gives?

 

[dave3009]

You'd need to change the names to the relevant names for that site, in your case this would work

Sub WebLogin()
Dim a As String
    Set ie = CreateObject("InternetExplorer.Application")
    With ie
        .Visible = True
        .Navigate "http://www.paris-turf.com/dossier-layer/pid251-layer-authentification.html?height=300&width=400&KeepThis=true&TB_iframe=true"
        Do Until .ReadyState = 4
            DoEvents
        Loop
        .document.all.Item("mail").Value = "Username"
        .document.all.Item("password").Value = "Password"
        .document.forms(0).submit
    End With
End Sub

The items in red will need changed to your email address and password respectively

 

[sandoz]

ok, the login and pw are taken into account,
but the data which should display afterwards
are always hidden with stars, as if the login were ignored. below the code :

Sub WebLogin()
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
.Navigate "http://www.paris-turf.com/dossier-layer/pid251-layer-authentification.html?height=300&width=400&KeepThis=true&TB_iframe=true"
Do Until .ReadyState = 4
DoEvents
Loop
.document.all.Item("mail").Value = "username"
.document.all.Item("password").Value = "password"
.document.forms(0).submit
End With

varconnection = "http://www.paris-turf.com/reunion/pid57-course.html?table=pronostics_des_pros&idcourse=798992#course"

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & varconnection, Destination:=Range("A1"))
.FieldNames = False
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = False
.SaveData = True
End With

end sub