Solved

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

Posted on 2011-02-17
7
1,457 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 19

Expert Comment

by:Arno Koster
ID: 34917366
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
ID: 34918371
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:Arno Koster
ID: 34924536
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:STP8377
ID: 34926012
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:
Arno Koster earned 500 total points
ID: 34928750
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
ID: 34929933
Excelllent!   I made a few changes and that code worked perfectly.  I learned a lot from this, thanks.
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 34932522
great !
you're welcome
0

Featured Post

Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

690 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