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
|
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
:)
|
