Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom save as pathname and filename
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...! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom save as pathname and filename
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom save as pathname and filename
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...! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom save as pathname and filename
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...! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom save as pathname and filename
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...! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom save as pathname and filename
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...! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom save as pathname and filename
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...! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom save as pathname and filename
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...! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom save as pathname and filename
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...! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell("filename") doesn't update to new filename when do save as. | Excel Worksheet Functions | |||
save as different filename | Excel Programming | |||
Save Filename | Excel Programming | |||
save as filename | Excel Programming | |||
LoadPicture Pathname or no Pathname | Excel Programming |