If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
set default directory for saving files
I have created a new template in Excel, and would like each new file based on
this template to be saved to a particular directory. Any ideas? Many thanks. |
#2
|
|||
|
|||
set default directory for saving files
Maybe...
Provide a dedicated macro that saves to that folder. Then make sure the users know to run that macro when they're saving. Vibeke wrote: I have created a new template in Excel, and would like each new file based on this template to be saved to a particular directory. Any ideas? Many thanks. -- Dave Peterson |
#3
|
|||
|
|||
set default directory for saving files
That's a good idea that should work, except I'm using the 'record macro'
wizard (programming not being one of my stronger skills). I can't find a way to stop recording at the point of entering a filename for the template, i.e. in recording the macro would develop as follows: Select File Menu Select Save as Select the desired default directory Enter new file name (this is the hitch, since each new file name would be unique) Click save End Macro Am I overlooking something obvious? Many thanks for your time! "Dave Peterson" wrote: Maybe... Provide a dedicated macro that saves to that folder. Then make sure the users know to run that macro when they're saving. Vibeke wrote: I have created a new template in Excel, and would like each new file based on this template to be saved to a particular directory. Any ideas? Many thanks. -- Dave Peterson |
#4
|
|||
|
|||
set default directory for saving files
How about something like:
Option Explicit Private Declare Function SetCurrentDirectoryA Lib _ "kernel32" (ByVal lpPathName As String) As Long Sub ChDirNet(szPath As String) Dim lReturn As Long lReturn = SetCurrentDirectoryA(szPath) If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path." End Sub Sub testme() Dim myNewFolder As String Dim CurFolder As String Dim UserFileName As Variant Dim UserFolder As String Dim TestStr As String Dim resp As Long If ActiveWorkbook.Path = "" Then 'keep going, it was based on a template (*.xlt) and hasn't been saved Else 'get out, it's already been saved Exit Sub End If myNewFolder = "\\C:\my documents\excel" CurFolder = CurDir On Error Resume Next ChDirNet myNewFolder If Err.Number 0 Then 'what should happen MsgBox "Design error--Folder not found" & vbLf & _ "Contact Vibeke right away, please." Err.Clear Exit Sub End If On Error GoTo 0 UserFileName = Application.GetSaveAsFilename _ (InitialFileName:="Please Stay in this folder!", _ filefilter:="Excel Files, *.xls") ChDrive CurFolder ChDir CurFolder If UserFileName = False Then 'user hit cancel Exit Sub End If UserFolder = Left(UserFileName, InStrRev(stringcheck:=UserFileName, _ stringmatch:="\", Start:=-1, compa=vbTextCompare) - 1) If LCase(UserFolder) = LCase(myNewFolder) Then 'ok Else Beep MsgBox "File NOT Saved!" & vbLf & vbLf _ & "Please choose a filename in: " & vbLf & myNewFolder Exit Sub End If TestStr = "" On Error Resume Next TestStr = Dir(UserFileName) On Error GoTo 0 If TestStr = "" Then 'file doesn't exist 'don't prompt about overwriting Else 'give them a choice resp = MsgBox(Prompt:="Overwrite existing file?", Buttons:=vbYesNo) If resp = vbNo Then MsgBox "File not saved" Exit Sub End If End If Application.DisplayAlerts = False 'stop overwrite prompt Application.EnableEvents = False 'get by that workbook_beforesave event On Error Resume Next 'just in case ActiveWorkbook.SaveAs Filename:=UserFileName, _ FileFormat:=xlWorkbookNormal If Err.Number 0 Then MsgBox "File not saved!" & vbLf & _ Err.Number & vbLf & Err.Description Err.Clear Else MsgBox "Saved to:" & vbLf & UserFileName End If Application.EnableEvents = True Application.DisplayAlerts = True End Sub This actually prompts the user to see if they want to overwrite the existing file. It may be easier to allow the users to do this than explain why they can't update an existing file. But if you really don't want them to have this ability, change this section: If TestStr = "" Then 'file doesn't exist 'don't prompt about overwriting Else 'give them a choice resp = MsgBox(Prompt:="Overwrite existing file?", Buttons:=vbYesNo) If resp = vbNo Then MsgBox "File not saved" Exit Sub End If End If to If TestStr = "" Then 'file doesn't exist 'don't prompt about overwriting Else Msgbox "That name already exists!" exit sub End if ======= And to try to stop the users from hitting the File|SaveAs dialog, put this in the ThisWorkbook module: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Cancel = True MsgBox "Please use button to save this file" End Sub Be aware that if the user opens the workbook with macros disabled, then all this fails. And if they disable events, it'll fail, too. Vibeke wrote: That's a good idea that should work, except I'm using the 'record macro' wizard (programming not being one of my stronger skills). I can't find a way to stop recording at the point of entering a filename for the template, i.e. in recording the macro would develop as follows: Select File Menu Select Save as Select the desired default directory Enter new file name (this is the hitch, since each new file name would be unique) Click save End Macro Am I overlooking something obvious? Many thanks for your time! "Dave Peterson" wrote: Maybe... Provide a dedicated macro that saves to that folder. Then make sure the users know to run that macro when they're saving. Vibeke wrote: I have created a new template in Excel, and would like each new file based on this template to be saved to a particular directory. Any ideas? Many thanks. -- Dave Peterson -- Dave Peterson |
#5
|
|||
|
|||
set default directory for saving files
Dave,
Thank you very much for your attention to my query. Unfortunately my understanding of writing macros is extremely limited (as in, I don't even know where to start!), so I cannot even offer the satisfaction of letting you know this worked. But I do very much appreciate your time. Regards, Vibeke "Dave Peterson" wrote: How about something like: Option Explicit Private Declare Function SetCurrentDirectoryA Lib _ "kernel32" (ByVal lpPathName As String) As Long Sub ChDirNet(szPath As String) Dim lReturn As Long lReturn = SetCurrentDirectoryA(szPath) If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path." End Sub Sub testme() Dim myNewFolder As String Dim CurFolder As String Dim UserFileName As Variant Dim UserFolder As String Dim TestStr As String Dim resp As Long If ActiveWorkbook.Path = "" Then 'keep going, it was based on a template (*.xlt) and hasn't been saved Else 'get out, it's already been saved Exit Sub End If myNewFolder = "\\C:\my documents\excel" CurFolder = CurDir On Error Resume Next ChDirNet myNewFolder If Err.Number 0 Then 'what should happen MsgBox "Design error--Folder not found" & vbLf & _ "Contact Vibeke right away, please." Err.Clear Exit Sub End If On Error GoTo 0 UserFileName = Application.GetSaveAsFilename _ (InitialFileName:="Please Stay in this folder!", _ filefilter:="Excel Files, *.xls") ChDrive CurFolder ChDir CurFolder If UserFileName = False Then 'user hit cancel Exit Sub End If UserFolder = Left(UserFileName, InStrRev(stringcheck:=UserFileName, _ stringmatch:="\", Start:=-1, compa=vbTextCompare) - 1) If LCase(UserFolder) = LCase(myNewFolder) Then 'ok Else Beep MsgBox "File NOT Saved!" & vbLf & vbLf _ & "Please choose a filename in: " & vbLf & myNewFolder Exit Sub End If TestStr = "" On Error Resume Next TestStr = Dir(UserFileName) On Error GoTo 0 If TestStr = "" Then 'file doesn't exist 'don't prompt about overwriting Else 'give them a choice resp = MsgBox(Prompt:="Overwrite existing file?", Buttons:=vbYesNo) If resp = vbNo Then MsgBox "File not saved" Exit Sub End If End If Application.DisplayAlerts = False 'stop overwrite prompt Application.EnableEvents = False 'get by that workbook_beforesave event On Error Resume Next 'just in case ActiveWorkbook.SaveAs Filename:=UserFileName, _ FileFormat:=xlWorkbookNormal If Err.Number 0 Then MsgBox "File not saved!" & vbLf & _ Err.Number & vbLf & Err.Description Err.Clear Else MsgBox "Saved to:" & vbLf & UserFileName End If Application.EnableEvents = True Application.DisplayAlerts = True End Sub This actually prompts the user to see if they want to overwrite the existing file. It may be easier to allow the users to do this than explain why they can't update an existing file. But if you really don't want them to have this ability, change this section: If TestStr = "" Then 'file doesn't exist 'don't prompt about overwriting Else 'give them a choice resp = MsgBox(Prompt:="Overwrite existing file?", Buttons:=vbYesNo) If resp = vbNo Then MsgBox "File not saved" Exit Sub End If End If to If TestStr = "" Then 'file doesn't exist 'don't prompt about overwriting Else Msgbox "That name already exists!" exit sub End if ======= And to try to stop the users from hitting the File|SaveAs dialog, put this in the ThisWorkbook module: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Cancel = True MsgBox "Please use button to save this file" End Sub Be aware that if the user opens the workbook with macros disabled, then all this fails. And if they disable events, it'll fail, too. Vibeke wrote: That's a good idea that should work, except I'm using the 'record macro' wizard (programming not being one of my stronger skills). I can't find a way to stop recording at the point of entering a filename for the template, i.e. in recording the macro would develop as follows: Select File Menu Select Save as Select the desired default directory Enter new file name (this is the hitch, since each new file name would be unique) Click save End Macro Am I overlooking something obvious? Many thanks for your time! "Dave Peterson" wrote: Maybe... Provide a dedicated macro that saves to that folder. Then make sure the users know to run that macro when they're saving. Vibeke wrote: I have created a new template in Excel, and would like each new file based on this template to be saved to a particular directory. Any ideas? Many thanks. -- Dave Peterson -- Dave Peterson |
#6
|
|||
|
|||
set default directory for saving files
You may want to read David McRitchie's notes:
http://www.mvps.org/dmcritchie/excel/getstarted.htm Vibeke wrote: Dave, Thank you very much for your attention to my query. Unfortunately my understanding of writing macros is extremely limited (as in, I don't even know where to start!), so I cannot even offer the satisfaction of letting you know this worked. But I do very much appreciate your time. Regards, Vibeke "Dave Peterson" wrote: How about something like: Option Explicit Private Declare Function SetCurrentDirectoryA Lib _ "kernel32" (ByVal lpPathName As String) As Long Sub ChDirNet(szPath As String) Dim lReturn As Long lReturn = SetCurrentDirectoryA(szPath) If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path." End Sub Sub testme() Dim myNewFolder As String Dim CurFolder As String Dim UserFileName As Variant Dim UserFolder As String Dim TestStr As String Dim resp As Long If ActiveWorkbook.Path = "" Then 'keep going, it was based on a template (*.xlt) and hasn't been saved Else 'get out, it's already been saved Exit Sub End If myNewFolder = "\\C:\my documents\excel" CurFolder = CurDir On Error Resume Next ChDirNet myNewFolder If Err.Number 0 Then 'what should happen MsgBox "Design error--Folder not found" & vbLf & _ "Contact Vibeke right away, please." Err.Clear Exit Sub End If On Error GoTo 0 UserFileName = Application.GetSaveAsFilename _ (InitialFileName:="Please Stay in this folder!", _ filefilter:="Excel Files, *.xls") ChDrive CurFolder ChDir CurFolder If UserFileName = False Then 'user hit cancel Exit Sub End If UserFolder = Left(UserFileName, InStrRev(stringcheck:=UserFileName, _ stringmatch:="\", Start:=-1, compa=vbTextCompare) - 1) If LCase(UserFolder) = LCase(myNewFolder) Then 'ok Else Beep MsgBox "File NOT Saved!" & vbLf & vbLf _ & "Please choose a filename in: " & vbLf & myNewFolder Exit Sub End If TestStr = "" On Error Resume Next TestStr = Dir(UserFileName) On Error GoTo 0 If TestStr = "" Then 'file doesn't exist 'don't prompt about overwriting Else 'give them a choice resp = MsgBox(Prompt:="Overwrite existing file?", Buttons:=vbYesNo) If resp = vbNo Then MsgBox "File not saved" Exit Sub End If End If Application.DisplayAlerts = False 'stop overwrite prompt Application.EnableEvents = False 'get by that workbook_beforesave event On Error Resume Next 'just in case ActiveWorkbook.SaveAs Filename:=UserFileName, _ FileFormat:=xlWorkbookNormal If Err.Number 0 Then MsgBox "File not saved!" & vbLf & _ Err.Number & vbLf & Err.Description Err.Clear Else MsgBox "Saved to:" & vbLf & UserFileName End If Application.EnableEvents = True Application.DisplayAlerts = True End Sub This actually prompts the user to see if they want to overwrite the existing file. It may be easier to allow the users to do this than explain why they can't update an existing file. But if you really don't want them to have this ability, change this section: If TestStr = "" Then 'file doesn't exist 'don't prompt about overwriting Else 'give them a choice resp = MsgBox(Prompt:="Overwrite existing file?", Buttons:=vbYesNo) If resp = vbNo Then MsgBox "File not saved" Exit Sub End If End If to If TestStr = "" Then 'file doesn't exist 'don't prompt about overwriting Else Msgbox "That name already exists!" exit sub End if ======= And to try to stop the users from hitting the File|SaveAs dialog, put this in the ThisWorkbook module: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Cancel = True MsgBox "Please use button to save this file" End Sub Be aware that if the user opens the workbook with macros disabled, then all this fails. And if they disable events, it'll fail, too. Vibeke wrote: That's a good idea that should work, except I'm using the 'record macro' wizard (programming not being one of my stronger skills). I can't find a way to stop recording at the point of entering a filename for the template, i.e. in recording the macro would develop as follows: Select File Menu Select Save as Select the desired default directory Enter new file name (this is the hitch, since each new file name would be unique) Click save End Macro Am I overlooking something obvious? Many thanks for your time! "Dave Peterson" wrote: Maybe... Provide a dedicated macro that saves to that folder. Then make sure the users know to run that macro when they're saving. Vibeke wrote: I have created a new template in Excel, and would like each new file based on this template to be saved to a particular directory. Any ideas? Many thanks. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|