Google Answers Logo
View Question
 
Q: IE Automation in Excel ( Answered 4 out of 5 stars,   2 Comments )
Question  
Subject: IE Automation in Excel
Category: Computers > Programming
Asked by: nandun-ga
List Price: $200.00
Posted: 05 Oct 2006 05:17 PDT
Expires: 04 Nov 2006 04:17 PST
Question ID: 770960
I have a very specific need for a client. He needs to access secure
web data from excel. The site has you login to an https page,
redirects you to a query page where you specify parameters. On
submitting the query page, it sends you a zipped file with data.

There are multiple constraints:

a. He wants to do all this work by simply clicking on a button on an
excel page -- the button will then call a VBA routine.
b. He works for a company that has very rigid security requirements --
the only way he can access the web is through IE --  everything else
is blocked, no ftp, ping, other exe's being able to access the
internet
c. Fortunately, he can run VBA from Excel
d. He has no installation privileges, so no dll's etc -- its got to
all run from VBA.

What I need:

a. What options do I have to make this happen for my client?
b. Pointers to source that does this from VBA

Clarification of Question by nandun-ga on 05 Oct 2006 06:11 PDT
I am in a bit of a hurry on this one -- google answers did not let me
change the expiry date on the question, but this question will be
alive till 15 Oct

Request for Question Clarification by theta-ga on 05 Oct 2006 07:51 PDT
Hi nandun-ga,
  What version of MS Excel is your client using?
  It should be possible to invoke IE from Excel VBA and then
manipulate the page contents to submit data to the query page.
  Could you provide a list of form elements (their type and their
ID/Name) used on the query page? Or better yet, would it be possible
for you to provide the HTML source for the query page so i can take a
look at the form elements?  I require this information so that I can
test the sample VBA macro at my end.
  Please note that developing and testing the full VBA code that does
what you require will more time than is usually reserved for $50
questions. Therefore I request you to revise your price for this
question accordingly. Please take a look at the Google Answers pricing
guidelines: http://answers.google.com/answers/pricing.html

Regards,
Theta-ga
:)

Clarification of Question by nandun-ga on 05 Oct 2006 13:15 PDT
Dear theta-ga,

thanks for your response. What I was expecting at this stage was
merely that you would point me to someone who might have already done
this or very similar work. On the other hand, if you are willing to
provide tested code, would have no problem springing for it -- I would
need couple of days to setup the secure pages that provide the data --
and then post the url with directions -- its going to be a pretty
simple navigation. Please let me know which direction you'd like to
proceed.

Nandun-ga

Request for Question Clarification by theta-ga on 06 Oct 2006 10:15 PDT
Hi nandun-ga,
   Tested code it is then. :) Once you are done with the secure site
setup, just post the URL here, and I will test it out with the VBA
code. Also, could you tell me which version of MS Excel (97, XP, 2003,
2007) your client will be working with?
   As mentioned before, please reprice the question based on the
number of hours of effort it will involve. The Google Answers pricing
guidelines [http://answers.google.com/answers/pricing.html] should
help.

Regards,
Theta-ga
:)

Clarification of Question by nandun-ga on 06 Oct 2006 14:32 PDT
great. Please look for update in 48 hrs.

Clarification of Question by nandun-ga on 08 Oct 2006 02:33 PDT
I have enabled a simple secure web data pull at
https://www.equestionbank.com/login.asp. The login page also links to
a dummy excel file that has a button on it -- the code behind the
button needs to be written -- so the expectation for this ga
task/answer is tested code that enables the button. My client has
Windows 2000, with Excel 2000 installed. Enjoy :-)

Request for Question Clarification by theta-ga on 09 Oct 2006 01:57 PDT
Hi nandun-ga,
  The excel file has a comment saying that you require the macro to
download the zip file, extract the csv file it contains, and then
import the cvs data into the worksheet.
  Since Excel does not have in built zip manipulation capabilities,
