asked on

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

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?

Arno Koster

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 ?

STP8377

ASKER

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

Arno Koster

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.

STP8377

ASKER

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

ASKER CERTIFIED SOLUTION

Arno Koster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

STP8377

ASKER

Excelllent! I made a few changes and that code worked perfectly. I learned a lot from this, thanks.

Arno Koster

great !
you're welcome

Excel Learner

Hi,

Could you please specify how you made the webquery to work on an already open website , for every time i use it i get new instance of ie open

Thanks