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

 

[dave3009]

You will need to look at the source HTML code for those pages to find the reference for those input boxes. From there it's plain sailing can you find out what these inputboxes are called or give us a link to the page so we can find out on your behalf

 

[waldymar]

The site is: www.wifa.ru
On the left side you can see the login and password fields. I would appreciate if you could teach me that I can do it by myself. I'm using Office 2003.

 

[dave3009]

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

 

[waldymar]

Thanks a lot!
A couple of questions:
Does it matter that I use another web browser than Internet Explorer?
Is this a standard code for any web-site I would like to import into excel?
What does this mean - "Do Until .ReadyState = 4"?

 

[dave3009]

I'm not sure how to make this work with any other browser TBH. I use Firefox personally but I find if you want to get Microsoft applications to work together you're best sticking with other Microsoft apps. Please google using your prefered browser with VBA

ReadyState 4 bascially means 'idle' i.e. all html, javascript, css, advertising etc etc is loaded and IE is expecting your input. This may differ if you use your preferred browser

If you want to import data you best look at Excel's web connection wizard, this is IE based but it will happily download any tables etc you want. Some of the other board members might show you how you could run a SQL query (or something) if you are accessing a database you have permission to use

 

[taigovinda]

Hey Dave,

Your code worked for the web page I wanted to use it on, and I was able to find the names for the login and pass. But this:

        .document.forms(0).submit

...seems to do something other than pressing the login button. I tried changing the 0 to a 1 but no dice - then it errors. Any tips on how I can press the login button, or search the html source to find its name and then use that name?

Thanks,
Tai

 

[dave3009]

Are there any other forms on your page?

 

[taigovinda]

I don't know what that means... can I pm you a pair of sites please? Thanks.

 

[dave3009]

Sure thing, just PM them over I'll check them out for you