PDA

View Full Version : Save an excel file got from a '.do' script automatically



gundaah
07-29-2009, 12:31 AM
Hi

I m trying to download an excel file which the output of a submit button in a page, I am using MSXML2.XMLHTTP50 object to send a url , use the responsebody / responsestream along with a adodb.stream object to save it as an excel file but all that i get is an error page. i use single sign on to this site so login is not a problem , i know the URL that pops the "save as" dialog box after generating the excel file and i tried that in IE directly and it works, i am able to generate the excel file.But when i try to do that using code through VBA all i get is a error page.

I have given my code below. there are two parts, one method connects to the website and passes the url required for generating the excel file.
I have to necessarily open the homepage for my identity to be established and then only i can shoot the other url which will pop the excel file out.

The second part is a method where i use ADODB.Stream object to save the outcome of the previous method's xmlhttp.send event.

I tried running acode given part of another thread in this form but it is not working. Excel crashed.


Private Sub downloadFile()
Dim URL As String
Dim oreq As MSXML2.XMLHTTP50
Dim m_Form
Set oreq = New MSXML2.XMLHTTP50
URL = "//homepage/login.do"
oreq.Open "POST", URL, False
oreq.send
' goto the reports interface
URL = "//homepage/reports/index.do"
oreq.Open "POST", URL, False
oreq.send
URL = "//homepage/changeContext.do?" & ' all the parameters needed for the file generation
' and this is also the equivalent of a submit button. this works directly in IT but not through VBA
oreq.Open "GET", URL, False
oreq.setRequestHeader "Content-Type", "application/ms-excel"
oreq.send

SaveBinaryData ThisWorkbook.Path & "\test1.xls", oreq.responseStream
'or - but both of these save statements gives me a html page and not an excel file
SaveBinaryData ThisWorkbook.path & "\test1.xls", oreq.responseBody
End Sub

' this saves the byte array into an excel file int he local disk
Public Function SaveBinaryData(FileName, ByteArray)

Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2

'Create Stream object
Dim BinaryStream As ADODB.Stream
Set BinaryStream = New ADODB.Stream

'Specify stream type - we want To save binary data.
'BinaryStream.Type = adTypeBinary

'Open the stream And write binary data To the object

With BinaryStream
.Type = adTypeBinary
.Open
'.Mode = adModeWrite
.Write ByteArray
.SaveToFile FileName, adSaveCreateOverWrite
End With
End Function


Thanks in advance for your help :-)

Regards
Vijay