the VBA will need to access an external utility to do this. Would you
know if your client has any Archiver such as WinZip or WinRAR
installed?
  For the rest, opening an IE instance and navigating to the download
link page should not be an issue. However, simulating a link click
would cause the download prompt dialog box to appear. The user will
need to click this to continue. Are you ok with this, or should I
explore how we can bypass this dialog?

Regards,
Theta-ga
:)

Clarification of Question by nandun-ga on 09 Oct 2006 06:40 PDT
You can safely assume Winzip. On the dialog front -- we need to get
rid of it -- and if I need to change the flow on the website, please
let me know. Thanks!

Request for Question Clarification by theta-ga on 14 Oct 2006 05:21 PDT
Hi nandun-ga,
  So there are two problems with downloading the file using an IE instance.
    1. When i programatically click on a link, the Information bar
appears at the top of the page, telling me that the download was
blocked for security reasons. This bar can be made to not appear by
adding the website URL to the 'Trusted Sites' zone in Excel. I have
not explored whether it is possible to do this from Excel. Would it be
possible for you to manually do this on the client machine?
    2. Once I do click on the link, the ie save dialog appears.

  Now the only surefire way (I am not considering SendKeys as an
option due to its brittleness) I have found (so far!) to download the
file without any prompts is to use the XmlHTTP object.
  The idea being that you create one ASP page that would take in the 4
form arguments (username, pwd,country, drug) and would then write the
appropriate zip file contents to the response. If any of the form data
were invalid, it would write an empty response string.
  This way I can use the XmlHTTP object to post the form content and
then save the website response to a zip file.

Tell me if this is possible for you to do, and then I can post the
relevant code as an answer. Till then I will be exploring getting this
done via IE automation.

Thanks,
Theta-ga

Request for Question Clarification by theta-ga on 15 Oct 2006 01:37 PDT
Hi nandun-ga,
   I was wondering if you could provide me with some more information
on the expected usage scenario here. On the login page, you say that
you want the user to login, and goto the download page without
changing any of the country/drug parameters in the dropdowns.
   Now using XMLHttp, I am able to directly access the data.zip file
over https, completely bypassing the login and the data pages. If you
can confirm that the user always wants the content of the data.zip
file, then our problem i solved.
   If on the other hand, the file would change based on login and
selected data options, then the best way is the one that I outlined in
the earlier post. Have a webpage to which I will post the 4 form
fields, and have the page write out the zip file in its response.
   Let me know your thoughts on this.

Regards,
Theta-ga
:)

Clarification of Question by nandun-ga on 15 Oct 2006 07:36 PDT
Dear theta-ga:

I have changed the code on the server to avoid the link and go
directly to the zip file after it gets the parameters. The usage
scenario from Excel would be that the user logs in, gets possible
selections from a webpage, and submits to receive the zip file. All
this can be done using XMLHttp instead of IE Automation, if XMLHttp
uses the same ports as IE. I'm certainly not expecting you to write
the page interpretation logic in VBA, just solve the technical problem
of downloading the zip file based on page parameters. Definitely want
the server to recognize that the multiple requests are from the same
session, so the user logs in only once.

Nandu-ga
Answer  
Subject: Re: IE Automation in Excel
Answered By: theta-ga on 15 Oct 2006 08:02 PDT
Rated:4 out of 5 stars
 
Hi nandun-ga,
    I am posting an initial version of the excel file for you to test.
This code directly accesses the zip file on the server. It does *not*
got through the login/data pages first. I am still working on that
issue, but I wanted you to test out the other functionality while I
work on this.
    As of now, on the click of the button, the app directly downloads
the zip file from the server to a temp folder on the user's disk. It
then extracts the csv file using WinZip. Note that there must be a
*registered* version of WinZip installed for this to work. Otherwise,
the WinZip demo dialog will popup and the app will fail. Once the csv
file is extracted, it is then read into the Excel sheet.
    You can download the excel file containing the code from:
http://rapidshare.de/files/36844325/Fetchdata2.xls.html

  I will be working on finishing off the login/data functionality today itself.

