Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As Dialog
Larry,
just use a combination of ChDrive and ChDir as follows: Sub SaveInDir() ChDrive "c:" ChDir "c:\Temp" strFile = Application.Dialogs(xlDialogSaveAs).Show End Sub Robin Hammond www.enhanceddatasystems.com "Larry Dodd" wrote in message ... I am trying to put some code in the BeforeSave event so that when the user tries to save the workbook they will be prompted with the Save As dialog with a different file name so they do not save over the original file. I am using the SafeFileAs function and the Save As dialog does appear but the initial directory is set to My Documents and I would like it to be set to something else. Below is the code that I am using. Can anyone tell me how I can accomplish this? Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim DialogResult As String Dim UserFileName As String Dim sAppPath As String Application.EnableEvents = False sAppPath = ActiveWorkbook.Path & "\Bone Match 5.0 Template Directory\Bone Match 5.0 History\BoneMatch.xls" DialogResult = Application.GetSaveAsFilename(InitialFileName:=sAp pPath, FileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls") If DialogResult = "False" Then Application.EnableEvents = True Cancel = True Exit Sub End If UserFileName = CStr(DialogResult) Workbook.SaveAs (UserFileName) Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As Dialog
Thank you for responding but that did not work for me. It does open the
Save As dialog but it is in the folder that the original file was opened from. This is the code that I used. Please let me know if there is something else that I need to do. Also when I call the SaveAs function the file is saved to the location that I selected but then Excel gives me an error message and closes. If you know anything about that please let me know Application.EnableEvents = False ChDir (ActiveWorkbook.Path) ChDir ("Bone Match Template Directory") ChDir ("Bone Match History") strFile = Application.Dialogs(xlDialogSaveAs).Show Application.EnableEvents = True *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As Dialog
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean) Dim DialogResult As String Dim UserFileName As String Dim sAppPath As String Dim sFile as String On Error goto ErrHandler Application.EnableEvents = False Cancel = True sAppPath = ActiveWorkbook.Path & _ "\Bone Match 5.0 Template Directory\" & _ "Bone Match 5.0 History\" sFile = "BoneMatch.xls" chDrive sAppPath chdir sAppPath DialogResult = Application.GetSaveAsFilename( _ InitialFileName:=sAppPath & sFile, _ FileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls") If DialogResult = "False" Then Application.EnableEvents = True Exit Sub End If If lcase(ThisWorkbook.FullName) = lcase(DialogResult) Then msgbox "Must change name. Save Cancelled" Application.EnableEvents = True Exit Sub End if Workbook.SaveAs DialogResult ErrHandler: Application.EnableEvents = True End Sub This assumes the directory you selected exists. In any event, you always want to set Cancel = True -- Regards, Tom Ogilvy "Larry Dodd" wrote in message ... I am trying to put some code in the BeforeSave event so that when the user tries to save the workbook they will be prompted with the Save As dialog with a different file name so they do not save over the original file. I am using the SafeFileAs function and the Save As dialog does appear but the initial directory is set to My Documents and I would like it to be set to something else. Below is the code that I am using. Can anyone tell me how I can accomplish this? Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim DialogResult As String Dim UserFileName As String Dim sAppPath As String Application.EnableEvents = False sAppPath = ActiveWorkbook.Path & "\Bone Match 5.0 Template Directory\Bone Match 5.0 History\BoneMatch.xls" DialogResult = Application.GetSaveAsFilename(InitialFileName:=sAp pPath, FileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls") If DialogResult = "False" Then Application.EnableEvents = True Cancel = True Exit Sub End If UserFileName = CStr(DialogResult) Workbook.SaveAs (UserFileName) Application.EnableEvents = True End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As Dialog
Hi Nick,
That is interesting. It actually works better than that. I tried C:\MyTest, and it went to C: as you said. I then tried C:\Mytest\Mytest, and it went to c:\MyTest ! But best of all, I then tried C:\Mytest\Mytest\ and it went to c:\MyTest\Mytest. A good find. -- HTH RP (remove nothere from the email address if mailing direct) "Nick Hodge" wrote in message ... Larry Can you not explicitly pass the path to the GetSaveAsFilename method. I used the code below and it offered the save as dialog with my root 'C' as the 'preset' path Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim sFileName As String If SaveAsUI Then Exit Sub With Application .EnableEvents = False sFileName = .GetSaveAsFilename("C:\" & Me.Name) Me.SaveAs Filename:=sFileName .EnableEvents = True End With End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Larry Dodd" wrote in message ... Thank you for responding but that did not work for me. It does open the Save As dialog but it is in the folder that the original file was opened from. This is the code that I used. Please let me know if there is something else that I need to do. Also when I call the SaveAs function the file is saved to the location that I selected but then Excel gives me an error message and closes. If you know anything about that please let me know Application.EnableEvents = False ChDir (ActiveWorkbook.Path) ChDir ("Bone Match Template Directory") ChDir ("Bone Match History") strFile = Application.Dialogs(xlDialogSaveAs).Show Application.EnableEvents = True *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As Dialog
Just to add - at least in xl97 -
If the path is not valid, it does not raise an error. It goes somewhere else. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... Hi Nick, That is interesting. It actually works better than that. I tried C:\MyTest, and it went to C: as you said. I then tried C:\Mytest\Mytest, and it went to c:\MyTest ! But best of all, I then tried C:\Mytest\Mytest\ and it went to c:\MyTest\Mytest. A good find. -- HTH RP (remove nothere from the email address if mailing direct) "Nick Hodge" wrote in message ... Larry Can you not explicitly pass the path to the GetSaveAsFilename method. I used the code below and it offered the save as dialog with my root 'C' as the 'preset' path Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim sFileName As String If SaveAsUI Then Exit Sub With Application .EnableEvents = False sFileName = .GetSaveAsFilename("C:\" & Me.Name) Me.SaveAs Filename:=sFileName .EnableEvents = True End With End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Larry Dodd" wrote in message ... Thank you for responding but that did not work for me. It does open the Save As dialog but it is in the folder that the original file was opened from. This is the code that I used. Please let me know if there is something else that I need to do. Also when I call the SaveAs function the file is saved to the location that I selected but then Excel gives me an error message and closes. If you know anything about that please let me know Application.EnableEvents = False ChDir (ActiveWorkbook.Path) ChDir ("Bone Match Template Directory") ChDir ("Bone Match History") strFile = Application.Dialogs(xlDialogSaveAs).Show Application.EnableEvents = True *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save As Dialog
I am trying to put some code in the BeforeSave event so that when the user
tries to save the workbook they will be prompted with the Save As dialog with a different file name so they do not save over the original file. I am using the SafeFileAs function and the Save As dialog does appear but the initial directory is set to My Documents and I would like it to be set to something else. Below is the code that I am using. Can anyone tell me how I can accomplish this? Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim DialogResult As String Dim UserFileName As String Dim sAppPath As String Application.EnableEvents = False sAppPath = ActiveWorkbook.Path & "\Bone Match 5.0 Template Directory\Bone Match 5.0 History\BoneMatch.xls" DialogResult = Application.GetSaveAsFilename(InitialFileName:=sAp pPath, FileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls") If DialogResult = "False" Then Application.EnableEvents = True Cancel = True Exit Sub End If UserFileName = CStr(DialogResult) Workbook.SaveAs (UserFileName) Application.EnableEvents = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save as dialog | Excel Discussion (Misc queries) | |||
how to get disk icon on save button of save as dialog like 2000 | Excel Discussion (Misc queries) | |||
Changing Save As Type in Save as dialog box | Excel Programming | |||
Save As Dialog | Excel Programming | |||
Save As Dialog | Excel Programming |