how to save a file from a diaglog box using VB or VBA
 

I have an .asp file that produces an EXCEL file as a result. Now I have
to automate the process of opening the file and saving the resulting
Excel file to the local machine/ desktop.

Right now I can only open the link to the file using (reference:
Microsoft internet controls) :

Sub Test()
Dim IE As New SHDocVw.InternetExplorer
IE.Visible = True
IE.Navigate "http://xlsAllInOne.asp"
End Sub

Now how do i eliminate the popup/dialogue box that comes up asking if
you want to save or open the file. Can that done either from VB or
somewhere in the HTML?

Thanks in advance.

how to save a file from a diaglog box using VB or VBA
 

You don't need to use IE to get the file - you should be able to
download the file directly and save it to disk.

Something like:

Sub FetchFile(sURL As String, sPath As String)
Dim oXHTTP As Object
Dim oStream As Object

Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
Set oStream = CreateObject("ADODB.Stream")

Application.StatusBar = "Fetching " & sURL & " as " & sPath

oXHTTP.Open "GET", sURL, False
oXHTTP.send

With oStream
.Type = adTypeBinary
.Open
.Write oXHTTP.responseBody
.SaveToFile sPath, adSaveCreateOverWrite
.Close
End With

Set oXHTTP = Nothing
Set oStream = Nothing

End Sub


Sub TestSave()
FetchFile "http://xlsAllInOne.asp", ThisWorkbook.Path & "\test.xls"
End Sub


Tim.


"bi" wrote in message
oups.com...
I have an .asp file that produces an EXCEL file as a result. Now I
have
to automate the process of opening the file and saving the resulting
Excel file to the local machine/ desktop.

Right now I can only open the link to the file using (reference:
Microsoft internet controls) :

Sub Test()
Dim IE As New SHDocVw.InternetExplorer
IE.Visible = True
IE.Navigate "http://xlsAllInOne.asp"
End Sub

Now how do i eliminate the popup/dialogue box that comes up asking
if
you want to save or open the file. Can that done either from VB or
somewhere in the HTML?

Thanks in advance.

how to save a file from a diaglog box using VB or VBA
 

Is there a reference Library I should include? It is giving me error at
oXHTTP.Send. I am running this VBA in MS Access or Excel. Both times it
gave the same error.

Thanks
Bi

how to save a file from a diaglog box using VB or VBA
 

I was about to say I used late binding, so you should not need to
reference any other libraries, but then I noticed I had used some
constants from the other libraries.

Try adding refs to
Microsoft XML (mine is v2.6)
Microsoft ActiveX Data Objects (mine is 2.7)


Also, perhaps it doesn't like the URL you're passing to it ?
The URL you posted didn't look valid to me....

Tim.



"bi" wrote in message
oups.com...
Is there a reference Library I should include? It is giving me error
at
oXHTTP.Send. I am running this VBA in MS Access or Excel. Both times
it
gave the same error.

Thanks
Bi