53909

How can i split an Excel (.xls) file that contains multiple sheets into separate excel files? [dupli

Question:

This question already has an answer here:

<ul><li> <a href="/questions/59075/save-each-sheet-in-a-workbook-to-separate-csv-files" dir="ltr" rel="nofollow">Save each sheet in a workbook to separate CSV files</a> <span class="question-originals-answer-count"> 6 answers </span> </li> </ul>

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?

Answer1:

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 <a href="https://msdn.microsoft.com/en-us/library/office/ff198017.aspx" rel="nofollow">here</a>

Recommend

  • Lotus Domino on 64 bit system: Could not create automation object, error 208
  • Get values on a row based on two or more rows in Excel
  • Go up one folder level
  • Looking up Access database in Excel
  • How to edit the source of a power query using VBA?
  • Macro to save active Sheet as new workbook, ask user for location and remove macros from the new wor
  • Using Range(Cells() Cells()).Style results in application defined or user defined error
  • Convert numbers to text-formatted numbers
  • Removing Filters for Each Table in a Workbook, VBA
  • Pivot Cache in one one workbook, Pivot table in another workbook
  • Excel split sheet data by name into new Excel Workbook [duplicate]
  • VBA Excel Save Copy as txt File
  • Get IMEI code or/and Mac Address of a Device
  • Refresh Excel connections
  • save as dialog excel code
  • Passing COM objects as parameters in C#
  • How to get current directory name in macros programming?
  • VBA Check Row For Value Then Another Value and Paste If True
  • Copy and Paste Entire Row
  • How do I retrieve the text in a table column using Selenium RC?
  • Failing to get duration of youtube video using xpath
  • Searching an XML file using PHP [closed]
  • Using VBA to select a dynamic range of cells and create a chart
  • parsing xml and html page with lxml and requests package in python
  • Deploying pre-encrypted configuration files to a production environment
  • Instanciate service on startup in Angular2
  • get passwords from chrome
  • Double-click autofill - dynamic based on adjacent cell
  • Excel VBA URLDownloadToFile Error for https Ressource
  • Creating a Multidimensional, Associative Array in VBScript
  • Conversion from string “a” to type 'Boolean' is not valid
  • C# - Serializing and deserializing static member
  • Sending data from AppleScript to FileMaker records
  • AT Commands to Send SMS not working in Windows 8.1
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • How to include full .NET prerequisite for Wix Burn installer
  • apache spark aggregate function using min value
  • Is it possible to post an object from jquery to bottle.py?
  • Sorting a 2D array using the second column C++
  • Observable and ngFor in Angular 2