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]

I wouldn't recommend posting your password and username so I've stripped it from my code and will ask a mod to remove it from yours.

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 = "ldfsdfh"
        .document.all.Item("password").Value = "lkjgshdgsh"
        .document.forms(0).submit
    End With

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.paris-turf.com/pid1-paris-turf-la-base-numero-1-du-turf.-tierce-quarte-quinte-pmu-pmh.html" _
        , Destination:=Range("$A$1"))
        .Name = _
        "pid1-paris-turf-la-base-numero-1-du-turf.-tierce-quarte-quinte-pmu-pmh"
        .FieldNames = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .HasAutoFormat = True
        .TablesOnlyFromHTML = True
        .AdjustColumnWidth = True
        .Refresh BackgroundQuery:=False
    End With

This give me the desired result, no * asterisks

 

[sandoz]

no damage with showing login and pw.
Sorry but the url you used does not require a login to display its data !

The url to use is
www.paris-turf.com/reunion/pid57-course.html?table=pronostics_des_pros&idcourse=798992
and it does not work in your code.

try it first directly with the logwin and pw I gave to see what the data are
( an array of numbers)

 

[sandoz]

So, it can't be done?

 

[Alex0128]

Were you all ever able to figure this out?

I've created an extensive personal finance workbook and it would be EXTREMELY helpful to have some data automatically pulled from my mint.com account each time I open the file or refresh.

Thanks!

 

[TastyWheat]

I believe I may have found a solution to this problem.

Here's my code, I'll explain it after the quote:

     [COLOR=#008000]' Connection URL should be for the login page; WSheet is a Worksheet object in case that's not clear[/COLOR]
     With WSheet.QueryTables.Add(Connection:="URL;http://www.mysite.com/login.php", Destination:=WSheet.Range("$A$1"))
          .PostText = "username=johndoe&password=secret"

          [COLOR=#008000]' Most of the properties below are probably unnecessary to the login procedure, but this is the code I used[/COLOR]
          .Name = "MySite"
          .RowNumbers = False
          .FillAdjacentFormulas = False
          .PreserveFormatting = True
          .RefreshOnFileOpen = False
          .BackgroundQuery = True
          .RefreshStyle = xlOverwriteCells
          .SavePassword = False
          .SaveData = True
          .AdjustColumnWidth = True
          .RefreshPeriod = 0
          .WebSelectionType = xlSpecifiedTables
          .WebFormatting = xlWebFormattingNone
          .WebPreFormattedTextToColumns = True
          .WebConsecutiveDelimitersAsOne = True
          .WebSingleBlockTextImport = False
          .WebDisableDateRecognition = False
          .WebDisableRedirections = False
          .Refresh BackgroundQuery:=False
            
          [COLOR=#008000]' This is where the REAL web query information goes[/COLOR]
          .Connection = "URL;http://www.mysite.com/statistics.php"
          .PostText = "enddate=2011/01/01&startdate=2011/12/31"
          .WebTables = "2"
          .Refresh BackgroundQuery:=False
     End With

The first part is for logging into the site. Excel saves the login credentials on a per session basis from what I can tell (i.e. as long as the file is open you won't need to relogin). You'll see a similar behavior if you use the web query wizard to login, then run your web query code. My code simply removes the requirement to manually login. If you prefer users to login interactively though you can always create a userform and use their credentials instead of hard-coding it.

I hope this helps someone!

 

[TastyWheat]

One downside I noticed about this code is that there may not be an easy way to determine if the login was successful. If the login page has a table that can be grabbed you could analyze those results or analyze the results of the actual data grab (QueryTable.ResultRange may be useful in that case).

I couldn't find a way to elegantly integrate a web browser object into my spreadsheet but I'm pretty sure a user can login through that and the credentials would be saved for a seperate web query.

 

[IonutC]

Hi guys,

Hope you all are fine.

I have the same issue. I have multi accounts to use and to extract data as well.

The code is running smoothly only for one account as I had to add the login details for only one account.

Can someone please help me with the HTML tags so I can write the code for each account i have.

My code is:

 With ActiveSheet.QueryTables.Add(Connection:= _        "URL;https://dd.worldwatchplus.com/transummary", Destination:=Range("$A$1"))
       
        .Name = "transummary"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "3"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

the page i have to use is: https://dd.worldwatchplus.com/login


I need your help so I can add the info for the login details into my code.

This is much appreciated!

thank you in advance,
Ionut

 

[dave3009]

Try this, as I don't hold valid credentials I am unable to test fully. As an alternative you may find it easier to login via Excels own browser this will retain your login credentials until they expire this way you do not need to process the login to do your data query.

Sub Ionut()
Set ie = CreateObject("InternetExplorer.Application")
    With ie
        .Visible = True
        .Navigate "https://dd.worldwatchplus.com/login"
            Do Until .ReadyState = 4
                DoEvents
            Loop
        .document.all.Item("fake-authid").Value = "Your.Entity" ' Entity
        .document.all.Item("fake-username").Value = "Your.Username" ' Username
        .document.all.Item("fake-password").Value = "Your.Password" ' Password
' ****** You may need to uncomment this, unable to validate ******
'        .document.all.Item("login-authid").Value = "Your.Entity"
'        .document.all.Item("login-username").Value = "Your.Username"
'        .document.all.Item("login-password").Value = "Your.Password"
        .document.forms(0).submit
    End With
With ActiveSheet.QueryTables.Add(Connection:="URL;https://dd.worldwatchplus.com/transummary", Destination:=Range("$A$1"))


        .Name = "transummary"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "3"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[IonutC]

Thank you Dave! Is doing what it has to do, but i think is something with the site as the text boxes have grey letters and dunno why is like this.
Is something from the site we have to add some code so the state is 3 and 4 after the login?

Please see bellow the pic with the issue.

Thank you for your help here!
I really appreciate it!

Thank you,
Ionut

 

[IonutC]