"Save As" for Excel file with Internet Explorer

[MattKlein]

Hi all!

I have created an Internet Explorer object and used that to log into a website. Now, I need to download several .xls files from this web site.

When I .Navigate to them IE prompts the "Save As" dialog. I would like to have it just automatically download the file and save it.

I've tried using a XMLHTTP object to save the file, but when I try and download the file, I instead get the sign-in screen.

So I guess I'm looking for one of:
1) A way to use an XMLHTTP object from a IE object that has already authenticated
2) A way to keep the "Save As" dialog from popping up and just saving the file with a name I specify
3) or any other way to get the file from a secure website to my hard drive

Thank you all very much!

-Matt

[Oorang]

Try this:

Option Explicit
'Thanks again All-API!
Private Declare Function URLDownloadToFileA Lib "urlmon" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Private Sub Test()
Dim strFileName As String
Dim strURL As String
strFileName = "C:\MyDownloadedFile.xls"
strURL = "http://www.someurl.net/myfile.xls"
If Download(strURL, strFileName) Then
Shell "notepad.exe " & strFileName
Else
MsgBox "Download was made of fail."
End If
End Sub

Public Function Download(URL As String, LocalFilename As String) As Boolean
Const lngReserved_c As Long = 0
Dim lngRetVal As Long
lngRetVal = URLDownloadToFileA(Excel.Application.Hwnd, URL, LocalFilename, lngReserved_c, lngReserved_c)
Download = Not CBool(lngRetVal)
End Function

[Ken Puls]

Hey Aaron,

I'm curious... haven't played with this at all, but what does this part accomplish?

If Download(strURL, strFileName) Then
Shell "notepad.exe " & strFileName
Else
MsgBox "Download was made of fail."
End If

Unless I misread it, it looks like it is trying to open the Excel file with notepad if the download is successful. (Kind of weird for an Excel file...)

I'm also assuming that "Download was made of fail" probably means something more like "Download failed"

Shouldn't it look more like:

If Not Download(strURL,strFileName) Then MsgBox "Download Failed!"

As I say, I haven't tested this at all, it just looked weird to me... :dunno

[Oorang]

lol Well...I was originally break testing it using html files and I was using the notepad line to AutoLaunch them. Then I forgot to change it back:oops:

As for "Made Of Fail" I was just being a bit whimsical. Sort of a tounge in cheek reference to gamer lingo. (See also, "Made of Win & Pwn", "Epic Fail!", etc.) A friend of mine and I were making fun of people who were talking that way on X-Box live, and we made fun them so much it actually seeped in our vocabulary. Irony is ironic that way.

[MattKlein]

Thanks Aaron!

I think this will end up getting me where I need to be, however, I'm still having issues with authentication. A little background...

I log into a website, then surf to a download.aspx page, which then prompts the "Save As" window. So I'm understanding it as, when I try to .navigate to the download.aspx page, the server instead returns the .xls file I want.

When trying to call

URLDownloadToFileA(IE.HWnd, URL, FileName, 0, 0)
'or
URLDownloadToFileA(IE.Application.HWnd, URL, FileName, 0, 0)
'or
URLDownloadToFileA(Excel.Application.Hwnd, URL, FileName, 0, 0)

I get an "Automation Error - The object invoked has disconnected from its clients".

However, the function works when called as

URLDownloadToFileA(0, URL, FileName, 0, 0)
'no parent window
but then it downloads the "login" page, instead of the desired .xls file. I assume is does this because a new, unauthenticate, object is trying to download the file. So that is why I'm trying to download the file from the IE object, because it has already authenticated.

Any ideas?

Thanks for the help!

-Matt

[Oorang]

Ahh well the issue there that you need the actual path to the file. If you give it a URL as a paramater it will download the actual html file. You need the full path to the file. (Example: www.microsoft.com/office/ork/2000/download/Phd2err.xls (http://www.microsoft.com/office/ork/2000/download/Phd2err.xls))

[MattKlein]

Well, when I call the download.aspx, I am going to get an .xls file because that is all the server will send me, it doens't return download.aspx.

For example, trying to download (or just opening in IE)
http://chart.apis.google.com/chart?cht=p3&chd=t:60,40&chs=250x100&chl=Hello|World
will save (or open) a PNG file, not a web page.

But the file that I'm recieving is not the .xls file that its suppose to be, but instead the "log in" page because the server sees the attempted download as a new connectioin. Just like signing into you webmail, but then opening a new IE and surfing to your inbox in that new window will prompt a log-in again.

Is there a way to save a file from a current IE object that doesn't involve user interaction, like clicking on a "Save" button? I need to use a current IE object because I have already used it to log in to the server so the server will allow that connection to download the file.

Thanks!

-Matt

[lucas]

Aaron I had trouble with your code crashing excel....an alternative.
Not as sophisticated but maybe saveas:

Option Explicit
Sub GetUpdatedMessage()
With Application
.ScreenUpdating = False
.Workbooks.Open ("http://slucas.virtualave.net/exceldata/data.xls")
ActiveWorkbook.SaveAs "F:\Temp\MyDownloadedFile.xls"
.Workbooks("MyDownloadedFile.xls").Close savechanges:=False
End With
Application.ScreenUpdating = True
End Sub

[Oorang]

Doesn't need to be sophisticated... It needs to work :yes

I noticed it was crashing on dynamic pages too. I was trying to work out why and correct/workaround it. But I was still circling so this was timely.

[lucas]

Hi Aaron, there must be a way to download these files directly without doing a savas. Your code is over my head to troubleshoot but it crashed on the link in my code too which is not a dynamic page. It is just an excel file in a server directory.

[Ken Puls]

As for "Made Of Fail" I was just being a bit whimsical. Sort of a tounge in cheek reference to gamer lingo. (See also, "Made of Win & Pwn", "Epic Fail!", etc.) A friend of mine and I were making fun of people who were talking that way on X-Box live, and we made fun them so much it actually seeped in our vocabulary. Irony is ironic that way.

Aha! Went right over my head, not being a gamer. ;)

[Oorang]

Aha! Went right over my head, not being a gamer. ;) Me either really, but I still know several, so you get this kind of information whether you intended to or not :)

[MattKlein]

Bump

[gundaah]

Hi Aaron

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 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 you code given part of the earliest entry in this thread 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

[Oorang]

Cough... *read whole thread* Cough :D

[gundaah]

Cough... *read whole thread* Cough :D

Hi Aaron

I did read through the whole thread and also tried the code given, it still did not work thats why i put the article and my code, what you are mentioning is an already existent file in a server in a some directory and what i am referring to is a runtime generated file when some particular details in a form are submitted. I think i put too much of words into my earlier post... may be that gave a wrong idea. Just think of this scenario...

you go to a particular page in a website, fillup some details in a form and click submit and then internally there is a serverside scripting that pulls some records from a DB based on your form details and then returns the output as an excel file. How do i trap this excel file using XMLHTTP.

/-Vijay

[Oorang]

Hmmm, well maybe I'm just not getting it. I can be thick sometimes:) I can see what you are trying to do, but debugging it can be a bit hard without access to the site.
If you navigate the site manually, are you still prompted to save the file?

[gundaah]

Hi Aaron

Yes, when i do it manually i can still generate the file and save it to the local drive.

Kind Regards
Vijay