I am trying to use win32com to copy a worksheet from my workbook to a new workbook. The code is working fine but the cell formulas in the new book point back to the original book. I would like to break the links in the new book so that these formulas are replaced with raw numbers. This is trivial to do in Excel but I haven't been able to find out how to do it using the win32com client in Python.
Here is a snippet of my code:
import win32com.client
xl = win32com.client.gencache.EnsureDispatch('Excel.Application')
xl.Visible = True
#Open & Refresh Spreadsheet
wb = xl.Workbooks.Open(r"C:\Users\me\dummy.xlsx") #Dummy path
print("Refreshing data...")
wb.RefreshAll()
#Create new book and copy target sheet over
print("Opening new workbook")
nwb = xl.Workbooks.Add()
newfile = r"C:\Users\me\dummy2.xlsx"
wb.Worksheets(["Target Sheet"]).Copy(Before=nwb.Worksheets(1))
nwb.SaveAs(newfile)
This code works fine but in the saved "dummy2" file each of the cells containing formulas reference the original sheet. How can I break the links in the new book and/or copy values only from the original book?
Edit in response to @martineau 's downvote of the answer and of the (admittedly unsatisfactory) Microsoft documentation.
I think you haven't been able to find out how to do this because you have been looking in the wrong place. Your question really has little to do with Python or with win32com.
This line
xl = win32com.client.gencache.EnsureDispatch('Excel.Application')
fires up a COM client called xl that talks to excel.exe. Your variable xl is a thin Python wrapper around a Microsoft COM object that can call Excel VBA functions. When you type xl., everything after the dot is expected to be a VBA object or method. Any value (other than strings and floats) that you get back from a call is a VBA object in a thin Python wrapper. Python conventions do not necessarily apply to such objects.
So to find out about what functions you need to call, you need to be looking at the Excel VBA documentation. One difficulty with that documentation is that it assumes you are writing VBA, not Python. The other is that it isn't all that well-written.
The VBA method you need is Workbook.BreakLink().
Call it after copying the original workbook and before saving the copy, like this (I'm using your dummy filename here, don't expect it to actually work without fixing that):
wb.Worksheets(["Target Sheet"]).Copy(Before=nwb.Worksheets(1))
nwb.BreakLink(Name=r"C:\Users\me\dummy.xlsx", Type=1)
nwb.SaveAs(newfile)
The name of the link is the filename it points to, and the type of the link is 1 (for a link to an Excel spreadsheet). In this case you know the name of the link source (since you just made a copy of it) so there is no need to ask what the filename is, but in the general case you need to call Workbook.LinkSources() to find out what they are, and break them one by one.