I am trying to change the Save As directory once a file is open so that
the user automaticly saves the file to a different directory than the
one they opened it in.

Example:
user opens \\serverA\form.xls
user alters the form
on save I need to change the default directory to
\\serverb\form1.xls

Any ideas?

Thanks
Steve

"smonczka" wrote in message
oups.com...
I am trying to change the Save As directory once a file is open so that
the user automaticly saves the file to a different directory than the
one they opened it in.

Example:
user opens \\serverA\form.xls
user alters the form
on save I need to change the default directory to
\\serverb\form1.xls

Hi Steve,

The following very simple Windows API function will set the current
directory to any valid path string:

Private Declare Function SetCurrentDirectoryA _
Lib "kernel32" (ByVal lpPathName As String) As Long

Public Sub SetUNCPath(ByVal szPathToSet As String)
SetCurrentDirectoryA szPathToSet
End Sub

Note that a valid UNC path specification has the form
\\ServerName\ShareName with no file name included. If you pass the API
function an invalid path it will fail silently, so if the current directory
doesn't change as you expect, double check what's in the string you're
passing it.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

Rob you just saved me several weeks of work. If I couldn't get this
worked out I was dead in the water. Thanks!

Steve

Rob,

I made a mistake. I need to change the directory to a HTPP directory
not an UNC dir. The problem is that I have an application that calls
an EXCEL template from within a Sharepoint document library. When the
user fills out the form and saves it, Excel wants to save it back to
the template directory. What I need is to redirect the save as to the
actual library.

What I though I would do is set up an OnOpen macro that changed the
directory. Is this at all possible?

Thanks
Steve