How to save selected worksheet as xlxs

Question

I am running Windows 7 x64 and Office 2010 x64. I'm having an issue with VBA code for saving a file as .xlsx. If I save it as .xls, everything is fine, but I need to save it as .xlsx. Here is my code:

Sub Save_Alignment()

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False

End With


Dim Fpath    As String
Dim Fname   As String
Dim FileExtStr As String
Dim FileFormatNum As Long

FileExtStr = ".xls"
FileFormatNum = 56

Fpath = "C:\Users\Me\Documents\"
Fname = "Document " & Format(Date, "yyyy-mm-dd") & FileExtStr


Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets 'SetVersions
    If ws.Name = "CFGBranchAlignment" Then
        Dim wb As Workbook
        Set wb = ws.Application.Workbooks.Add
        ws.Copy Before:=wb.Sheets(1)
        wb.SaveAs Fpath & Fname, FileFormat:=FileFormatNum
        Set wb = Nothing
    End If

Next ws
Workbooks("Document MASTER.xlsm").Activate
Workbooks(Fname).Close SaveChanges:=False

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True

End With

End Sub

This will run fine, until I change the FileExtStr to .xlsx and the FileFormatNum to 51. Then the code breaks at wb.SaveAs Fpath... line.

I have also tried the following line after Fpath:

wb.SaveAs path & Fname, Excel.XlFileFormat.xlOpenXMLWorkbook

I am copying this from a macro enabled workbook (if that helps).

Got the basics for the code here:

Use VBA Macro to Save each Excel Worksheet as Separate Workbook

But I'm not able to get it to run with error unless I save the file in the Old excel version. At my wits end so any help would be appreciated.

I think your sheet module contains code and it makes an error because the code wont be saved as a macro free workbook — Siphor, Jun 26 '14 at 19:37
Try saving before you copy the sheets. Is there code in the sheets' modules? — Roland, Jun 26 '14 at 19:39
I was working with automatically running a macro based on a private sub tied to a worksheet. When I check the specific tab I am copying, it does not have any code tied to that sheets module. Do I need to remove all code from all worksheets for this to work properly? — Mike Marshall, Jun 27 '14 at 15:14
No you don't have to do that. I posted something which works on my system. Please have a go at your end. — L42, Jul 01 '14 at 08:23
Siphor was correct. When I removed the code in the sheets module the code ran fine. — Mike Marshall, Jul 01 '14 at 11:37

score 1 · Answer 1 · answered Jun 27 '14 at 11:58

1

For saving the file as .xlsx please change as below the file extension and FileformatNum

FileExtStr = ".xlsx"
FileFormatNum = 51

answered Jun 27 '14 at 11:58

Snail

89
2

@MikeMarshall If you explicitly provide the argument name, you should provide all argument names. `wb.SaveAs FileName:=Fpath & Fname, FileFormat:=FileFormatNum` – L42 Jul 01 '14 at 08:30

score 0 · Answer 2 · answered Jul 01 '14 at 08:00

Hi Mike try the below code... it working fine also be sure that you got 2007 or 2010 version instlled in your system or atleast "File conversion" If you are working from 2003 format, and if you dont have 2007 or 2010 version then the code will break.

Sub Save_Alignment()

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False

End With


Dim Fpath    As String
Dim Fname   As String
Dim FileExtStr As String
Dim FileFormatNum As Long

FileExtStr = ".xlsx"
FileFormatNum = 51

Fpath = "C:\"
Fname = "Document " & Format(Date, "yyyy-mm-dd") & FileExtStr


Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets 'SetVersions
    If ws.Name = "CFGBranchAlignment" Then
        Dim wb As Workbook
        Set wb = ws.Application.Workbooks.Add
        ws.Copy Before:=wb.Sheets(1)
        wb.SaveAs Fpath & Fname, FileFormat:=FileFormatNum
        Set wb = Nothing
    End If

Next ws
Workbooks("Document MASTER.xlsm").Activate
Workbooks(Fname).Close SaveChanges:=False

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True

End With

End Sub

score 0 · Answer 3 · edited May 23 '17 at 12:12

Try this. Made a little bit modification in your For Each clause:

Sub test()
    Dim Fname As String, Fpath As String, FileExtStr As String

    FileExtStr = ".xls"
    Fpath = CreateObject("WScript.Shell").specialfolders("MyDocuments") & "\"
    Fname = "Document " & Format(Date, "yyyy-mm-dd") & FileExtStr

    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = "CFGBranchAlignment" Then
            Dim wb As Workbook
            ws.Copy
            Set wb = ActiveWorkbook
            wb.SaveAs Fpath & Fname, xlOpenXMLWorkbook
            Set wb = Nothing
        End If
    Next ws
End Sub

HTH. Btw, stick to working with your object directly.
Check this out to see ways on how to avoid select and active[object].

How to save selected worksheet as xlxs

3 Answers3