View Single Post
  #9   Report Post  
Posted to microsoft.public.excel.programming
Aaron Howe[_2_] Aaron Howe[_2_] is offline
external usenet poster
 
Posts: 23
Default Custom save as pathname and filename

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...!