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

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

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

Workbook.SaveAs
 

On Wed, 10 Oct 2007 10:51:03 -0700, chemicals wrote:

....
If strDocName < "False" Then
....

Why quotes around False?

B.

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.

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.

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.

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

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

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

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

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

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

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