I posted this question once before but asked the wrong question..

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.

I am pulling a form from a document libary. The form then has to be
saved as a different name by the user to another directory. Each form
is saved as a different name by the user so I can not use the VBA code
to save worksheet as.

Is there any way to change the home directory for document once it has
been opened.


Thanks
Steve

No that is not possible because it is a property of the workbook, and trying
to save elsewhere requires SaveAs.

You could always move it before opening it

Dim sOldName As String
Dim sNewName As String

sOldName = "C:\myTest\18.xls"
sNewName = "C:\19.xls"

Name sOldName As sNewName

Workbooks.Open "C:\19.xls"

but any directoreis being moved to must already exist, or else you need to
create them.

Whjy can't you use SaveAs though, you can get the username with

Environ("UserName")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"smonczka" wrote in message
oups.com...
I posted this question once before but asked the wrong question..

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.

I am pulling a form from a document libary. The form then has to be
saved as a different name by the user to another directory. Each form
is saved as a different name by the user so I can not use the VBA code
to save worksheet as.

Is there any way to change the home directory for document once it has
been opened.


Thanks
Steve

Only by doing a saveas to the directory.

you can change the default file location, but the user will have to use
SaveAs and not just Save.

chDrive "C"
chDir "C:\MyFolder"

--
Regards,
Tom Ogilvy

"smonczka" wrote in message
oups.com...
I posted this question once before but asked the wrong question..

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.

I am pulling a form from a document libary. The form then has to be
saved as a different name by the user to another directory. Each form
is saved as a different name by the user so I can not use the VBA code
to save worksheet as.

Is there any way to change the home directory for document once it has
been opened.


Thanks
Steve

The problem with using SaveAs is that I'm pulling the form from a WSS
document library, it's a quote form.

The user opens up a custom contact database application, locates the
clients name and then selects a "make quote" button from within the
application. Once they do this .net calls the excel form from the WSS
directory and populates it with the contact information.

Problem is when the form is saved by the user the default directory is
\document library\forms, cause that where the form was pulled from.
Meaning the user has to manual change the directory to \document
library. LOL Like that is going to happen.

If i use a SaveAs statement in an OnOpen macro the document will be
saved in the proper directory but it bypasses Sharepoints MetaData
which is colected by the document libary from the user when the
document is saved. Any type of SaveAs will automaticly bypass this
request to input the MetaData.

Sorry about being long winded, but I hope that makes it clearer.

Thanks
Steve

I have no idea what you are talking about, but I will take your word for it
:-).

So does the other suggestion not do it for you either?

Bob


"smonczka" wrote in message
oups.com...
The problem with using SaveAs is that I'm pulling the form from a WSS
document library, it's a quote form.

The user opens up a custom contact database application, locates the
clients name and then selects a "make quote" button from within the
application. Once they do this .net calls the excel form from the WSS
directory and populates it with the contact information.

Problem is when the form is saved by the user the default directory is
\document library\forms, cause that where the form was pulled from.
Meaning the user has to manual change the directory to \document
library. LOL Like that is going to happen.

If i use a SaveAs statement in an OnOpen macro the document will be
saved in the proper directory but it bypasses Sharepoints MetaData
which is colected by the document libary from the user when the
document is saved. Any type of SaveAs will automaticly bypass this
request to input the MetaData.

Sorry about being long winded, but I hope that makes it clearer.

Thanks
Steve

Sounds like you are saying you want to save the original document, but you
want it have a different name and be saved to a different location.
unfortunately, that is not possible without doing a SaveAs. It sounds like
your solution would have to reside with your interface to the sharepoint
server. Possibly you can define this document as a template and gather data
on workbooks created from that template. I can't say, but it would make
sense that it would support such a construct.

--
Regards,
Tom Ogilvy


"smonczka" wrote in message
oups.com...
The problem with using SaveAs is that I'm pulling the form from a WSS
document library, it's a quote form.

The user opens up a custom contact database application, locates the
clients name and then selects a "make quote" button from within the
application. Once they do this .net calls the excel form from the WSS
directory and populates it with the contact information.

Problem is when the form is saved by the user the default directory is
\document library\forms, cause that where the form was pulled from.
Meaning the user has to manual change the directory to \document
library. LOL Like that is going to happen.

If i use a SaveAs statement in an OnOpen macro the document will be
saved in the proper directory but it bypasses Sharepoints MetaData
which is colected by the document libary from the user when the
document is saved. Any type of SaveAs will automaticly bypass this
request to input the MetaData.

Sorry about being long winded, but I hope that makes it clearer.

Thanks
Steve

Thanks both for your replys. Tom yes that is what I am trying to do.
I just want to open the file from one directory and have the
application show a different directory when the user saves it.

I'll try the WSS group from here then. Looks like you might be right
and this would have to be handled on the Sharepoint end.

Thanks again,

Steve