Regards,
Theta-ga
:)

Request for Answer Clarification by nandun-ga on 15 Oct 2006 09:09 PDT
It works - the only issue that I find is that it fails on the winzip
task -- not even when I increase the wait; but if I put a MsgBox after
the task, it works fine. Will look for updates.

Clarification of Answer by theta-ga on 16 Oct 2006 02:41 PDT
Hi nandu-ga,
   Quick Update:
   I have got the session login with XmlHttp working. It's based off
the code available here:
http://72.14.235.104/search?q=cache:Bxz1dMkZcYsJ:blog.netnerds.net/2006/04/27/+use+xmlhttp+to+login+cookies&hl=en&ct=clnk&cd=21
   I am currently testing the code at my end. I will be putting up the
final Excel file with the code soon.
Regards,
Theta-ga
:)

Clarification of Answer by theta-ga on 16 Oct 2006 04:14 PDT
Hi nandun-ga,
     You can download the Excel file containing the final code from:
http://rapidshare.de/files/36944619/Fetchdata-Final.xls.html

     The following are the major changes made to the code:
        - It now logs into the server, submits the form and then
stores the zip file contents that the server returns to it.
        - It now waits for Winzip to extract the data.csv file into
the temp folder before it proceeds further.
        - It deletes the temp files it created once its done


   The logic is quite simple, and the code has comments explaining the
functionality. The logic is as follows:
    - Use XmlHttp and send a GET request to the login page (login.asp)
    - This will return the session cookie that we extract and store
    - Using the session cookie we POST the username and password to
the processlogin.asp page. now we are logged in.
    - Using the session cookie, we POST the country and drug data to
the getdata.asp page. Now we have selected the required data.
    - Using the session cookie, we send a GET request to the
getdata.asp page. It returns the zip file contents to us.
    - We write the returned data in a data.zip file in the temp folder
    - We use winzip to extract data.csv from this zip file
    - We then open data.csv in text mode and read it line by line,
adding the contents of each line to the Excel Worksheet.
    - Once we are done, we delete the zip and csv files.

 I am including the code below:
================================================================
Const zipFileName = "data.zip"
Const csvFileName = "data.csv"

Sub fetchdata_Click()

    Dim fso As Object
    Dim tempFolder As String
    fetchdata.Caption = "Retrieving Data. Please Wait..."
    fetchdata.Enabled = False
    
    On Error GoTo Cleanup
    ' get path to a temp folder where the zip file will be downloaded
    ' and its contents extracted
    Set fso = CreateObject("Scripting.FileSystemObject")
    tempFolder = fso.GetSpecialFolder(2) + "\" + fso.GetTempName + "\"
    
    'Download the zip file
    DownloadZipFile tempFolder + zipFileName

    'extract csv file from the zip
    ExtractCsvFile tempFolder + zipFileName, tempFolder

    ' Import csv contents. Paste them into the sheet starting 
    ' at the specified cell
    ImportCsvData tempFolder + csvFileName, Range("A1")
    
Cleanup:
    'Delete the tem folder and files we created
    DeleteAllFiles tempFolder
    'Reset the button status
    fetchdata.Caption = "Fetch Data"
    fetchdata.Enabled = True
End Sub

