I have a scenario where I have an Excel template sheet, which contains some formulas, formatting, and specific sizing. Nothing too crazy. I need to fill out several hundred of these templates for a downstream process. For sake of argument today, let's assume that the formatting needs to remain unchanged.
My initial plan was to write specific values to specific cells (ie, write out an employee name to cell A2 and preserving formatting) by using the new Excel output functionality included in the 2020.1+ releases. This worked great.
The issue arises when I want to have each sheet in the Excel file dynamically renamed to a different employee name. I have dynamically written out sheet names before many times, but I have never had to do this with existing formatting on that sheet, meaning that I can't just create it out of nothing. Therefore, I need to:
1) Dynamically copy & rename sheets within an Excel file, while preserving formatting.
2) Output data to specific cells on specific sheets.
Thanks in advance! Looking forward to seeing what you guys think I should do.
Thanks for the reply I tried this, yes it copied the sheet and rename it but all other sheet from the template excel file are all copied to the destination file as well, is that possible that i can ONLY copy one sheet ?
this is great, perfect! Can I ask more question how can we copy sheet cross file? it seems that this solution only work for copy sheet within one excel file, is that possible i can copy sheet to another workbook?
I use the openpyxl in this code. But the openpyxl doesn't have the function which copy from one Excel file to another Excel file. If you need that function, you have to use the pywin32 package. It need to be installed Excel application. The code is as follows.
from ayx import Package
from ayx import Alteryx
from win32com.client import Dispatch
df1 = Alteryx.read("#1")
CopyFromPath = df1.iat[0,df1.columns.get_loc('CopyFromPath')]
CopySheetName = df1.iat[0,df1.columns.get_loc('CopyFromSheet')]
NewPath = df1.iat[0,df1.columns.get_loc('NewFileExcelPath')]
xl = Dispatch("Excel.Application")
xl.Visible = True # You can remove this line if you don't want the Excel application to be visible
wbOld = xl.Workbooks.Open(Filename=CopyFromPath)
wbNew = xl.Workbooks.Open(Filename=NewPath)
wsOld = wbOld.Worksheets(CopySheetName)