I have been trying to enter a code into my sheet which will save the file to
a directory on the network using a pre-defined file name. My filename is a
declared string, and is working properly. I can get as far as:

* Getting the Save As dialogue in the right folder, but with no filename
* Getting the Save As dialogue to show a name, not the right one, in the
last place I saved
* Getting the Save As dialogue to show the completely wrong name in the
right folder.

Assuming I wanted to do this using the GetSaveAsFilename option, how would I
do it? And how would it differ if I wanted an automatic save where the user
didn't have the prompt? The end result would have to be:

\\server\folder\subfolder\filenamefromstring.xls

Whichever way it was done...!

Aaron, try:

Sub foo()
Dim sPath$, sFile$, sFull$
sPath = "c:\windows\"
sFile = "text.csv"

ChDrive sPath
ChDir sPath
sFull = Application.GetSaveAsFilename(sFile, "CSV files,*.csv")


End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Aaron Howe wrote :

I have been trying to enter a code into my sheet which will save the
file to a directory on the network using a pre-defined file name. My
filename is a declared string, and is working properly. I can get as
far as:

* Getting the Save As dialogue in the right folder, but with no
filename * Getting the Save As dialogue to show a name, not the right
one, in the last place I saved
* Getting the Save As dialogue to show the completely wrong name in
the right folder.

Assuming I wanted to do this using the GetSaveAsFilename option, how
would I do it? And how would it differ if I wanted an automatic save
where the user didn't have the prompt? The end result would have to
be:

\\server\folder\subfolder\filenamefromstring.xls

Whichever way it was done...!

Maybe something like

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel as Boolean)


Cancel = true
Application.Enablevenets = False
me.saveas Me.Range(Myfilename)
Application.Enablevenets = true


End Sub

will save correctly each time user is trying to save, so quite
drastically;)

Dm Unseen

This works where a contigious name should be given, but the name will change
depending on how the form is filled out... so how do I adapt:

sFull = Application.GetSaveAsFilename(sFile, "CSV files,*.csv")

to make it the pre-defined name from the string?

"keepITcool" wrote:

Aaron, try:

Sub foo()
Dim sPath$, sFile$, sFull$
sPath = "c:\windows\"
sFile = "text.csv"

ChDrive sPath
ChDir sPath
sFull = Application.GetSaveAsFilename(sFile, "CSV files,*.csv")


End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Aaron Howe wrote :

I have been trying to enter a code into my sheet which will save the
file to a directory on the network using a pre-defined file name. My
filename is a declared string, and is working properly. I can get as
far as:

* Getting the Save As dialogue in the right folder, but with no
filename * Getting the Save As dialogue to show a name, not the right
one, in the last place I saved
* Getting the Save As dialogue to show the completely wrong name in
the right folder.

Assuming I wanted to do this using the GetSaveAsFilename option, how
would I do it? And how would it differ if I wanted an automatic save
where the user didn't have the prompt? The end result would have to
be:

\\server\folder\subfolder\filenamefromstring.xls

Whichever way it was done...!

i posted "pseudo code" illustrating
the use of chdrive/chdir before calling the dialog.
which was what you were asking.