Sub DownloadZipFile(zipFileName As String)
  Dim fso As Object
  Dim xmlObj As Object, stream As Object
  Dim strSource As String
  Dim cookie As String
  Set fso = CreateObject("Scripting.FileSystemObject")
  Dim loginPageUrl As String, loginFormUrl As String, dataFormUrl As String
  Dim loginFormData As String, dataFormData As String, responseData As Variant
  
  'If the output folder doesnt exist, create it
  If Not fso.FolderExists(fso.GetParentFolderName(zipFileName)) Then
    MkDir fso.GetParentFolderName(zipFileName)
  End If
  
  loginPageUrl = "https://www.equestionbank.com/login.asp"
  loginFormUrl = "https://www.equestionbank.com/processlogin.asp"
  dataFormUrl = "https://www.equestionbank.com/getdata.asp"
  loginFormData = "userid=guest&password=guest&submit=Login"
  dataFormData = "country=UK&drug=Lipitor&submit=Get%20Data"
  
  'Make a request to get the session cookie
  Call MakeSessionRequest("GET", loginPageUrl, loginFormData, cookie, True)
  'Make a request to submit the login form
  Call MakeSessionRequest("POST", loginFormUrl, loginFormData, cookie)
  'Make a request to submit the data form
  Call MakeSessionRequest("POST", dataFormUrl, dataFormData, cookie)
  'Get the zip file contents from the server
  responseData = MakeSessionRequest("GET", dataFormUrl, dataFormData, cookie)

  'Write the returned zip file contents to a file on disk
  Set stream = CreateObject("adodb.stream")
  Const adTypeBinary = 1
  Const adSaveCreateNotExist = 1
  Const adSaveCreateOverWrite = 2
  stream.Type = adTypeBinary
  stream.Open
  stream.Write responseData
  stream.savetofile zipFileName, adSaveCreateOverWrite
  stream.Close
  Set stream = Nothing
  Set xmlObj = Nothing
End Sub

Sub ExtractCsvFile(zipFileName As String, outputFolder As String)
  Dim fso As Object
  Set fso = CreateObject("Scripting.FileSystemObject")
  'Use Winzip to extract the zip file contents
  Shell "C:\progra~1\WinZip\winzip32.exe -min -e -o " + zipFileName + " " _
        + outputFolder
  'Wait for winzip to finish extraction of files
  While Not fso.FileExists(outputFolder + csvFileName)
    DoEvents
  Wend
   
End Sub

Sub ImportCsvData(filepath As String, startCell As Range)
   Dim colOffSet As Integer, rowOffset As Integer, i As Integer
   Dim token() As String, fhandle As Integer, fline As String
   'Read the csv file line by line
   'Split line items at a comma
   'Insert each item into a seperate cell
   fhandle = FreeFile()
   Open filepath For Input Access Read Lock Write As #fhandle
  
    While (Not (EOF(fhandle)))
       Line Input #fhandle, fline
       token = Split(Trim(fline), ",")
       For i = 0 To UBound(token)
           startCell.Offset(rowOffset, colOffSet).Value = token(i)
           colOffSet = colOffSet + 1
       Next i
       colOffSet = 0
       rowOffset = rowOffset + 1
    Wend
    Close #fhandle
End Sub

' This method uses XmlHttp to make a GET or POST call to the server
' It also optionally extracts the cookie information from the response headers
' It returns the entire response body.
Function MakeSessionRequest(method As String, url As String, data As String, _
 ByRef cookie As String, Optional ByRef updateCookie = False) As Byte()
 
  If Len(cookie) = 0 Then cookie = "dummy=dummy;"
  httpReferrer = Trim(url)
  postVars = Trim(data)
  
  Dim XMLHTTP As Object
  
    Set XMLHTTP = CreateObject("MSXML2.serverXMLHttp")
    XMLHTTP.Open method, Trim(url), False
    
    If UCase(method) = "POST" Then
      XMLHTTP.setRequestHeader "Content-Type", _
                       "application/x-www-form-urlencoded"
    End If
    XMLHTTP.setRequestHeader "Referer", httpReferrer 'in case the server cares
    XMLHTTP.setRequestHeader "Cookie", "to deal with XMLHTTP bug"
    XMLHTTP.setRequestHeader "Cookie", cookie
    XMLHTTP.send postVars
    
    'wait for response
    While XMLHTTP.readyState <> 4
      XMLHTTP.waitForResponse 1000
    Wend
     
    ' extract the cookie data from the response header
    If updateCookie Then
      cookie = ""
      strHeaders = XMLHTTP.getAllResponseHeaders()
      hArr = Split(strHeaders, "Set-Cookie: ")
      For kk = 1 To UBound(hArr)
          theCookie = Left(hArr(kk), InStr(hArr(kk), "path=/") - 2)
          cookie = cookie & " " & theCookie
      Next
    End If
         
    'return the response body
    MakeSessionRequest = XMLHTTP.responseBody
    Set XMLHTTP = Nothing
