Home > Forum Home > Automating Data Analysis Tasks > Web query Share

Web query

Excel Help for Web Query in Automating Data Analysis Tasks


Forum TopicPost Reply Login

Web Query

Rate this:
(3/5 from 1 vote)
Confused Hi everyone
New to this page, but thank god i found it :)
I have a web query which i found in here, which is af webquery with login. I just can figure out to use it to my desired webpage. But i might think the webpage is blocking it or something. I've tried with "password" insteed of "&password as well. im sry i can list the login details, but it is my copmpanies private information
VBA Code:
 
Sub Login_WebQuery()
Dim MyPost As String
Const MyUrl As String = "[http://rigpoint2.ods-petrodata.com/savedquery.php?op=runquery&savedqueryid=19577&skin=rpv2b&prefix]="
Const PostUser As String = "login=******" 'Change user name here
Const PostPassword As String = "&password=******" 'Change password here
   
MyPost = PostUser & PostPassword
   
With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;" & MyUrl, Destination:=Cells(1, 1))
    .PostText = MyPost
    .BackgroundQuery = True
    .TablesOnlyFromHTML = True
    .Refresh BackgroundQuery:=False
    .SaveData = True
End With
   
End Sub

 
Hope anyone can help! best regards Caspar

 CGN
 Posted by on
 
Replies - Displaying 1 to 7 of 7Order Replies By: Most Recent | Chronological | Highest Rated
Oops
Rate this:
(3/5 from 1 vote)
You may find, by looking at the source HTML of the login page, that there are additional variables required for the login.  These can be hidden form variables with values or sometimes also session variables for which we must first extract using a preceeding web query to open the session.
 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
Thank you for your answer. Whew can I find these in the HTML code?:/ seems tricky. Are you able too see them? Again thx for your answer!!
 CGN
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
This is from the HTML code:
HTML for login:
 <INPUT tabIndex=1 id=login style="FONT-SIZE: 8pt; FONT-FAMILY: Arial; WIDTH: 140px; MARGIN-LEFT: 10px" size=14 name=login>
HTML for password:
<INPUT tabIndex=2 id=password style="FONT-SIZE: 8pt; FONT-FAMILY: Arial; WIDTH: 140px; MARGIN-LEFT: 0px; MARGIN-TOP: 4px" size=14 type=password value="" name=password>
HTML for submit button:
<INPUT id=submitBtn class=btnLogin style="WIDTH: 60px; MARGIN-LEFT: 70px; MARGIN-TOP: 4px" type=submit value=Login name=submitBtn>

I just can really figure out which else can be used? Can anyone help?:)
 CGN
 Posted by on
Sad
Rate this:
(3/5 from 1 vote)
If these are the only input tags in the form HTML tag then there is no reason why the web query does not log in. Also make sure that the URL is the target page from the form tag and not the login page. If you get an error message from the VBA, what is it and on what line?
 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
The problem is that it seems like the query makes a query of the loginpage, so i think it has to some how, press the submit button, or am i mistaken?:)

I dont get any error message, the query just takes the login page, and not the desired target page.. 
 CGN
 Posted by on
Fedup
Rate this:
(3/5 from 1 vote)
From the URL in you code, when we look at the source, the target page seems to be https://login.ods-petrodata.com/login.php. Also, there are two hidden fields required: site and uri. The uri is likely to be a URL encoded version of the original URL for redirect after login.
 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
Damn, seems a bit more complicated than normally! I made a VBA code that signs in and takes the HTML table. It would just be much easyer to do it with a query, and faster! and easyer to work with in pivot tables afterwards. Are you able to get the query to go with the login and password details? It seems like (using httpfox) that it is navigating to this webpage (MyUrl), which is a bit different, but still cant get it to work, cause it goes to login page. Anyway to skip this? Sub Login_WebQuery() Dim MyPost As String Const MyUrl As String = "http://rigpoint2.ods-petrodata.com/currentactivity.php?op=runquery&savedqueryid=19577&skin=rpv2b&prefix=" Const PostUser As String = "[email protected]" 'Change user name here Const PostPassword As String = "password=Test1234" 'Change password here MyPost = PostUser & PostPassword With ActiveSheet.QueryTables.Add(Connection:= _ "URL;" & MyUrl, Destination:=Cells(1, 1)) .PostText = MyPost .BackgroundQuery = True .TablesOnlyFromHTML = True .Refresh BackgroundQuery:=False .SaveData = True End With End Sub
 CGN
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for Web query in the