Solved

MS Excel Web Query, using existing IE instead of opening a new instance of IE?

Posted on 2011-02-17
7
1,377 Views
Last Modified: 2012-05-11
With Microsoft Excel 2003, using the "Web Query" feature, I record a macro, put the URL of the website into the New Web Query and that opens a new instance of Internet Explorer.  Using the Web Query, I go to a specific table within the tables on the web page and select the table that I want to get the data from and the Web Query copies the data and pastes it into Excel.  The website that I am getting the data from requires a login and password.  I wrote some VBA code to send the username and password and that works at first, however, if I use this web query several times in one day and after using the Web Query several times, I am logged into that website many times and I begin getting error messages and the Web Query does not grab any data.

Is it possible for the Web Query to to switch to the existing, already opened and logged into IE and grab that data and paste it into Excel, instead of opening a new browser window every time the query is run?

Or, if that is not possible, is it possible, to run the macro that uses the login and password, but then, after copying the data, have the query automatically logout and close the new instance of IE?
0
Comment
Question by:STP8377
  • 4
  • 3
7 Comments
 
LVL 19

Expert Comment

by:akoster
Comment Utility
Have you tried using the

RefreshPeriod

property of the querytable ?

if you set it to null, automatic updating is disabled, while setting it to a numeric value enables an automatic update every n minutes. (value = 5 => each 5 minutes it will be updated). I am not 100% sure though that this will solve your problems, but you might want to give it a try.

ideally, you would want to save the login/password combination as a cookie and directly open the page with data. If this is not possible, can you add code to log out after the data hes been processed ?
0
 

Author Comment

by:STP8377
Comment Utility
The attached code works to login (assuming that you are not going to the google page url in the code, but are going to a page that requires a login), but I don't know what code can be used to logout.  It's not a matter of refreshing, the data is only grabbed once and then Excel is closed and the next time that the data is gathered from the website the data is put into a new spreadsheet.
I think the workaround that might work best is to find the Excel VBA code to:
a) switch to the active IE browser window,
b) check the page title to be sure it's the correct web page;
c) save the webpage to my hard drive
d) then run the standard Web Query on the saved hard drive page
Sub querywithlogin()

Dim username As String
Dim user_password As String
    Set ie = CreateObject("InternetExplorer.Application")
    With ie
        .Visible = True
            DoEvents
        .Navigate "http://google.com?schedulerType=1&orderBy=companyName&orderDescending=false&pageSize=200"
        
'Do Until ie.readystate = READYSTATE_COMPLETE
        
        Do Until .readystate = 4
            DoEvents
        Loop
Dim myTitle As String
myTitle = ie.document.Title
If myTitle = "Google" Then
MsgBox "logged in"
Else
MsgBox "not logged in"
        .document.all.Item("login").Value = "UserNameHere"
        .document.all.Item("password").Value = "PasswordHere"
        .document.forms(0).submit
        .Navigate "http://google?schedulerType=1&orderBy=companyName&orderDescending=false&pageSize=200"
End If

Open in new window

0
 
LVL 19

Expert Comment

by:akoster
Comment Utility
STP8377,

can you send me the actual page used ?
If the page offers a login form, they should also give you the option to log out.
Almost all websites that use autorisation offer a hyperlink which you can click to log out.

The solution would then be as simple as to add

[...]
.navigate "<log out hyperlink>"
do until .readystate = 4 : doevents : loop
[...]
end with
ie.quit
set ie = nothing
[...]

Open in new window



after you have parsed your data. Then you can close excel.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:STP8377
Comment Utility
I can't send the entire page but the code attachecd is the last part of the page and it has the logout info.  After logging out, can I close IE?  How do I get back to Excel?  I am thinking that it might be better to not automate the entire process and instead tell the user to save the web page to the hard drive and then run the query on the saved page because then we don't need to worry about logging in and out, etc.
<DIV class="box bod" style="BORDER-RIGHT: 0px; BORDER-TOP: 0px; BORDER-LEFT: 0px; BORDER-BOTTOM: 0px">
<TABLE class=denseText style="FONT-SIZE: 9px" cellSpacing=0 cellPadding=0 
width="100%">
  <TBODY>
  <TR>
    <TD width="50%">Date Generated: 2/18/11 8:15:26 <BR>User: <A 
      href="http://xyz/profile/person_summary.do?personId=4187">Doe, John 
      </A>[<A href="http://xyz/jms_ops/logoff.do">logout</A>] <BR>Timezone: <A 
      href="http://xyz/core/preferences/timezone_prompt.do">America/Chicago</A> 
    </TD>
    <TD style="TEXT-ALIGN: right" width="50%"><BR><SPAN title="Served by jmsd">Scheduler version 6.8 
      Build 6809 </SPAN><BR>Copyright © 2006 Acme Corporation 
</TD></TR></TBODY></TABLE></DIV></BODY></HTML>

Open in new window

0
 
LVL 19

Accepted Solution

by:
akoster earned 500 total points
Comment Utility
This will do perfectly nice !

When you have copied the data, nothing is holding you back to log out and close IE. Because excel was in focus before internet explorer was started, when closing IE excel be visible again.
Even better would be to never show IE to the user at all, and inform the progress to the user by making use of the statusbar.

general setup of the code involved should be :

Sub querywithlogin()

dim ie as object
Dim username As String
Dim user_password As String
Dim myTitle As String

    application.statusbar =  "opening internet explorer"
    Set ie = CreateObject("InternetExplorer.Application")
    With ie
        .Visible = false
        DoEvents
        application.statusbar = "browsing to login page"
        .Navigate "http://google.com?schedulerType=1&orderBy=companyName&orderDescending=false&pageSize=200"

        '-- some internet explorer versions prefer readtystate, some prefer busy
        Do Until .readystate = 4
            DoEvents
        Loop
        while .busy
            Doevents
        wend

        application.statusbar =  "logging in"
        myTitle = ie.document.Title
        If myTitle <> "Google" Then
             .document.all.Item("login").Value = "UserNameHere"
             .document.all.Item("password").Value = "PasswordHere"
             .document.forms(0).submit
             .Navigate "http://google?schedulerType=1&orderBy=companyName&orderDescending=false&pageSize=200"
             '-- some internet explorer versions prefer readtystate, some prefer busy
             Do Until .readystate = 4
                 DoEvents
             Loop
             while .busy
                 Doevents
             wend
       End If

[...]
save data from ie to excel
[...]

        application.statusbar =  "logging out"
        .Navigate "http://xyz/jms_ops/logoff.do"
        '-- some internet explorer versions prefer readtystate, some prefer busy
        Do Until .readystate = 4
            DoEvents
        Loop
        while .busy
            Doevents
        wend

        application.statusbar = "closing internet explorer"
        .quit
     end with
     set ie = nothing

     '-- finished !
     application.statusbar = false
end sub

Open in new window

0
 

Author Closing Comment

by:STP8377
Comment Utility
Excelllent!   I made a few changes and that code worked perfectly.  I learned a lot from this, thanks.
0
 
LVL 19

Expert Comment

by:akoster
Comment Utility
great !
you're welcome
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now