Excel file separate each worksheet into a separate external excel file

Developers
1

Hello all
If possible, help from the Excel experts
I have an excel file that contains several worksheets
I want to separate each worksheet into a separate external excel file
I have a code that works fine as in the video
I would like to write it on Python Script
Thank you

import clr
clr.AddReference('ProtoGeometry')
from Autodesk.DesignScript.Geometry import *

myfilepath = IN[0]

imagePath = IN[1]


#Assign your output to the OUT variable.
OUT = 0

import clr
clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
from Microsoft.Office.Interop import Excel

# Instantiate the Excel Application
ex = Excel.ApplicationClass()

# Make it Visiable for us all to see
ex.Visible = True


# Disable Alerts - Errors Ignore them, they're probably not important
ex.DisplayAlerts = False

# Create a new Workbook = Spreadsheet file
workbook = ex.Workbooks.Open(myfilepath)
#Create a new Worksheet within our new Workbook
worksheet = workbook.Worksheets[1]  # Arrays of Object Start 1 in the MS Office World

worksheet.Copy Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & .Name & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

OUT =myfilepath, dir(worksheet)

code vba excel

Sub SaveAsPDFall()

Dim ws As Worksheet
    Dim xPath As String
    Dim SH As Worksheet
     xPath = Application.ActiveWorkbook.Path
 Application.ScreenUpdating = False
    Application.DisplayAlerts = False
     
For Each ws In ThisWorkbook.Worksheets

 With ws
     .Copy
     Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & .Name & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
     Application.ActiveWorkbook.Close False
 End With
Next ws

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub
```[Processing: test.rar...]()
[Processing: test.xlsm...]()


[Processing: test.rar...]()

[Processing: test.rar...]()

![wwq|689x386](upload://wHJPsq7sdf5GVpOmR8foqL3CoiE.gif)
2

wwq

3

separate each worksheet into a separate external excel.dyn (5.9 KB)

4

Should be pretty straight forward once you get your list structure figured out. You’re basically reading Excel (the original workbook with individual worksheets) to get all the data, and then writing to Excel (with one node but multiple workbooks) to split it into individual files. You can write to multiple files as long as you have multiple file paths and nested Excel data.

5

Thank you for your reply
My excel file contains formats and images within cells
Attached file is an example
Therefore, it must be divided by code so as not to lose formats and images
Thanks

6

@c.poupin
HI
If you have time, please help
to separate each sheet
To an excel file like a video
Thank you

7

try this

import clr
import sys
import System
from System.Runtime.InteropServices import Marshal
from System.IO import *

clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' )
from Microsoft.Office.Interop import Excel
from System.Runtime.InteropServices import Marshal

class SplitUtilsXls():
	def __init__(self, filePath):
		self.filePath = filePath
		self.dirpath = Path.GetDirectoryName(filePath)
		
	def SplitXls(self):
		ex = Excel.ApplicationClass()
		ex.Visible = True
		ex.DisplayAlerts = False
		workbook = ex.Workbooks.Open(self.filePath)
		for ws in workbook.Worksheets:
			new_workbook = ex.Workbooks.Add()
			wstemp = new_workbook.Worksheets[1]
			ws.Copy(wstemp)
			savepath = "{}\\{}.xlsx".format(self.dirpath, ws.Name)
			new_workbook.SaveAs(savepath)
			Marshal.ReleaseComObject(new_workbook)	
		workbook.Save()	
		ex.Workbooks.Close()
		ex.Quit()
		Marshal.ReleaseComObject(workbook)
		Marshal.ReleaseComObject(ex)				

filePath = IN[0]

obj_xls = SplitUtilsXls(filePath)
obj_xls.SplitXls()
OUT = Directory.GetFiles(obj_xls.dirpath)

image
image1523×391 45.4 KB

3 Likes
8

@c.poupin
Thanks a lot
fastest reply
and the best solution
you are a great man

1 Like