1

I want to copy paste ranges for sheets in my .xlsm workbook using xlwings.I want to maintain the source formatting and the leading single quote because of which I've used the .copy , .paste method. I'm getting this error while trying to run the code (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', 'Select method of Range class failed', 'xlmain11.chm', 0, -2146827284), None) after sheet 1 is successfully copied . The first sheet in the loop is copied properly but the above mentioned error is displayed after completion of sheet 1. This is the code I used to copy paste the ranges across sheets :


wb = xw.Book('standalone.xlsm')
sheet_names = ['Sheet1','Sheet2']
for sheet_name in sheet_names:
    sht=wb.sheets(sheet_name)
    sht.range('A1:A6').api.SpecialCells(12).copy
    sht.range('B1').select()
    sht.api.paste
    sht.api.Application.CutCopyMode=0
wb.save('standalone.xlsm')
wb.close()


PS :- I don't want to use `.value` method of copying ranges as it doesn't preserve source formatting and cells which start with single quote
Scope
  • 727
  • 4
  • 15

2 Answers2

1

The sheet must be active before you can select a range on it. Use sht.select():

import xlwings as xw

wb = xw.Book('test.xlsx')
sheet_names = ['Sheet1', 'Sheet2']
for sheet_name in sheet_names:
    sht = wb.sheets[sheet_name]
    sht.range('A1:A6').api.copy
    sht.select()
    sht['B1'].select()
    sht.api.paste
    wb.app.api.CutCopyMode = False
wb.save('test.xlsx')
wb.close()
mouwsy
  • 1,457
  • 12
  • 20
1

Mouwsy's answer works fine. Alternatively you can use pastespecial and with it you wont have to select the sheet

import xlwings as xw

wb = xw.Book('test.xlsx')
sheet_names = ['Sheet1', 'Sheet2']
for sheet_name in sheet_names:
    sht=wb.sheets(sheet_name)
    sht.range('A1:A6').api.SpecialCells(12).copy
    sht.range("R1").api.PasteSpecial("-4104")
    sht.api.Application.CutCopyMode=0
wb.save('standalone.xlsm')
wb.close()

Update: Just found out xlwings already has a copy and paste method so a simple copy paste will be:

for sheet_name in sheet_names:
    sht=wb.sheets(sheet_name)
    sht.range('A1:A6').copy()
    sht.range("R1").paste()
    sht.api.Application.CutCopyMode=0

This preserves source formatting. I cant find any other options for customising a copy and paste operation from the docs so I guess you'll still need the api for complete control.

West
  • 2,350
  • 5
  • 31
  • 67
  • 1
    I assume you are using SpecialCells to only get visible cells in case of filters or hidden cells, otherwise its not required for a simple copy paste – West Nov 13 '20 at 03:08
  • Thanks for the answer. So without the PasteSpecial the source formatting as is by just using the `copy` `paste ` will be retained – Scope Nov 13 '20 at 07:10
  • Thanks again, If you feel it would help others facing the same issue you may upvote this question. Personally when I faced the issue I searched many SO answers and none of the answers worked ( maybe because of different version or could be any other reason) – Scope Nov 13 '20 at 07:40