End Function

Public Function DeleteAllFiles(ByVal FolderSpec As String) As Boolean
'Deletes all files in folder specified
'by parameter FolderSpec. Also deletes the folder itself.
'Does not delete subfolders or files within subfolders
Dim oFs As New FileSystemObject
Dim oFolder As Folder
Dim oFile As File

If oFs.FolderExists(FolderSpec) Then
    Set oFolder = oFs.GetFolder(FolderSpec)
    On Error Resume Next
    For Each oFile In oFolder.Files
        oFile.Delete True 'deletes read-only file also
    Next
    DeleteAllFiles = oFolder.Files.Count = 0
    oFs.DeleteFolder FolderSpec, True
End If

End Function
================================================================

Related resources
=================
    - Netnerds.net:Sustain Remote Cookie Sessions in an ASP/VBScript
      [http://72.14.235.104/search?q=cache:Bxz1dMkZcYsJ:blog.netnerds.net/2006/04/27/+use+xmlhttp+to+login+cookies&hl=en&ct=clnk&cd=21]

    - Zip Activeworkbook, File or Files with WinZip	
      [http://www.rondebruin.nl/zip.htm]
    
    - Automating Internet Explorer
      [http://www.dailydoseofexcel.com/archives/2004/09/22/automating-internet-explorer/]
-------------------------------------------

Hope this helps.
If you need any clarifications, just ask!

Regards,
Theta-ga
:)



==========================================
Google Search Terms Used:

use xmlhttp maintain session
vba excel send form POST data https
open winzip files with vba
vba read csv into excel

Request for Answer Clarification by nandun-ga on 17 Oct 2006 18:52 PDT
Dear Theta-ga:

In general this works, but I'm finding some erratic behavior in how
the requests from XMLHttp get treated vs. from a browser. Specific
problem areas are in response.redirect on the server, and that the get
requests aren't passing parameters to the server. My guess is that
this has to do with the cookie parsing in the code -- I have put
additional code on the site so you can see the logs of what happens
when you use a regular pathway through the browser, versus using the
XMLHTTP object from Excel. I have also zipped up the code -- both the
code and directions to access the log are on
www.equestionbank.com/login.asp

Thanks! 

Nandun-ga

Clarification of Answer by theta-ga on 20 Oct 2006 13:35 PDT
Hi Nandu-ga,
   The problem here seems to be that the ServerXmlHttp object is not
handling the redirect to the downloadable file properly. This is what
happens when we send the second post request:
     - Say the second POST request sends the form data as China and Zocor
     - The getdata.asp page processes this and sends a Redirect
instruction back to the client in its response.
     - Now, a browser handles this redirect correctly and hits the url
specified, and downloads the file.
     - XmlHttp however runs into a problem when trying to follow the
redirect instruction, and the response contains an error message
returned by the server.
     - This is why the original solution made a GET request after the
final POST to download the file. The problem here was that the GET
request was hardwired to hit the url for the zip file and so could not
deal with the conditional switch between the data.csv and data.zip.

The solution is something I suggested earlier. Since you just care
about sending the right Zip file to the user, whenever the user
submits the second form instead of using Response.Redirect to redirect
the client to the zip file, use Response.BinaryWrite to write the
complete zip file to the response. This way the XmlHttp POST call will
be able to read the zip file from the response (without having to
resort to a GET call). The browser will still deal with the zip file
as before (by popping up the download dialog box).

To do the above, you will need to make changes to getdata.asp as well
as the VBA code in the excel file. The modifications are documented
below:

 - In the getdata.asp file, replace all the Response.Redirect calls
with the following code:
        Response.Buffer = TRUE
	  Response.Clear
        Response.ContentType = "application/x-zip-compressed"
        Response.BinaryWrite(ReadBinaryFile(Server.MapPath("data.zip")))
        Response.End
    The ReadBinaryFile() is a user defined function that reads in the
specified file in binary mode. You can find this code online at:
http://www.motobit.com/tips/detpg_read-write-binary-files/ . I have
copied this code below:
     Function ReadBinaryFile(FileName)
        Const adTypeBinary = 1
        'Create Stream object
        Dim BinaryStream
        Set BinaryStream = CreateObject("ADODB.Stream")
       'Specify stream type - we want To get binary data.
        BinaryStream.Type = adTypeBinary
       'Open the stream
        BinaryStream.Open
       'Load the file data from disk To stream object
        BinaryStream.LoadFromFile FileName
       'Open the stream And get binary data from the object
        ReadBinaryFile = BinaryStream.Read
   End Function

 - Now, we need to make a minor change in the Excel VBA code. In the
DownloadZipFile method, we currently have code that makes the XmlHttp
requests as follows:
  'Make a request to get the session cookie
  Call MakeSessionRequest("GET", loginPageUrl, loginFormData, cookie, True)
  'Make a request to submit the login form
  Call MakeSessionRequest("POST", loginFormUrl, loginFormData, cookie)
  'Make a request to submit the data form
  Call MakeSessionRequest("POST", dataFormUrl, dataFormData, cookie)
  'Get the zip file contents from the server
  responseData = MakeSessionRequest("GET", dataFormUrl, dataFormData, cookie)

Remove the last GET request and change the code to:
  'Make a request to get the session cookie
  Call MakeSessionRequest("GET", loginPageUrl, loginFormData, cookie, True)
  'Make a request to submit the login form
  Call MakeSessionRequest("POST", loginFormUrl, loginFormData, cookie)
  'Make a request to submit the data & get the zip file contents 
  responseData = MakeSessionRequest("POST", dataFormUrl, dataFormData, cookie)
  
For your convenience, I have compressed and uploaded the getdata.asp
and the updated excel file. You can download them from:
[http://rapidshare.com/files/60956/fetchdata_updates.zip.html]


Hope this helps!
Regards,
Theta-ga
:)
nandun-ga rated this answer:4 out of 5 stars
Theta-ga was very responsive and did a good job with well thoght-out
code; unfortunately, the solution didn't work -- my guess is that the
client probably has http proxies installed. I've pretty much given up
on this approach -- since MSXML doesn't support the proxy
authentication -- the only approach that is feasible is probably
something like http://forums.devx.com/showthread.php?t=3473 (they
don't provide the code, though)

Comments  
Subject: Re: IE Automation in Excel
From: inventorbob-ga on 11 Oct 2006 09:06 PDT
 
The best way I have accomplished this is through sendkeys

You open the web page from a call or shell in Excel.

now you can look at the web page as it comes up in the taskbar.  If
you hold the mouse over the web page's icon on the taskbar, it will
show you the name of that application.

use

AppActivate <name you saw on the taskbar>

it will activate the web page.

now use

Application.Sendkeys "<whatever your login name is>"
Application.Sendkeys "{Tab}" 'or whatever keystroke moves you to the password
Application.sendkeys "<Password>"

etc.  You just find out how to do everything you need in the website
using keystrokes, and then use Sendkeys to send those keystrokes.

If you need to pause or wait, there are many excel tips on adding wait
times.  I like Application.OnTime the best.

You can also use excel to start up winzip, and then activate winzip,
and send keystrokes to it.

Keystrokes.  Thats the best quick and dirty solution to many Excel
automation problems.
Subject: Re: IE Automation in Excel
From: nandun-ga on 11 Oct 2006 13:49 PDT
 
dear inventorbob-ga: thanks for the comment. its an excellent way to
achieve this kind of automation and i'd probably do that for my needs
where i need a quick fix. unfortunately, my clients may not be
understanding about different windows being activated and run while
they are inside excel. all the same -- thanks very much for taking the
time -- its a valuable piece of advice i'm sure i'll end up using.
best, nandu-ga

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy