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]

The webpage is built in such a way that it is difficult to just fake a login, unfortunately in the world we live in people are ever smart and build applications that attempt to bypass USER authentication. So try uncommenting the second batch of .document.all.Item and see if that works. Remember you will need to put in YOUR legitimate credentials. Failing that login via Excels browser and run your query.

 

[IonutC]

Hi Dave,

Thank you for your prompt reply.

I did uncomented that part and it is the same thing.
I used my login details and they are in grey. I used those for you so I can show you how is it looking.

Thank you,
Ionut

 

[dave3009]

OK,

It looks like that website has employed some reasonable tactics to stop automated logins, that seems fair as they may want users to physically authenticate themselves. This is why we have things like SMS and captcha verification (and others). From my own personal point of view I don't want to try and circumvent a system, especially as someone has gone to such an extent to prevent it...it just wouldn't be right. Perhaps someone else will be able to assist you further, sorry.

Best wishes

Dave

 

[IonutC]

OK,

It looks like that website has employed some reasonable tactics to stop automated logins, that seems fair as they may want users to physically authenticate themselves. This is why we have things like SMS and captcha verification (and others). From my own personal point of view I don't want to try and circumvent a system, especially as someone has gone to such an extent to prevent it...it just wouldn't be right. Perhaps someone else will be able to assist you further, sorry.

Best wishes

Dave

Thank you!

 

[IonutC]

Hi Dave,

I changed the code on the uncommneted part and now the macro is working is taking me to the right page, but now the query is not working.
It is like the macro is saved on a cache and requires me login id and is saying that "
Your session expired due to inactivity.
Please Sign In again."

It is like excel does not take into consideration the login details at all ...

this is the full code:

[/COLOR][COLOR=#333333]Sub test()[/COLOR]
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Dim a As String
Dim ie As Object
Dim ieDoc As Object
Dim clip As Object

Set ie = CreateObject("InternetExplorer.Application")
    With ie
        .Visible = True
        .Navigate "https://dd.com/login"
            Do Until .ReadyState = 4
                DoEvents
            Loop
        .Document.all.Item("fake-authid").Value = "Account"
        .Document.all.Item("fake-username").Value = " Username" 
        .Document.all.Item("fake-password").Value = "Password"

        .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 ie
        .Visible = True
        .Navigate "https://dd.s.com/transummary"
            Do Until .ReadyState = 4
                DoEvents
            Loop
 End With

          
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://dd./transummary", Destination:=Range("$A$1"))
      
        .Name = "transummary_1"
        .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
    
ie.Quit
Set ie = Nothing

 </code>[COLOR=#333333]End Sub[/COLOR][COLOR=#333333]

If I record a macro via macro recorder and use that code to my Sub the macro will work properly until I close the macro, then despite i use login details it is like the vba does not take into consideration the credentials I used..

Thank you,
Ionut

 

[dave3009]

Once you are logged in and have created the query you should only have to refresh. On Data tab click Refresh.

 

[IonutC]

Hi,


I did, but it seems that does not take into consideration the fact that our macro has the login details in it.

Is odd.

Kr,
Ionut

 

[dave3009]

I think I've already mentioned that it might be better to login first via the Excel browser.

Data | From Web | navigate to page, then login, then close query window | Refresh query as mentioned in #36. You session/cookie will be stored differently depending on your browser. For example if you are logged out of a website on all your installed browsers, you then login to that website using one of them, you are not logged in on the other.

 

[IonutC]

ok! so I caN't do that while using the login details into the query.

Thank you man!

Have a great weekend,
Ionut

 

[mavarcho]

Hello Dave,

I have similar issue, I am struggling to adjust your config to my requirement.
I want VBA to access web page and enter automatically username and password BUT there is NO id in html.

web page config:
<

td class="login_fields"><input class="login_name FormField" type="text" name="j_username" size="15" tabindex=1 autocomplete='off' title="Login Name"/></td

>



<


td class="login_fields"><input class="login_password FormField" type="password" name="j_password" tabindex=2 size="15" autocomplete='off' onkeypress="passwordIn(event, this)" title="Login Password"/></td

>



<


td class="login_button_field"><a class="button FormButton" href="#" name="loginbutton" *******="document.LoginForm.submit();" tabindex=4 title="Log In"><span>Log In</span></a></td>




<td class="login_button_field"></td>



many thanks

Marek