if you wrap it in a sub make sfile an argument
(and make the filefilter more flexible.
'in your form..
Sub CallerinUserform()
Call SaveToMYFolder(txtfilename.Text)
End Sub

'in the forms codemodule or a normal module
Sub SaveToMYFolder(sFile$)
Dim sPath$, sFull$
sPath = "c:\"
ChDrive sPath
ChDir sPath
sFull = Application.GetSaveAsFilename(sFile, ",*." & Right$(sFile, 3))
'blah blah

End Sub
--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Aaron Howe wrote :

This works where a contigious name should be given, but the name will
change depending on how the form is filled out... so how do I adapt:

sFull = Application.GetSaveAsFilename(sFile, "CSV files,*.csv")

to make it the pre-defined name from the string?

"keepITcool" wrote:

Aaron, try:

Sub foo()
Dim sPath$, sFile$, sFull$
sPath = "c:\windows\"
sFile = "text.csv"

ChDrive sPath
ChDir sPath
sFull = Application.GetSaveAsFilename(sFile, "CSV files,*.csv")


End Sub




--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Aaron Howe wrote :

I have been trying to enter a code into my sheet which will save
the file to a directory on the network using a pre-defined file
name. My filename is a declared string, and is working properly.
I can get as far as:

* Getting the Save As dialogue in the right folder, but with no
filename * Getting the Save As dialogue to show a name, not the
right one, in the last place I saved
* Getting the Save As dialogue to show the completely wrong name
in the right folder.

Assuming I wanted to do this using the GetSaveAsFilename option,
how would I do it? And how would it differ if I wanted an
automatic save where the user didn't have the prompt? The end
result would have to be:

\\server\folder\subfolder\filenamefromstring.xls

Whichever way it was done...!

I think we may be talking at cross purposes here. If I refer back to my
original question, I was asking how the code would accomodate a filename
which changes depending on how the form is filled out.

Therefore the filepath will always be \\server\fodler\subfolder
but the filename could be
Q12345AB
Q12346AB
Q12347AK
etc etc, and will be different everytime, therefore creating a unique file
for each unique form entry

"keepITcool" wrote:



i posted "pseudo code" illustrating
the use of chdrive/chdir before calling the dialog.
which was what you were asking.

if you wrap it in a sub make sfile an argument
(and make the filefilter more flexible.
'in your form..
Sub CallerinUserform()
Call SaveToMYFolder(txtfilename.Text)
End Sub

'in the forms codemodule or a normal module
Sub SaveToMYFolder(sFile$)
Dim sPath$, sFull$
sPath = "c:\"
ChDrive sPath
ChDir sPath
sFull = Application.GetSaveAsFilename(sFile, ",*." & Right$(sFile, 3))
'blah blah

End Sub
--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Aaron Howe wrote :

This works where a contigious name should be given, but the name will
change depending on how the form is filled out... so how do I adapt:

sFull = Application.GetSaveAsFilename(sFile, "CSV files,*.csv")

to make it the pre-defined name from the string?

"keepITcool" wrote:

Aaron, try:

Sub foo()
Dim sPath$, sFile$, sFull$
sPath = "c:\windows\"
sFile = "text.csv"

ChDrive sPath
ChDir sPath
sFull = Application.GetSaveAsFilename(sFile, "CSV files,*.csv")


End Sub




--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Aaron Howe wrote :

I have been trying to enter a code into my sheet which will save
the file to a directory on the network using a pre-defined file
name. My filename is a declared string, and is working properly.
I can get as far as:

* Getting the Save As dialogue in the right folder, but with no
filename * Getting the Save As dialogue to show a name, not the
right one, in the last place I saved
* Getting the Save As dialogue to show the completely wrong name
in the right folder.

Assuming I wanted to do this using the GetSaveAsFilename option,
how would I do it? And how would it differ if I wanted an
automatic save where the user didn't have the prompt? The end
result would have to be:

\\server\folder\subfolder\filenamefromstring.xls

Whichever way it was done...!

Your orignal question? Not in this thread.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Aaron Howe wrote :

I think we may be talking at cross purposes here. If I refer back to
my original question, I was asking how the code would accomodate a
filename which changes depending on how the form is filled out.

Therefore the filepath will always be \\server\fodler\subfolder
but the filename could be
Q12345AB
Q12346AB
Q12347AK
etc etc, and will be different everytime, therefore creating a unique
file for each unique form entry

"keepITcool" wrote:



i posted "pseudo code" illustrating
the use of chdrive/chdir before calling the dialog.
which was what you were asking.

if you wrap it in a sub make sfile an argument
(and make the filefilter more flexible.
'in your form..
Sub CallerinUserform()
Call SaveToMYFolder(txtfilename.Text)
End Sub

'in the forms codemodule or a normal module
Sub SaveToMYFolder(sFile$)
Dim sPath$, sFull$
sPath = "c:\"
ChDrive sPath
ChDir sPath
sFull = Application.GetSaveAsFilename(sFile, ",*." & Right$(sFile,
3)) 'blah blah

End Sub
--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Aaron Howe wrote :

This works where a contigious name should be given, but the name
will change depending on how the form is filled out... so how do
I adapt:

sFull = Application.GetSaveAsFilename(sFile, "CSV files,*.csv")

to make it the pre-defined name from the string?

"keepITcool" wrote:

Aaron, try:

Sub foo()
Dim sPath$, sFile$, sFull$
sPath = "c:\windows\"
sFile = "text.csv"

ChDrive sPath
ChDir sPath
sFull = Application.GetSaveAsFilename(sFile, "CSV files,*.csv")


End Sub




--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Aaron Howe wrote :

I have been trying to enter a code into my sheet which will
save the file to a directory on the network using a
pre-defined file name. My filename is a declared string, and
is working properly. I can get as far as:

* Getting the Save As dialogue in the right folder, but with
no filename * Getting the Save As dialogue to show a name,
not the right one, in the last place I saved
* Getting the Save As dialogue to show the completely wrong
name in the right folder.

Assuming I wanted to do this using the GetSaveAsFilename
option, how would I do it? And how would it differ if I
wanted an automatic save where the user didn't have the
prompt? The end result would have to be:

\\server\folder\subfolder\filenamefromstring.xls

Whichever way it was done...!

First of all you need to know your convention for naming the file. If the
path is always the same, but the filename is different, then you would need
to determine what the "next" filename will be. Once you know what it should
be, you can construct it by "piecing" the text together. So if it will
always start with a Q, then the first thing is that
Filename = "Q"
Then the second thing, if it is a number like you show, then
Filename = Filename & NewNumber
Then if it ends in a two letter combination
Filename = Filename & TwoLetters
And finally, if it will end with ".xls" then
Filename = Filename & ".xls"

Now, all of those could be in one line, if you already have all of the
information available, or split up as necessary if you need to capture the
data for each piece.

So it could be:
Filename = "Q" & NewNumber & TwoLetters & ".xls"
(Also, I may have inappropriately used the '&' and it may need to be a '+'
but, at least by splitting it up initially, you can test if that is correct.)

Now, if the new filename is dependent not only on the "form" used but also
the last file saved by that form, then you would need to pull all of the
filenames from the directory that match the filename construct for the
particular form, sort the list according to your numbering sequence, or just
search through the list until you have reached the end, and every occurrence
found that is "larger" than the last is the most recent name. Once the most
recent name is found, then increment to the next and set the filename as
above and you can still use the code that has been provided to help you out.

Short of it.. The filename is merely a string... You need to construct your
filename string, and then append it to the full path. I think there was some
other help provided that did the save "automatically" without prompting the
user for the filename. It involved disabling events.

Help out any?

"Aaron Howe" wrote:

I think we may be talking at cross purposes here. If I refer back to my
original question, I was asking how the code would accomodate a filename
which changes depending on how the form is filled out.

Therefore the filepath will always be \\server\fodler\subfolder
but the filename could be
Q12345AB
Q12346AB
Q12347AK
etc etc, and will be different everytime, therefore creating a unique file
for each unique form entry

"keepITcool" wrote:



i posted "pseudo code" illustrating
the use of chdrive/chdir before calling the dialog.
which was what you were asking.

if you wrap it in a sub make sfile an argument
(and make the filefilter more flexible.
'in your form..
Sub CallerinUserform()
Call SaveToMYFolder(txtfilename.Text)
End Sub

'in the forms codemodule or a normal module
Sub SaveToMYFolder(sFile$)
Dim sPath$, sFull$
sPath = "c:\"
ChDrive sPath
ChDir sPath
sFull = Application.GetSaveAsFilename(sFile, ",*." & Right$(sFile, 3))
'blah blah

End Sub
--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Aaron Howe wrote :

This works where a contigious name should be given, but the name will
change depending on how the form is filled out... so how do I adapt:

sFull = Application.GetSaveAsFilename(sFile, "CSV files,*.csv")

to make it the pre-defined name from the string?

"keepITcool" wrote:

Aaron, try:

Sub foo()
Dim sPath$, sFile$, sFull$
sPath = "c:\windows\"
sFile = "text.csv"

ChDrive sPath
ChDir sPath
sFull = Application.GetSaveAsFilename(sFile, "CSV files,*.csv")


End Sub




--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Aaron Howe wrote :

I have been trying to enter a code into my sheet which will save
the file to a directory on the network using a pre-defined file
name. My filename is a declared string, and is working properly.
I can get as far as:

* Getting the Save As dialogue in the right folder, but with no
filename * Getting the Save As dialogue to show a name, not the
right one, in the last place I saved
* Getting the Save As dialogue to show the completely wrong name
in the right folder.

Assuming I wanted to do this using the GetSaveAsFilename option,
how would I do it? And how would it differ if I wanted an
automatic save where the user didn't have the prompt? The end
result would have to be:

\\server\folder\subfolder\filenamefromstring.xls

Whichever way it was done...!

Thanks GB, that was the direction I was heading for (see thread above re
creation of the field that will be the filename! IT see bottom of this
thread, first paragraph explained that).

So now I need to get that filename into the Save As dialogue (probably the
easiest way), but by the looks of it that's not going to happen. I think the
best I can hope for is to get the variable of the filename and enter it into
the clipboard, and maybe end up using sendkeys to insert it. Ugly but
effective I guess...

"GB" wrote:

First of all you need to know your convention for naming the file. If the
path is always the same, but the filename is different, then you would need
to determine what the "next" filename will be. Once you know what it should
be, you can construct it by "piecing" the text together. So if it will
always start with a Q, then the first thing is that
Filename = "Q"
Then the second thing, if it is a number like you show, then
Filename = Filename & NewNumber
Then if it ends in a two letter combination
Filename = Filename & TwoLetters
And finally, if it will end with ".xls" then
Filename = Filename & ".xls"

Now, all of those could be in one line, if you already have all of the
information available, or split up as necessary if you need to capture the
data for each piece.

So it could be:
Filename = "Q" & NewNumber & TwoLetters & ".xls"
(Also, I may have inappropriately used the '&' and it may need to be a '+'
but, at least by splitting it up initially, you can test if that is correct.)

Now, if the new filename is dependent not only on the "form" used but also
the last file saved by that form, then you would need to pull all of the
filenames from the directory that match the filename construct for the
particular form, sort the list according to your numbering sequence, or just
search through the list until you have reached the end, and every occurrence
found that is "larger" than the last is the most recent name. Once the most
recent name is found, then increment to the next and set the filename as
above and you can still use the code that has been provided to help you out.

Short of it.. The filename is merely a string... You need to construct your
filename string, and then append it to the full path. I think there was some
other help provided that did the save "automatically" without prompting the
user for the filename. It involved disabling events.

Help out any?

"Aaron Howe" wrote:

I think we may be talking at cross purposes here. If I refer back to my
original question, I was asking how the code would accomodate a filename
which changes depending on how the form is filled out.

Therefore the filepath will always be \\server\fodler\subfolder
but the filename could be
Q12345AB
Q12346AB
Q12347AK
etc etc, and will be different everytime, therefore creating a unique file
for each unique form entry

"keepITcool" wrote:



i posted "pseudo code" illustrating
the use of chdrive/chdir before calling the dialog.
which was what you were asking.

if you wrap it in a sub make sfile an argument
(and make the filefilter more flexible.
'in your form..
Sub CallerinUserform()
Call SaveToMYFolder(txtfilename.Text)
End Sub

'in the forms codemodule or a normal module
Sub SaveToMYFolder(sFile$)
Dim sPath$, sFull$
sPath = "c:\"
ChDrive sPath
ChDir sPath
sFull = Application.GetSaveAsFilename(sFile, ",*." & Right$(sFile, 3))
'blah blah

End Sub
--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Aaron Howe wrote :

This works where a contigious name should be given, but the name will
change depending on how the form is filled out... so how do I adapt:

sFull = Application.GetSaveAsFilename(sFile, "CSV files,*.csv")

to make it the pre-defined name from the string?

"keepITcool" wrote:

Aaron, try:

Sub foo()
Dim sPath$, sFile$, sFull$
sPath = "c:\windows\"
sFile = "text.csv"

ChDrive sPath
ChDir sPath
sFull = Application.GetSaveAsFilename(sFile, "CSV files,*.csv")


End Sub




--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Aaron Howe wrote :

I have been trying to enter a code into my sheet which will save
the file to a directory on the network using a pre-defined file
name. My filename is a declared string, and is working properly.
I can get as far as:

* Getting the Save As dialogue in the right folder, but with no
filename * Getting the Save As dialogue to show a name, not the
right one, in the last place I saved
* Getting the Save As dialogue to show the completely wrong name
in the right folder.

Assuming I wanted to do this using the GetSaveAsFilename option,
how would I do it? And how would it differ if I wanted an
automatic save where the user didn't have the prompt? The end
result would have to be:

\\server\folder\subfolder\filenamefromstring.xls

Whichever way it was done...!

Well I thought that keepITcool had identified how to get the filename into
the SaveAs dialogue, by having a routine that would open the saveas dialogue
box with the filename in it. I think that the response provided by
keepITcool was a step down the line from your original question, and that as
you indicated below, I got you to creating the filename, however you have
also indicated that you can't get the dialogue to show the filename/save the
document called what you want.

Way I see it, once you have created the filename as a string. Send it to
SavetoMyFolder subroutine, which has as a path "\\server\folder\sub-folder\"
and then you could append the filename to the end of the path and have

sFull = application.GetSaveAsFilename(sFile, ",*." & Right$(sFile, 3))

I'm not sure off the top of my head, how to tell it to just save without
prompting the user, but something in this process should get you to where you
want. Actually it sounds like if you already know where the file is supposed
to end up, and you have constructed the filename the way you want it to be by
programmatically establishing the filename, then you should be able to simply
use SaveAs instead of GetSaveAsFilename.

Take a look at the SaveAs Method, it can be used to save a chart, worksheet,
or workbook and will save that applicable item with the filename transferred.
So say you want to save the worksheet that is given the Name Sheet1 (Not the
same as the tab name you see when looking at the tabs in the workbook) then
you could do

Sheet1.SaveAs Filename:=strPathAndFileName

And this will save Sheet1 as whatever the value of strPathAndFileName is
Or if you know that the name of the Sheet will stay constant as shown on the
tab of the workbook, you can perform
Worksheets("Our data Sheet").SaveAs Filename:=strPathAndFileName

And if you are intentionally designing this to be able to replace particular
files, I know that it is possible to cause the SaveAs to not prompt the user,
and instead just write over it. But I have forgotten the method to do that,
it has to do with prompts and disabling them.

Best of luck, you should be able to perform the action(s) that you are
attempting.


"Aaron Howe" wrote:

Thanks GB, that was the direction I was heading for (see thread above re
creation of the field that will be the filename! IT see bottom of this
thread, first paragraph explained that).

So now I need to get that filename into the Save As dialogue (probably the
easiest way), but by the looks of it that's not going to happen. I think the
best I can hope for is to get the variable of the filename and enter it into
the clipboard, and maybe end up using sendkeys to insert it. Ugly but
effective I guess...

"GB" wrote:

First of all you need to know your convention for naming the file. If the
path is always the same, but the filename is different, then you would need
to determine what the "next" filename will be. Once you know what it should
be, you can construct it by "piecing" the text together. So if it will
always start with a Q, then the first thing is that
Filename = "Q"
Then the second thing, if it is a number like you show, then
Filename = Filename & NewNumber
Then if it ends in a two letter combination
Filename = Filename & TwoLetters
And finally, if it will end with ".xls" then
Filename = Filename & ".xls"

Now, all of those could be in one line, if you already have all of the
information available, or split up as necessary if you need to capture the
data for each piece.

So it could be:
Filename = "Q" & NewNumber & TwoLetters & ".xls"
(Also, I may have inappropriately used the '&' and it may need to be a '+'
but, at least by splitting it up initially, you can test if that is correct.)

Now, if the new filename is dependent not only on the "form" used but also
the last file saved by that form, then you would need to pull all of the
filenames from the directory that match the filename construct for the
particular form, sort the list according to your numbering sequence, or just
search through the list until you have reached the end, and every occurrence
found that is "larger" than the last is the most recent name. Once the most
recent name is found, then increment to the next and set the filename as
above and you can still use the code that has been provided to help you out.

Short of it.. The filename is merely a string... You need to construct your
filename string, and then append it to the full path. I think there was some
other help provided that did the save "automatically" without prompting the
user for the filename. It involved disabling events.

Help out any?

"Aaron Howe" wrote:

I think we may be talking at cross purposes here. If I refer back to my
original question, I was asking how the code would accomodate a filename
which changes depending on how the form is filled out.

Therefore the filepath will always be \\server\fodler\subfolder
but the filename could be
Q12345AB
Q12346AB
Q12347AK
etc etc, and will be different everytime, therefore creating a unique file
for each unique form entry

"keepITcool" wrote:



i posted "pseudo code" illustrating
the use of chdrive/chdir before calling the dialog.
which was what you were asking.

if you wrap it in a sub make sfile an argument
(and make the filefilter more flexible.
'in your form..
Sub CallerinUserform()
Call SaveToMYFolder(txtfilename.Text)
End Sub

'in the forms codemodule or a normal module
Sub SaveToMYFolder(sFile$)
Dim sPath$, sFull$
sPath = "c:\"
ChDrive sPath
ChDir sPath
sFull = Application.GetSaveAsFilename(sFile, ",*." & Right$(sFile, 3))
'blah blah

End Sub
--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Aaron Howe wrote :

This works where a contigious name should be given, but the name will
change depending on how the form is filled out... so how do I adapt:

sFull = Application.GetSaveAsFilename(sFile, "CSV files,*.csv")

to make it the pre-defined name from the string?

"keepITcool" wrote:

Aaron, try:

Sub foo()
Dim sPath$, sFile$, sFull$
sPath = "c:\windows\"
sFile = "text.csv"

ChDrive sPath
ChDir sPath
sFull = Application.GetSaveAsFilename(sFile, "CSV files,*.csv")


End Sub




--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Aaron Howe wrote :

I have been trying to enter a code into my sheet which will save
the file to a directory on the network using a pre-defined file
name. My filename is a declared string, and is working properly.
I can get as far as:

* Getting the Save As dialogue in the right folder, but with no
filename * Getting the Save As dialogue to show a name, not the
right one, in the last place I saved
* Getting the Save As dialogue to show the completely wrong name
in the right folder.

Assuming I wanted to do this using the GetSaveAsFilename option,
how would I do it? And how would it differ if I wanted an
automatic save where the user didn't have the prompt? The end
result would have to be:

\\server\folder\subfolder\filenamefromstring.xls

Whichever way it was done...!

For an unmapped network drive:

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

Sub SetUNCPath(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub


Sub PickUNCfile()
On Error GoTo ErrHandler
sName = "filenamefromstring.xls"
SetUNCPath "\\server\folder\subfolder\"
fname = Application.GetSaveAsFilename(InitialFilename:=sNa me, _
FileFilter:="Excel Files (*.xls),*.xls")
Exit Sub
ErrHandler:
MsgBox "Couldn't set path"
End Sub

--
Regards,
Tom Ogilvy


"Aaron Howe" wrote in message
...
Thanks GB, that was the direction I was heading for (see thread above re
creation of the field that will be the filename! IT see bottom of this
thread, first paragraph explained that).

So now I need to get that filename into the Save As dialogue (probably the
easiest way), but by the looks of it that's not going to happen. I think
the
best I can hope for is to get the variable of the filename and enter it
into
the clipboard, and maybe end up using sendkeys to insert it. Ugly but
effective I guess...

"GB" wrote:

First of all you need to know your convention for naming the file. If
the
path is always the same, but the filename is different, then you would
need
to determine what the "next" filename will be. Once you know what it
should
be, you can construct it by "piecing" the text together. So if it will
always start with a Q, then the first thing is that
Filename = "Q"
Then the second thing, if it is a number like you show, then
Filename = Filename & NewNumber
Then if it ends in a two letter combination
Filename = Filename & TwoLetters
And finally, if it will end with ".xls" then
Filename = Filename & ".xls"

Now, all of those could be in one line, if you already have all of the
information available, or split up as necessary if you need to capture
the
data for each piece.

So it could be:
Filename = "Q" & NewNumber & TwoLetters & ".xls"
(Also, I may have inappropriately used the '&' and it may need to be a
'+'
but, at least by splitting it up initially, you can test if that is
correct.)

Now, if the new filename is dependent not only on the "form" used but
also
the last file saved by that form, then you would need to pull all of the
filenames from the directory that match the filename construct for the
particular form, sort the list according to your numbering sequence, or
just
search through the list until you have reached the end, and every
occurrence
found that is "larger" than the last is the most recent name. Once the
most
recent name is found, then increment to the next and set the filename as
above and you can still use the code that has been provided to help you
out.

Short of it.. The filename is merely a string... You need to construct
your
filename string, and then append it to the full path. I think there was
some
other help provided that did the save "automatically" without prompting
the
user for the filename. It involved disabling events.

Help out any?

"Aaron Howe" wrote:

I think we may be talking at cross purposes here. If I refer back to
my
original question, I was asking how the code would accomodate a
filename
which changes depending on how the form is filled out.

Therefore the filepath will always be \\server\fodler\subfolder
but the filename could be
Q12345AB
Q12346AB
Q12347AK
etc etc, and will be different everytime, therefore creating a unique
file
for each unique form entry

"keepITcool" wrote:



i posted "pseudo code" illustrating
the use of chdrive/chdir before calling the dialog.
which was what you were asking.

if you wrap it in a sub make sfile an argument
(and make the filefilter more flexible.
'in your form..
Sub CallerinUserform()
Call SaveToMYFolder(txtfilename.Text)
End Sub

'in the forms codemodule or a normal module
Sub SaveToMYFolder(sFile$)
Dim sPath$, sFull$
sPath = "c:\"
ChDrive sPath
ChDir sPath
sFull = Application.GetSaveAsFilename(sFile, ",*." & Right$(sFile,
3))
'blah blah

End Sub
--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Aaron Howe wrote :

This works where a contigious name should be given, but the name
will
change depending on how the form is filled out... so how do I
adapt:

sFull = Application.GetSaveAsFilename(sFile, "CSV files,*.csv")

to make it the pre-defined name from the string?

"keepITcool" wrote:

Aaron, try:

Sub foo()
Dim sPath$, sFile$, sFull$
sPath = "c:\windows\"
sFile = "text.csv"

ChDrive sPath
ChDir sPath
sFull = Application.GetSaveAsFilename(sFile, "CSV files,*.csv")


End Sub




--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Aaron Howe wrote :

I have been trying to enter a code into my sheet which will
save
the file to a directory on the network using a pre-defined
file
name. My filename is a declared string, and is working
properly.
I can get as far as:

* Getting the Save As dialogue in the right folder, but with
no
filename * Getting the Save As dialogue to show a name, not
the
right one, in the last place I saved
* Getting the Save As dialogue to show the completely wrong
name
in the right folder.

Assuming I wanted to do this using the GetSaveAsFilename
option,
how would I do it? And how would it differ if I wanted an
automatic save where the user didn't have the prompt? The end
result would have to be:

\\server\folder\subfolder\filenamefromstring.xls

Whichever way it was done...!

Not sure where you are going with the Sheet1 stuff, but

Sub ABC()
strPathandFilename = "C:\Data6\AATEST.xls"
Sheet1.SaveAs Filename:=strPathandFilename
End Sub

saved the whole workbook for me. If you are only trying to save a single
sheet, then this isn't the solution.

If you are trying to save the whole workbook, then no need to use Sheet as
the top object. Use ActiveWorkbook, thisworkbook or an object variable
that references the workbook to be saved.

--
Regards,
Tom Ogilvy


"GB" wrote in message
...
Well I thought that keepITcool had identified how to get the filename into
the SaveAs dialogue, by having a routine that would open the saveas
dialogue
box with the filename in it. I think that the response provided by
keepITcool was a step down the line from your original question, and that
as
you indicated below, I got you to creating the filename, however you have
also indicated that you can't get the dialogue to show the filename/save
the
document called what you want.

Way I see it, once you have created the filename as a string. Send it to
SavetoMyFolder subroutine, which has as a path
"\\server\folder\sub-folder\"
and then you could append the filename to the end of the path and have

sFull = application.GetSaveAsFilename(sFile, ",*." & Right$(sFile, 3))

I'm not sure off the top of my head, how to tell it to just save without
prompting the user, but something in this process should get you to where
you
want. Actually it sounds like if you already know where the file is
supposed
to end up, and you have constructed the filename the way you want it to be
by
programmatically establishing the filename, then you should be able to
simply
use SaveAs instead of GetSaveAsFilename.

Take a look at the SaveAs Method, it can be used to save a chart,
worksheet,
or workbook and will save that applicable item with the filename
transferred.
So say you want to save the worksheet that is given the Name Sheet1 (Not
the
same as the tab name you see when looking at the tabs in the workbook)
then
you could do

Sheet1.SaveAs Filename:=strPathAndFileName

And this will save Sheet1 as whatever the value of strPathAndFileName is
Or if you know that the name of the Sheet will stay constant as shown on
the
tab of the workbook, you can perform
Worksheets("Our data Sheet").SaveAs Filename:=strPathAndFileName

And if you are intentionally designing this to be able to replace
particular
files, I know that it is possible to cause the SaveAs to not prompt the
user,
and instead just write over it. But I have forgotten the method to do
that,
it has to do with prompts and disabling them.

Best of luck, you should be able to perform the action(s) that you are
attempting.


"Aaron Howe" wrote:

Thanks GB, that was the direction I was heading for (see thread above re
creation of the field that will be the filename! IT see bottom of this
thread, first paragraph explained that).

So now I need to get that filename into the Save As dialogue (probably
the
easiest way), but by the looks of it that's not going to happen. I
think the
best I can hope for is to get the variable of the filename and enter it
into
the clipboard, and maybe end up using sendkeys to insert it. Ugly but
effective I guess...

"GB" wrote:

First of all you need to know your convention for naming the file. If
the
path is always the same, but the filename is different, then you would
need
to determine what the "next" filename will be. Once you know what it
should
be, you can construct it by "piecing" the text together. So if it
will
always start with a Q, then the first thing is that
Filename = "Q"
Then the second thing, if it is a number like you show, then
Filename = Filename & NewNumber
Then if it ends in a two letter combination
Filename = Filename & TwoLetters
And finally, if it will end with ".xls" then
Filename = Filename & ".xls"

Now, all of those could be in one line, if you already have all of the
information available, or split up as necessary if you need to capture
the
data for each piece.

So it could be:
Filename = "Q" & NewNumber & TwoLetters & ".xls"
(Also, I may have inappropriately used the '&' and it may need to be a
'+'
but, at least by splitting it up initially, you can test if that is
correct.)

Now, if the new filename is dependent not only on the "form" used but
also
the last file saved by that form, then you would need to pull all of
the
filenames from the directory that match the filename construct for the
particular form, sort the list according to your numbering sequence,
or just
search through the list until you have reached the end, and every
occurrence
found that is "larger" than the last is the most recent name. Once
the most
recent name is found, then increment to the next and set the filename
as
above and you can still use the code that has been provided to help
you out.

Short of it.. The filename is merely a string... You need to
construct your
filename string, and then append it to the full path. I think there
was some
other help provided that did the save "automatically" without
prompting the
user for the filename. It involved disabling events.

Help out any?

"Aaron Howe" wrote:

I think we may be talking at cross purposes here. If I refer back
to my
original question, I was asking how the code would accomodate a
filename
which changes depending on how the form is filled out.

Therefore the filepath will always be \\server\fodler\subfolder
but the filename could be
Q12345AB
Q12346AB
Q12347AK
etc etc, and will be different everytime, therefore creating a
unique file
for each unique form entry

"keepITcool" wrote:



i posted "pseudo code" illustrating
the use of chdrive/chdir before calling the dialog.
which was what you were asking.

if you wrap it in a sub make sfile an argument
(and make the filefilter more flexible.
'in your form..
Sub CallerinUserform()
Call SaveToMYFolder(txtfilename.Text)
End Sub

'in the forms codemodule or a normal module
Sub SaveToMYFolder(sFile$)
Dim sPath$, sFull$
sPath = "c:\"
ChDrive sPath
ChDir sPath
sFull = Application.GetSaveAsFilename(sFile, ",*." & Right$(sFile,
3))
'blah blah

End Sub
--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Aaron Howe wrote :

This works where a contigious name should be given, but the name
will
change depending on how the form is filled out... so how do I
adapt:

sFull = Application.GetSaveAsFilename(sFile, "CSV files,*.csv")

to make it the pre-defined name from the string?

"keepITcool" wrote:

Aaron, try:

Sub foo()
Dim sPath$, sFile$, sFull$
sPath = "c:\windows\"
sFile = "text.csv"

ChDrive sPath
ChDir sPath
sFull = Application.GetSaveAsFilename(sFile, "CSV
files,*.csv")


End Sub




--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Aaron Howe wrote :

I have been trying to enter a code into my sheet which will
save
the file to a directory on the network using a pre-defined
file
name. My filename is a declared string, and is working
properly.
I can get as far as:

* Getting the Save As dialogue in the right folder, but with
no
filename * Getting the Save As dialogue to show a name, not
the
right one, in the last place I saved
* Getting the Save As dialogue to show the completely wrong
name
in the right folder.

Assuming I wanted to do this using the GetSaveAsFilename
option,
how would I do it? And how would it differ if I wanted an
automatic save where the user didn't have the prompt? The
end
result would have to be:

\\server\folder\subfolder\filenamefromstring.xls

Whichever way it was done...!

This may be a little late for your purposes, but this worked for me.

Sub ResAlertForm_SaveAs()
Application.ScreenUpdating = False

On Error GoTo ErrRoutine
Dim MyPath As String
Dim MyDirName As String
Dim SuggName As String
Dim NewDir As String

' customize this row with the path you want to scan
MyPath = "Z:\Agent Forms\Reservation Alert Forms"
MyDirName = Sheets("Reservation Alert Form").Range("H8") 'name of resort

' the next intruction tries to create a new directory.
' If a directory by the specified name already exists, it
' returns an error, number 75. This error is managed by
' the ErrRoutine block.
MkDir (MyPath & "\" & MyDirName)
NewDir = MyPath & "\" & MyDirName

' creates the file name (dd_mm_yyyy_xxxxxxRCNA.xls)
SuggName = Sheets("Reservation Alert Form").Range("D13") _
& ("_") & Sheets("Reservation Alert Form").Range("F13") _
& ("_") & Sheets("Reservation Alert Form").Range("H13") _
& ("_") & Sheets("Reservation Alert Form").Range("D21") _
& ".XLS"

'Changes Current Directory
ChDrive NewDir
ChDir NewDir

' Saves the copy of the form to the
ActiveWorkbook.SaveAs (NewDir & "\" & SuggName)
ExitRoutine:

Call ResAlertForm_Email
Exit Sub
ErrRoutine:
' run-time error 75 - Path/File Access error
If Err.Number = 75 Then
Resume Next
Else
MsgBox Err.Number & ": " & Err.Description
Exit Sub
End If

End Sub

"Aaron Howe" wrote:

I have been trying to enter a code into my sheet which will save the file to
a directory on the network using a pre-defined file name. My filename is a
declared string, and is working properly. I can get as far as:

* Getting the Save As dialogue in the right folder, but with no filename
* Getting the Save As dialogue to show a name, not the right one, in the
last place I saved
* Getting the Save As dialogue to show the completely wrong name in the
right folder.

Assuming I wanted to do this using the GetSaveAsFilename option, how would I
do it? And how would it differ if I wanted an automatic save where the user
didn't have the prompt? The end result would have to be:

\\server\folder\subfolder\filenamefromstring.xls

Whichever way it was done...!