Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook.SaveAs
This code did work before.... All I am trying to do is a SaveAs to another directory. I am using Office 2003.... I get no errors but the SaveAs function doesn't rename the current workbook that is open nor does it savea copy to the forlder specified...what gives? Here is my code: Sub SaveAsFile() Dim strDocName As String Const FilePath As String = "H:\Client\" On Error GoTo err_handler ChDrive FilePath ChDir FilePath strDocName = Application.GetSaveAsFilename(filefilter:="*.xls, *.xls") If strDocName < "False" Then 'do not allow it to be stored in the local directory If (Left(strDocName, 33) = "H:\Client\Deliverables Schedules") Then MsgBox "This schedule must be saved to the appropriate folder on H:\Client!", vbCritical, "Wrong Folder" Cancel = True Else ActiveWorkbook.SaveAs strDocName End If End If Exit Sub err_handler: MsgBox Err.Number & " " & Err.Description End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook.SaveAs
I removed the Cancel = True but it did not help
"chemicals" wrote: This code did work before.... All I am trying to do is a SaveAs to another directory. I am using Office 2003.... I get no errors but the SaveAs function doesn't rename the current workbook that is open nor does it savea copy to the forlder specified...what gives? Here is my code: Sub SaveAsFile() Dim strDocName As String Const FilePath As String = "H:\Client\" On Error GoTo err_handler ChDrive FilePath ChDir FilePath strDocName = Application.GetSaveAsFilename(filefilter:="*.xls, *.xls") If strDocName < "False" Then 'do not allow it to be stored in the local directory If (Left(strDocName, 33) = "H:\Client\Deliverables Schedules") Then MsgBox "This schedule must be saved to the appropriate folder on H:\Client!", vbCritical, "Wrong Folder" Else ActiveWorkbook.SaveAs strDocName End If End If Exit Sub err_handler: MsgBox Err.Number & " " & Err.Description End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook.SaveAs
use the following command line to modify the save directory:
Application.DefaultFilePath = FilePath As an alternative, you could also add the file path to the filename captured with the GetSaveAsFilename function. After getting the filename you could do the following: strDocName = FilePath & strDocName -- Kevin Backmann "chemicals" wrote: This code did work before.... All I am trying to do is a SaveAs to another directory. I am using Office 2003.... I get no errors but the SaveAs function doesn't rename the current workbook that is open nor does it savea copy to the forlder specified...what gives? Here is my code: Sub SaveAsFile() Dim strDocName As String Const FilePath As String = "H:\Client\" On Error GoTo err_handler ChDrive FilePath ChDir FilePath strDocName = Application.GetSaveAsFilename(filefilter:="*.xls, *.xls") If strDocName < "False" Then 'do not allow it to be stored in the local directory If (Left(strDocName, 33) = "H:\Client\Deliverables Schedules") Then MsgBox "This schedule must be saved to the appropriate folder on H:\Client!", vbCritical, "Wrong Folder" Cancel = True Else ActiveWorkbook.SaveAs strDocName End If End If Exit Sub err_handler: MsgBox Err.Number & " " & Err.Description End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook.SaveAs
On Wed, 10 Oct 2007 10:51:03 -0700, chemicals wrote:
.... If strDocName < "False" Then .... Why quotes around False? B. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook.SaveAs
If no file name is entered in the Save As dialog box the GetSaveAsFilename
function returns the string "FALSE" as its value. -- Kevin Backmann "Boris" wrote: On Wed, 10 Oct 2007 10:51:03 -0700, chemicals wrote: .... If strDocName < "False" Then .... Why quotes around False? B. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook.SaveAs
If no file name is entered in the Save As dialog box the GetSaveAsFilename
function returns the string "FALSE" as its value. This is sort of correct. If the user cancels out of GetSaveAsFileName, it returns a Boolean False value, which when stored in a String variable becomes the string "False". But you will get the same result if the user enters a file name "False". It is better to store the result of GetSaveAsFileName in a Variant type, test that for False (Boolean, not String -- no quotes) and act accordingly: Dim FName As Variant FName = Application.GetSaveAsFilename() If FName = False Then Debug.Print "User Cancelled" Else Debug.Print "Selected file: " & FName End If -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Kevin B" wrote in message ... If no file name is entered in the Save As dialog box the GetSaveAsFilename function returns the string "FALSE" as its value. -- Kevin Backmann "Boris" wrote: On Wed, 10 Oct 2007 10:51:03 -0700, chemicals wrote: .... If strDocName < "False" Then .... Why quotes around False? B. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook.SaveAs
I had forgotten all about that. Thanks for the heads-up.
One should never underestimate the end-user, the mistake you don't code for is the one they seem to do. -- Kevin Backmann "Chip Pearson" wrote: If no file name is entered in the Save As dialog box the GetSaveAsFilename function returns the string "FALSE" as its value. This is sort of correct. If the user cancels out of GetSaveAsFileName, it returns a Boolean False value, which when stored in a String variable becomes the string "False". But you will get the same result if the user enters a file name "False". It is better to store the result of GetSaveAsFileName in a Variant type, test that for False (Boolean, not String -- no quotes) and act accordingly: Dim FName As Variant FName = Application.GetSaveAsFilename() If FName = False Then Debug.Print "User Cancelled" Else Debug.Print "Selected file: " & FName End If -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Kevin B" wrote in message ... If no file name is entered in the Save As dialog box the GetSaveAsFilename function returns the string "FALSE" as its value. -- Kevin Backmann "Boris" wrote: On Wed, 10 Oct 2007 10:51:03 -0700, chemicals wrote: .... If strDocName < "False" Then .... Why quotes around False? B. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook.SaveAs
Kevin,
I added the code but ended up with the same result..... In the debugger strDocName ="H:\Client\<filename" where <filename is whatever the user types into the SaveAs Dialog. It steps right onto the "ActiveWorkbook.SaveAs strDocName" acting like it was successful yet there is no file and the currently open workbook name has not changed????? "Kevin B" wrote: use the following command line to modify the save directory: Application.DefaultFilePath = FilePath As an alternative, you could also add the file path to the filename captured with the GetSaveAsFilename function. After getting the filename you could do the following: strDocName = FilePath & strDocName -- Kevin Backmann "chemicals" wrote: This code did work before.... All I am trying to do is a SaveAs to another directory. I am using Office 2003.... I get no errors but the SaveAs function doesn't rename the current workbook that is open nor does it savea copy to the forlder specified...what gives? Here is my code: Sub SaveAsFile() Dim strDocName As String Const FilePath As String = "H:\Client\" On Error GoTo err_handler ChDrive FilePath ChDir FilePath strDocName = Application.GetSaveAsFilename(filefilter:="*.xls, *.xls") If strDocName < "False" Then 'do not allow it to be stored in the local directory If (Left(strDocName, 33) = "H:\Client\Deliverables Schedules") Then MsgBox "This schedule must be saved to the appropriate folder on H:\Client!", vbCritical, "Wrong Folder" Cancel = True Else ActiveWorkbook.SaveAs strDocName End If End If Exit Sub err_handler: MsgBox Err.Number & " " & Err.Description End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook.SaveAs
Don't know if it helps but this is the Sub I am calling it from
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI Then SaveAsFile CreateShortCut Cancel = True End If End Sub "chemicals" wrote: Kevin, I added the code but ended up with the same result..... In the debugger strDocName ="H:\Client\<filename" where <filename is whatever the user types into the SaveAs Dialog. It steps right onto the "ActiveWorkbook.SaveAs strDocName" acting like it was successful yet there is no file and the currently open workbook name has not changed????? "Kevin B" wrote: use the following command line to modify the save directory: Application.DefaultFilePath = FilePath As an alternative, you could also add the file path to the filename captured with the GetSaveAsFilename function. After getting the filename you could do the following: strDocName = FilePath & strDocName -- Kevin Backmann "chemicals" wrote: This code did work before.... All I am trying to do is a SaveAs to another directory. I am using Office 2003.... I get no errors but the SaveAs function doesn't rename the current workbook that is open nor does it savea copy to the forlder specified...what gives? Here is my code: Sub SaveAsFile() Dim strDocName As String Const FilePath As String = "H:\Client\" On Error GoTo err_handler ChDrive FilePath ChDir FilePath strDocName = Application.GetSaveAsFilename(filefilter:="*.xls, *.xls") If strDocName < "False" Then 'do not allow it to be stored in the local directory If (Left(strDocName, 33) = "H:\Client\Deliverables Schedules") Then MsgBox "This schedule must be saved to the appropriate folder on H:\Client!", vbCritical, "Wrong Folder" Cancel = True Else ActiveWorkbook.SaveAs strDocName End If End If Exit Sub err_handler: MsgBox Err.Number & " " & Err.Description End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook.SaveAs
Here is the calling subroutine in case that helps
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI Then SaveAsFile CreateShortCut Cancel = True End If End Sub Thanks "chemicals" wrote: Kevin, I added the code but ended up with the same result..... In the debugger strDocName ="H:\Client\<filename" where <filename is whatever the user types into the SaveAs Dialog. It steps right onto the "ActiveWorkbook.SaveAs strDocName" acting like it was successful yet there is no file and the currently open workbook name has not changed????? "Kevin B" wrote: use the following command line to modify the save directory: Application.DefaultFilePath = FilePath As an alternative, you could also add the file path to the filename captured with the GetSaveAsFilename function. After getting the filename you could do the following: strDocName = FilePath & strDocName -- Kevin Backmann "chemicals" wrote: This code did work before.... All I am trying to do is a SaveAs to another directory. I am using Office 2003.... I get no errors but the SaveAs function doesn't rename the current workbook that is open nor does it savea copy to the forlder specified...what gives? Here is my code: Sub SaveAsFile() Dim strDocName As String Const FilePath As String = "H:\Client\" On Error GoTo err_handler ChDrive FilePath ChDir FilePath strDocName = Application.GetSaveAsFilename(filefilter:="*.xls, *.xls") If strDocName < "False" Then 'do not allow it to be stored in the local directory If (Left(strDocName, 33) = "H:\Client\Deliverables Schedules") Then MsgBox "This schedule must be saved to the appropriate folder on H:\Client!", vbCritical, "Wrong Folder" Cancel = True Else ActiveWorkbook.SaveAs strDocName End If End If Exit Sub err_handler: MsgBox Err.Number & " " & Err.Description End Sub |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook.SaveAs
In the debugger strDocName ="H:\Client\<filename" where <filename is whatever the user types into the SaveAs Dialog. The GetSaveAsFileName function returns the complete path to the file, including the drive and folder names. For example, it returns C:\Test\Book1.xls The reason that your code is blowing up is that your strDocName is getting set to H:\Client\C:\Test\Book1.xls and this is clearly a bad file name. Use code like the following to return only the file name (no drive or path) from the input file name. Dim FName As Variant Dim N As Long Dim strDocName As String FName = Application.GetSaveAsFilename() If FName = False Then Exit Sub End If N = InStrRev(FName, "\") If N 0 Then FName = Mid(FName, N + 1) End If strDocName = "H:\Clients\" & FName MsgBox strDocName -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "chemicals" wrote in message ... Kevin, I added the code but ended up with the same result..... In the debugger strDocName ="H:\Client\<filename" where <filename is whatever the user types into the SaveAs Dialog. It steps right onto the "ActiveWorkbook.SaveAs strDocName" acting like it was successful yet there is no file and the currently open workbook name has not changed????? "Kevin B" wrote: use the following command line to modify the save directory: Application.DefaultFilePath = FilePath As an alternative, you could also add the file path to the filename captured with the GetSaveAsFilename function. After getting the filename you could do the following: strDocName = FilePath & strDocName -- Kevin Backmann "chemicals" wrote: This code did work before.... All I am trying to do is a SaveAs to another directory. I am using Office 2003.... I get no errors but the SaveAs function doesn't rename the current workbook that is open nor does it savea copy to the forlder specified...what gives? Here is my code: Sub SaveAsFile() Dim strDocName As String Const FilePath As String = "H:\Client\" On Error GoTo err_handler ChDrive FilePath ChDir FilePath strDocName = Application.GetSaveAsFilename(filefilter:="*.xls, *.xls") If strDocName < "False" Then 'do not allow it to be stored in the local directory If (Left(strDocName, 33) = "H:\Client\Deliverables Schedules") Then MsgBox "This schedule must be saved to the appropriate folder on H:\Client!", vbCritical, "Wrong Folder" Cancel = True Else ActiveWorkbook.SaveAs strDocName End If End If Exit Sub err_handler: MsgBox Err.Number & " " & Err.Description End Sub |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook.SaveAs
Chip StrDocName is set to "H:\Client\Book1.xls" when I show it with a MsgBox right before the Workbook.SaveAs.... The syntax is correct. I do not get an error it just doesn't work! Any other ideas? "Chip Pearson" wrote: In the debugger strDocName ="H:\Client\<filename" where <filename is whatever the user types into the SaveAs Dialog. The GetSaveAsFileName function returns the complete path to the file, including the drive and folder names. For example, it returns C:\Test\Book1.xls The reason that your code is blowing up is that your strDocName is getting set to H:\Client\C:\Test\Book1.xls and this is clearly a bad file name. Use code like the following to return only the file name (no drive or path) from the input file name. Dim FName As Variant Dim N As Long Dim strDocName As String FName = Application.GetSaveAsFilename() If FName = False Then Exit Sub End If N = InStrRev(FName, "\") If N 0 Then FName = Mid(FName, N + 1) End If strDocName = "H:\Clients\" & FName MsgBox strDocName -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "chemicals" wrote in message ... Kevin, I added the code but ended up with the same result..... In the debugger strDocName ="H:\Client\<filename" where <filename is whatever the user types into the SaveAs Dialog. It steps right onto the "ActiveWorkbook.SaveAs strDocName" acting like it was successful yet there is no file and the currently open workbook name has not changed????? "Kevin B" wrote: use the following command line to modify the save directory: Application.DefaultFilePath = FilePath As an alternative, you could also add the file path to the filename captured with the GetSaveAsFilename function. After getting the filename you could do the following: strDocName = FilePath & strDocName -- Kevin Backmann "chemicals" wrote: This code did work before.... All I am trying to do is a SaveAs to another directory. I am using Office 2003.... I get no errors but the SaveAs function doesn't rename the current workbook that is open nor does it savea copy to the forlder specified...what gives? Here is my code: Sub SaveAsFile() Dim strDocName As String Const FilePath As String = "H:\Client\" On Error GoTo err_handler ChDrive FilePath ChDir FilePath strDocName = Application.GetSaveAsFilename(filefilter:="*.xls, *.xls") If strDocName < "False" Then 'do not allow it to be stored in the local directory If (Left(strDocName, 33) = "H:\Client\Deliverables Schedules") Then MsgBox "This schedule must be saved to the appropriate folder on H:\Client!", vbCritical, "Wrong Folder" Cancel = True Else ActiveWorkbook.SaveAs strDocName End If End If Exit Sub err_handler: MsgBox Err.Number & " " & Err.Description End Sub |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook.SaveAs
Chip
Since GetSaveAsFileName function returns the complete path to the file, that is what I am using when I do the "Workbook.SaveAs strDocName" From the debugger (or MsgBox) the value of strDocName is "H:\Client\filename.xls" right before the SaveAs method is called. Everything is syntactically correct. Any other Ideas? (I wish it would at least throw an error) :-( "Chip Pearson" wrote: In the debugger strDocName ="H:\Client\<filename" where <filename is whatever the user types into the SaveAs Dialog. The GetSaveAsFileName function returns the complete path to the file, including the drive and folder names. For example, it returns C:\Test\Book1.xls The reason that your code is blowing up is that your strDocName is getting set to H:\Client\C:\Test\Book1.xls and this is clearly a bad file name. Use code like the following to return only the file name (no drive or path) from the input file name. Dim FName As Variant Dim N As Long Dim strDocName As String FName = Application.GetSaveAsFilename() If FName = False Then Exit Sub End If N = InStrRev(FName, "\") If N 0 Then FName = Mid(FName, N + 1) End If strDocName = "H:\Clients\" & FName MsgBox strDocName -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "chemicals" wrote in message ... Kevin, I added the code but ended up with the same result..... In the debugger strDocName ="H:\Client\<filename" where <filename is whatever the user types into the SaveAs Dialog. It steps right onto the "ActiveWorkbook.SaveAs strDocName" acting like it was successful yet there is no file and the currently open workbook name has not changed????? "Kevin B" wrote: use the following command line to modify the save directory: Application.DefaultFilePath = FilePath As an alternative, you could also add the file path to the filename captured with the GetSaveAsFilename function. After getting the filename you could do the following: strDocName = FilePath & strDocName -- Kevin Backmann "chemicals" wrote: This code did work before.... All I am trying to do is a SaveAs to another directory. I am using Office 2003.... I get no errors but the SaveAs function doesn't rename the current workbook that is open nor does it savea copy to the forlder specified...what gives? Here is my code: Sub SaveAsFile() Dim strDocName As String Const FilePath As String = "H:\Client\" On Error GoTo err_handler ChDrive FilePath ChDir FilePath strDocName = Application.GetSaveAsFilename(filefilter:="*.xls, *.xls") If strDocName < "False" Then 'do not allow it to be stored in the local directory If (Left(strDocName, 33) = "H:\Client\Deliverables Schedules") Then MsgBox "This schedule must be saved to the appropriate folder on H:\Client!", vbCritical, "Wrong Folder" Cancel = True Else ActiveWorkbook.SaveAs strDocName End If End If Exit Sub err_handler: MsgBox Err.Number & " " & Err.Description End Sub |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook.SaveAs
Chip
Since GetSaveAsFileName function returns the complete path to the file, that is what I am using when I do the "Workbook.SaveAs strDocName" From the debugger (or MsgBox) the value of strDocName is "H:\Client\filename.xls" right before the SaveAs method is called. Everything is syntactically correct. Any other Ideas? (I wish it would at least throw an error) :-( "chemicals" wrote: This code did work before.... All I am trying to do is a SaveAs to another directory. I am using Office 2003.... I get no errors but the SaveAs function doesn't rename the current workbook that is open nor does it savea copy to the forlder specified...what gives? Here is my code: Sub SaveAsFile() Dim strDocName As String Const FilePath As String = "H:\Client\" On Error GoTo err_handler ChDrive FilePath ChDir FilePath strDocName = Application.GetSaveAsFilename(filefilter:="*.xls, *.xls") If strDocName < "False" Then 'do not allow it to be stored in the local directory If (Left(strDocName, 33) = "H:\Client\Deliverables Schedules") Then MsgBox "This schedule must be saved to the appropriate folder on H:\Client!", vbCritical, "Wrong Folder" Cancel = True Else ActiveWorkbook.SaveAs strDocName End If End If Exit Sub err_handler: MsgBox Err.Number & " " & Err.Description End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SaveAs uses current open workbook filename; | Excel Programming | |||
Workbook::SaveAs() in MFC - Please help! | Excel Programming | |||
worksheet saveas workbook | Excel Programming | |||
Canceling SaveAs dialog box without saving workbook | Excel Programming | |||
Workbook.SaveAs | Excel Programming |