Side effect when copying .xlsx sheet to different excel file

Question

There are several ways to copy an excel (.xlsx) sheet to another existing excel file. As i have to deal with sheets containing formatted form objects (colored text boxes, lines, ...), the only working solution was to use the win32.com.client method, as mentioned here. Using that method, i can insert a sheet from one existing excel file in another existing excel file.

But doing so has a side effect i'd like to avoid:

Before adding the sheet, my destination file sheet contains content that fits on exactly one page when printing, or saving in PDF format.

After adding the sheet, my destination file sheet will result in 72 pages when printing or being saved in PDF format. First page contains the data, other 71 pages are plain white.

I'm pretty sure that's not a problem of the code, it looks like this:

from win32com.client import Dispatch

xl = Dispatch("Excel.Application")

wb1 = xl.Workbooks.Open(Filename=template)
wb2 = xl.Workbooks.Open(Filename=destination)

ws1 = wb1.Worksheets(1)
ws1.Copy(Before=wb2.Worksheets(1))

wb2.Close(SaveChanges=True)
xl.Quit()

This behaviour can of course be changed afterwards by defining the printing area manually in excel. It can also be changed by setting the print setup to only proccess page 1. But this is not the desired solution for this problem, as other people working with this files tend not to look at the printing preview and well, then you have this pile of slightly warm blank papers on the printer.

I tried to set the printing area after the sheet-copy-thing, using openpyxl. This doesn't work, as saving the result deletes all form objects from my sheets, because openpyxl can't handle them. (Setting the printing area itself worked fine, but that could ony be done with files just containing data.)

So, i'm looking for:

1. A solution to add an entire existing .xlsx worksheet containing form objects to another existing excel file, without messing around with the printed outcome, or

2. A method to change the printing area of existing .xlsx sheets, without losing form objects or images.

I didn't find anything helpful - but maybe option 2 can be done using win32.com, too?

Any idea or help is welcome, thank you!

Answer 1

...after i finally found this page:

Solution using win32.com.client:

Worksheets(n).PageSetup.PrintArea  = "$A$1:$L$32" # desired print area range goes here

In my case, i want to set the print area of the last (most right) worksheet in my file:

wb2_lastsheet = len(wb2.Worksheets)
wb2.Worksheets(wb2_lastsheet).PageSetup.PrintArea  = "$A$1:$L$32" 

Minor issues:

This will work only once for a file. The second time i try to set the print area like this, a small popup window wants me to insert a name for the second printing area, even if it's applied to another sheet in the file.

As the name Print_Area is some excel built-in term, and the first execution already automatically sets this name, every other attempt results in the popup window, wanting me to insert a valid name for the new printing region.

Nonetheless, setting the printing area multiple times works! You just have to put in names for them.

I've tried to deal with existing regions before setting a new print area, using the excel Name-Object, but that didn't work at all. Seems like the built-in term Print_Area is some kind of special object in excel, normal Name methods doesn't work here.

But i've reached what i want - and looking at it now, it's quite stupid simple.

Hope that helps some other excel-automation-pythonista.