could use some help or feedback on my saveas function

09-24-2004, 01:38 AM

littledizzy littledizzy is offline

Freshman

Join Date: Jul 2004

Posts: 26

could use some help or feedback on my saveas function

Now on my machine which has OS-XP, Excel-2000 my code works fine when I save, but it's not working for Excel-XP, meaning it just crashes and closes Excel altogether.

As I said my code works fine when I save, but if I hit Cancel, a False.xls seems to want to be saved and if I hit No or Cancel on that prompt, it has me debug my code.

I'm obviously not an expert, am actually learning more as I go, so any help would be greatly appreciated.

So this is my code....

Code:

Sub SaveWorkbook()
 Dim Newname As String
 Newname = Range("A1").Value

Dim strFName As Variant

Dim aExcel As Excel.Application
Set aExcel = CreateObject("Excel.Application")

Dim wExcel As Excel.Workbook
Set wExcel = ActiveWorkbook
    
    strFName = aExcel.GetSaveAsFilename(Newname, FileFilter:="Excel Files (*.xls), *.xls")

     wExcel.SaveAs FileName:=strFName, FileFormat _
        :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
        False, CreateBackup:=False

    Set wExcel = Nothing
    Set aExcel = Nothing

End Sub

Last edited by herilane; 09-24-2004 at 03:22 AM. Reason: [vb] tags

09-24-2004, 03:26 AM

herilane

Unashamed geek

Retired Moderator
* Expert *

Join Date: Jul 2003

Location: London, England

Posts: 8,988

You can check whether or not the user cancelled by checking strFName:

Code:

If strFName = CStr(False) Then
    'do not save
Else
    'continue with your code
End If

It's safer to compare to CStr(False) than to "False", in case your users have a non-English version of Excel.