I have an excel .xls file that contains 10 sheets. I want to split all of those sheets as separate .xls files. Does excel have any option to export a sheet into a separate .xls file?
Asked
Active
Viewed 1,836 times
0
-
1refer to http://stackoverflow.com/questions/59075 – Steven Manuel Mar 30 '15 at 06:14
1 Answers
0
Try this code
Sub Splitbook()
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
xWs.Copy
Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xls", fileformat:=56
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
These are the main file formats in Excel 2007-2013, Note: In Excel for the Mac the values are +1
51 = xlOpenXMLWorkbook (without macro's in 2007-2013, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2013, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2013 with or without macro's, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2013, xls)
6 = ".csv"
-4158 = ".txt"
36 = ".prn"
All file formats codes here
Dmitrij Holkin
- 1,995
- 3
- 39
- 86
-
its working .. but while trying to open file its showing one message"that file trying to open different format that specified file extension.verify that file not corrupted and is form a trusted source before opening file " – Unni R Mar 30 '15 at 07:23
-
-
1