Getting a Web query macro to log into the website and then copy the data.

[bracken752]

Hey all,

Thank you for all your help so far however I have encountered my next mountain to climb and hoping for a little more help.

I am using the macro shown below to web query a my company's 3rd party programmer website, which I need to pull some information from and report to the 'higher ups' within the company.

As the page is 'dymanic' and I will need to pull data from any of 1-330 different people the website URL is created by a combination of Vlookup (To get the ID) and =CONCATENATE to put that Vlookup data into the URL for example:

http://www.removed.net/RestrictedPages/UserDetail.aspx?userID=46

This is made by using the following combination of excel functions

=CONCATENATE("http://www.removed.net/RestrictedPages/UserDetail.aspx?userID=",VLOOKUP(U1,A28:C43,3,))

What I need is for when the web query goes to this site, it automatically logs in, when I record a macro to go to the website directly (Instead of using the concatenated code) it works after only one login (So I can manually log in and the rest of the macros work, but this doesn't seem to work when using the "Copy" macro shown in the code at the bottom of this post).

So I need to find a way for it to login automatically, I have scanned the first 10 pages of Google search for "using vba to login to website web query" and due to "www.knowexcel.com"being tagged as Malware :/.

From what I have found is that I need the information from the page source which is shown below but I don't know how to insert that into my macro (At the bottom of this post).

Any help would be much appreciated and I would love you (all) long time.

Page Source

[TABLE]
<tbody>[TR]
[TD="class: webkit-line-content"]label for="MainContent_LoginUser_UserName" id="MainContent_LoginUser_UserNameLabel">Username:</label>[/TD]
[/TR]
[TR]
[TD="class: webkit-line-number"][/TD]
[TD="class: webkit-line-content"]                        <nput name="ctl00$MainContent$LoginUser$UserName" type="text" id="MainContent_LoginUser_UserName" class="textEntry" />[/TD]
[/TR]
[TR]
[TD="class: webkit-line-number"][/TD]
[TD="class: webkit-line-content"]span id="MainContent_LoginUser_UserNameRequired" title="User Name is required." class="failureNotification" style="visibility:hidden;">*[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE]
<tbody>[TR]
[TD="class: webkit-line-content"]label for="MainContent_LoginUser_Password" id="MainContent_LoginUser_PasswordLabel">Password:</label>[/TD]
[/TR]
[TR]
[TD="class: webkit-line-number"][/TD]
[TD="class: webkit-line-content"]input name="ctl00$MainContent$LoginUser$Password" type="password" id="MainContent_LoginUser_Password" class="passwordEntry" />[/TD]
[/TR]
[TR]
[TD="class: webkit-line-number"][/TD]
[TD="class: webkit-line-content"]span id="MainContent_LoginUser_PasswordRequired" title="Password is required." class="failureNotification" style="visibility:hidden;">*

[/TD]
[/TR]
</tbody>[/TABLE]


My current macro

ActiveWorkbook.Worksheets.AddSelection.Copy
Application.CutCopyMode = False
Range("S12").Select
With ActiveSheet.QueryTables.Add(Connection:="URL;" & Sheets("Sheet1").Range("S12").Value, Destination:=Range("A1"))
.Name = "names"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

 

[bracken752]

Still cant get it to work, not sure what is going wrong. Cant figure how I can do a 'If' statement to say "If you get login screen use these details and then carry on".

Any ideas?

JB

 

[bracken752]

any ideas guys and gals?

 

[John_w]

I've never seen any VBA code which makes a web query log in automatically to a properly secure site. If the web site allows the login username and password in the URL, then the code at Excel Help for Automating A Web Query Login To Download Data would work, but I wouldn't call that secure because the login details are in plain text and therefore most web sites don't support it.

If the web page has a 'remember my login' or similar tick box and you tick this box and set the site as Internet Explorer's home page then that might allow the web query to log in automatically.

 

[bracken752]

I've never seen any VBA code which makes a web query log in automatically to a properly secure site. If the web site allows the login username and password in the URL, then the code at Excel Help for Automating A Web Query Login To Download Data would work, but I wouldn't call that secure because the login details are in plain text and therefore most web sites don't support it.

If the web page has a 'remember my login' or similar tick box and you tick this box and set the site as Internet Explorer's home page then that might allow the web query to log in automatically.

You sir are a god send, that worked perfectly. I think people in the office thought I'd finally cracked when I started jumping up and down lol.

I have another problem with getting the macro to loop and increase the cell D28 and then on the next loop around it goes to D29 instead, I will create a new thread to make it a little tidier.

Thank you very much though John <3 <3 <3