SaveAs .xls in 2007

[Giordano Bruno]

I am using Excel 2007 and want to allow users of a workbook to save the file as any name they choose, but only as an xls, not an xlsm. I can't find code that works for this in Excel 2007.

 

[dantheman9]

Depending on how you are saving the file maybe something like;

Fname = Application.GetSaveAsFilename( _
            InitialFileName:=Filename, _
            filefilter:="Excel (.xls), *.xls", _
            Title:="Save Excel .xls file to...")
        If Fname <> False Then
            .SaveAs Fname, xlTextMSDOS
        End If

 

[Giordano Bruno]

Thanks for that dantheman9. I'm sorry about the long delay, but the sky fell in over here and I have been sticking it back up again.

I've just tried you code, but the ".SaveAs" in the line above "End If" is blocked and I get a "Compile error: Invalid or unqualified reference"

When I apostrophe this line, the code stops on the first line with a "Run-time error 1004: Method 'GetSaveAsFilename' of object _Application failed.

Do you have any suggestions

 

[shadow12345]

I would think that would be related to how you are saving the file...

It looks to me like that code needs to go in the workbook event before save.

 

[Giordano Bruno]

Hi shadow12345,

Thanks for that. I am not quite sure what you mena by "how" I am saving the file. The code is in "This Workbook" and fires when I press F12, Ctrl-S or File Save. That's when I get the error messages.

 

[RoryA]

There's no workbook specified (and the format is wrong):

ThisWorkbook.SaveAs Fname, 56

 

[Giordano Bruno]

Hello rorya,

Thanks for the contribution. But I don't know how to modify the earlier code to insert that line. What I am trying to avoid is having the name changed or the extension which must remain as ".xls" in the Excel 2007" environment.

 

[RoryA]

You replace the line that was giving you the error with the line I posted.

 

[dantheman9]

Below code worked for me, you just need to set the Range to the cells you want to save or sheets.
Option also to set the Save As Diolog start location via the 'Path' option too.

Sub saveexcel()
Dim rng As Range
    Dim wb As Workbook
    Dim Fname As Variant
    Dim Path As String
 
    Set rng = Range("A1:A211") ' range of cells to save
    Set wb = Workbooks.Add
    Path = "C:\"   'Open Save As form to this location
 
    With wb
        rng.Copy
        .Worksheets(1).Range("A1").PasteSpecial Paste:=xlValues
        ' open path as set in path option box
        ChDir (Path)
        Application.DisplayAlerts = False
               Fname = Application.GetSaveAsFilename( _
            InitialFileName:="FileName", _
            filefilter:="Excel97, *.xls", _
            Title:="Save File To...")
        If Fname <> False Then
            .SaveAs Fname, 56
        End If
        .Close True
 
        End With

 

[Giordano Bruno]

rorya,
Sorry, but that gave me an error message also. I think that the original code segment is incomplete.

dantheman,
Your code goes some way to solving the problem. I don't want all the options you gave me so I have trimmed the code and it works except that it asks me to save three times.

Here is what I am using:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wb As Workbook
Dim Fname As Variant
With wb
Application.DisplayAlerts = False
Fname = Application.GetSaveAsFilename( _
InitialFileName:="AccountIndex", _
filefilter:="Excel2003, *.xls", _
Title:="Save File To...")
If Fname <> False Then
ActiveWorkbook.SaveAs Fname, 56
End If
End With
Application.DisplayAlerts = True
End Sub

Any idea why it doesn't just ask once?