Change Save Dir in IE (VBA)

D

Dave N

Hi,
I need some help with automating Internet Explorer using VBA. I have an
application to runs a report on our Internet based system and which can be
downloaded as a csv file. In order to save this file into the correct
location I need to change the directory that intintially pops up in the 'Save
As' dialog box. This defaults to the last location used. Can anyone please
suggest a way of doing this? For example, in Excel I would use the ChDir
statement, is there a similare statement for IE?
Any help would be much appreciated!
Dave
 
T

T Lavedas

Hi,
I need some help with automating Internet Explorer using VBA. I have an
application to runs a report on our Internet based system and which can be
downloaded as a csv file. In order to save this file into the correct
location I need to change the directory that intintially pops up in the 'Save
As' dialog box. This defaults to the last location used. Can anyone please
suggest a way of doing this? For example, in Excel I would use the ChDir
statement, is there a similare statement for IE?
Any help would be much appreciated!
Dave
Click to expand...

Are you actually programming in an HTML document? I ask, because it
really makes a difference what is available to you. If so, client
side scripting, which uses VBScript, not VBA, has many security
restrictions placed on it. For example, there is a "Save Directory"
entry in the registry that controls where the SaveAs dialog opens in
IE. To alter this, the normally unsafe for scripting WScript.Shell
object can read/write this registry item. However, IE will restrict
access to this object to varying degrees, depending on the security
level set. In the Medium setting, a request to permit execution of
ActiveX controls is displayed each time the page is loaded. In High,
the page just fails to load with an error. You do not want to set it
lower. The source can be flagged as trusted, but all of this requires
user intervention.

An example of HTML code that will do this is (changing the location to
the root of C:)...

<html>
<script language=vbs>
sKey = "HKCU\Software\Microsoft\Internet Explorer\Main\Save Directory"
with createobject("wscript.shell")
.RegWrite sKey, "C:\", "REG_SZ"
end with
</script>
<body>
TESTING
</body>

However, if similar code were run from a script that were controlling
IE via it's InternetExplorer.Application ActiveX interface, these
restrictions would not be in force. The Windows Scripting Host (WSH)
is one such way to gain access in this way, another is via one of the
Office application VBA implementations.

If this isn't enough information, I guess it would help to know more
exactly where your application runs to be able to provide more advice.

HTH,

Tom Lavedas
===========
http://members.cox.net/tglbatch/wsh/
 
D

Dave N

Thanks for your help with this Tom,
My application runs in Excel and I am using the Internet Explorer object
model (references the WebBroser Control - ShDocVw.dll). What I really need to
know is is there a way of changing the default Save As directory through this
or any other way while using VBA? You mentioned changing the registry using
WScript.Shell object - can this be done in VBA?
Regrards
Dave
 
T

T Lavedas

Thanks for your help with this Tom,
My application runs in Excel and I am using the Internet Explorer object
model (references the WebBroser Control - ShDocVw.dll). What I really need to
know is is there a way of changing the default Save As directory through this
or any other way while using VBA? You mentioned changing the registry using
WScript.Shell object - can this be done in VBA?
Regrards
Dave
Click to expand...

Yes, the Wscript.Shell class can be used, either the way I showed you
(late bound)...

sub setSaveAsDir(sStartin)
Const sKey = "HKCU\Software\Microsoft\Internet Explorer\Main\Save
Directory"
with createobject("wscript.shell")
.RegWrite sKey, sStartin, "REG_SZ"
end with
end sub

Or the wshom.ocx control library (Windows Script Host Object Model)
can be added to the available objects list (Object browser/
References). Then early binding can be used ...

Sub setSaveAs(sStartin)
Dim oWSH As New WshShell
Const sKey = "HKCU\Software\Microsoft\Internet Explorer\Main\Save
Directory"
oWSH.RegWrite sKey, sStartin, "REG_SZ"
End Sub

Just call the routine with the appropriate pathspec from the code that
activates the SaveAs or initializes the IE class, whichever is
appropriate.

Tom Lavedas
===========
http://members.cox.net/tglbatch/wsh/
 
T

Tim Williams

Is the CSV file linked from a URL on a page, or the result of a form
submission ?

If a link (or form submission using GET) then you could probably download it
directly using xmlHttp.

Tim
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dir() not working in MS Word 2016 in MAC system 0
How can I get a Shareable URL via VBA code to a OneDrive image? 0
Embedded Excel into Word with Tables 0
Can't save my Office documents (after computer rebuild and reauthorization) 0
VBA Export to PDF 0
VBA word change keys 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Handling internet explorer memory leaks from Excel VBA 0

Top