File SaveAs

[jackd]

I have posted a similar request (A2K and IE6 - File Save As) in Integration/Automation with no responses .

However, I now think the question is more about Accessing File SaveAs Dialog of Common Dialog Control. Since I have had no replies, I am trying MISC HELP.

I am trying to SAVE a web page to a local file via vba. I am using an Internet Explorer object, can naviagate to the page, but can not figure out how to save the file.

I have come to realize that I can not get or set a local file name, nor a Save methof if there is one. I think the Intenret explorer object must be "linked" somehow with the CommonDialog Save.

Does anyone have experience with this? Any comments would be appreciated.

jackd

[mvidas]

jackd,

Are you locked into using an IE object? There are other alternatives, much better in my opinion (just because I'm not a huge fan of IE, I think).

Also, are you trying to choose the local filename at runtime?

Matt

[jackd]

Matt,

First thanks for replying.
I am familiar with Access and VBA, although not expert. I'm not locked in necessarily. Here's the overview of what I'm trying to do..

We have a database of 60000 companies. Our data base is accessible on the web
http://strategis.ic.gc.ca/cdncc Some people in the organization have a requirement to take all the info about some selected companies to Trade Shows, meetings etc and can not be certain to have internet access. They want a selection of companies on CD. We have a licence for LIKSE (offline browser with search engine). I'm working with A2K and VBA to select companies and to (hopefully) get Complete Profiles (webpage) onto local files, so that I can add them to the Offline Browser on a CD.

What I have found is that IE (File Save As (web page complete)) does a fantastic job of cleaning up the local files. It removes session variables, relative server addresses etc from the html. I was trying to automate a process and take advantage of the File Save As.

I am open to options and any comments.

jack

[XLSTraderJK]

I too am trying to figure out how to automate the "saveas" feature of IE.

What I've learned is that in VB6 - this can't be done.

In VBA Excel, it can be done for some webpages that do not use java. (Generally only works with static webpages.) Just use Excel's web queries functionality:

wa$ = "somewebpage"
With ActiveSheet.QueryTables.Add(Connection:="URL;" & wa$, Destination:=Range("A1"))
.BackgroundQuery = False
.TablesOnlyFromHTML = True
.Refresh 'BackgroundQuery = False
.SaveData = True
End With


In .NET - can be done. But requires lots of coding. Basically, must access subroutines that reproduce the MHT format.

As an alternative, write a windows script: using VBscript, or Jscript to open the webpage using IE then save the webpage.

Automate the script through the use of task scheduler and windows scripting host.

Essentially, your creating a macro that records your keystrokes (the windows script). After this is accomplished you then use task scheduler to run the macro at a specific time.

[jackd]

Thanks XLSTraderJK,

I've sort of come to the same conclusion. I am working with dynamic pages. And have come to the same conclusion -- probably can't be done-- at least not in my timeframe.

I am using URLDownloadToFile API to download files from our website. I save 1 copy of the local graphics and css manually. It seems to work for my purposes.

jack

[XLSTraderJK]

For the benefit of subsequent readers of this thread,

The URLDownloadToFile API works well, but only saves the text of the webpage. Graphics and other support files are not retrieved. Note that webpages that use java generally are designed to hide content, and therefore this API doesn't get the embedded content.

[mvidas]

Hi jack,

Apparently I missed your original reply! Very sorry, not sure how that happened.

You do have a couple alternatives to using the IE, one being the URLDownloadToFile.
The only downfall is, as you know, you have to save the pictures and everything else, etc, as the URLDownloadToFile and other methods save the individual files and not the entire 'entity' of the page.
Just to show you some other alternatives, I do prefer the XMLHTTP method when saving files as I don't have to make any API calls to do so.[vba]Function SaveWebFile(ByVal vWebFile As String, ByVal vLocalFile As String) As Boolean
Dim oXMLHTTP As Object, i As Long, vFF As Long, oResp() As Byte
Set oXMLHTTP = CreateObject("Microsoft.XMLHTTP")
oXMLHTTP.Open "GET", vWebFile, False
oXMLHTTP.Send
oResp = oXMLHTTP.ResponseBody
vFF = FreeFile
If Dir(vLocalFile) <> "" Then Kill vLocalFile
Open vLocalFile For Binary As #vFF
Put #vFF, , oResp
Close #vFF
Set oXMLHTTP = Nothing
End Function[/vba]
You can always use the WinInet API as well, see details at http://www.freevbcode.com/ShowCode.asp?ID=1252

BTW- You can use the SaveAs dialog, heres an example that prompts the user for a saveas name[vba] Dim IE As Object 'InternetExplorer
Set IE = CreateObject("internetexplorer.application") 'New InternetExplorer
IE.Navigate "http://www.vbaexpress.com"
IE.Visible = True
Do Until IE.ReadyState = 4 'READYSTATE_COMPLETE
DoEvents
Loop
IE.ExecWB 4, 1 'OLECMDID_SAVEAS, OLECMDEXECOPT_PROMPTUSER[/vba]I left it as late binding if you didn't create a reference to ms inet controls.
You could specify the filename in the code like [vba]IE.ExecWB 4, 2, "C:\filename.html" 'OLECMDID_SAVEAS, OLECMDEXECOPT_DONTPROMPTUSER, "C:\filename.html"[/vba]But the saveas window will still appear (perhaps without ie.visible=true it might work, not sure)
Matt

[jackd]

Thanks Matt/mvidas,

I'm tryinig the SaveWebFile but get an Error 13 Type Mismatch on this statement

oResp = oXMLHTTP.ResponseBody

I'm definitely calling the function with 2 strings???
jack

[mvidas]

D'oh!
I forgot I changed it to download binary files, didn't think about that before posting it. Try the following (make sure you save your project before running--my excel VBA doesn't like downloading .php or .jsp pages (though it works without issue in VB6) and I have to force-close my excel sometimes):[vba]Function SaveWebFile(ByVal vWebFile As String, ByVal vLocalFile As String) As Boolean
Dim oXMLHTTP As Object, vFF As Long, oResp As String
Set oXMLHTTP = CreateObject("Microsoft.XMLHTTP")
oXMLHTTP.Open "GET", vWebFile, False
oXMLHTTP.Send
oResp = oXMLHTTP.ResponseText
vFF = FreeFile
If Dir(vLocalFile) <> "" Then Kill vLocalFile
Open vLocalFile For Output As #vFF
Print #vFF, oResp
Close #vFF
Set oXMLHTTP = Nothing
End Function[/vba]
Matt

[XLSTraderJK]

I would like to add that those of you using Win98 and VB6, will have problems using the createobject("InternetExplorer.application") code.

Be aware that you will lose two File Type Options in the dialogbox: 1. the ability to save the webpage in a single file format .mht and 2. the option to save the complete webpage is also disabled when using this code.

So using this code forces you to 1. respond to a dialog box (user input required), and 2. it only saves textual content and html souce code.

For those of us interested in saving a webpage in single file format .mht or a complete webpage including graphics, etc. This code isn't for you.

[brettdj]

Matt,

Its time this one went in the KB

Cheers

Dave

[mvidas]

I added it for you, Dave The savewebfile, not savewebtext one. I'll wait to see if anyone has any issues with the savewebfile routine, so I can apply them to the savewebtext one.

[brettdj]

Posted link to it at Dicks Blog, http://www.dicks-blog.com/archives/2...orer/#comments

And my version of it at Ozgrid, http://www.ozgrid.com/forum/showthread.php?t=15012