Workbook.SaveAs

[chemicals]

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

 

[chemicals]

I removed the Cancel = True but it did not help

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"

 

[Kevin B]

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

 

[Boris]

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

....

If strDocName <> "False" Then

....

Why quotes around False?

B.

 

[Kevin B]

If no file name is entered in the Save As dialog box the GetSaveAsFilename
function returns the string "FALSE" as its value.

 

[Chip Pearson]

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]

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.

 

[chemicals]

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?????

 

[chemicals]

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]

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

 

[Chip Pearson]

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]

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?

 

[chemicals]

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]

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)

:-(