Programing Help Needed

[smonczka]

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

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[smonczka]

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

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[smonczka]

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