Python 3x win32com: Copying used cells from worksheets in workbook

Question

I have 6 work sheets in my workbook. I want to copy data (all used cells except the header) from 5 worksheets and paste them into the 1st. Snippet of code that applies:

`

excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Open(mergedXL)
wsSIR = wb.Sheets(1)
sheetList = wb.Sheets
for ws in sheetList:
        used = ws.UsedRange
        if ws.Name != "1st sheet":
            print ("Copying cells from "+ws.Name)
            used.Copy()

` used.Copy() will copy ALL used cells, however I don't want the first row from any of the worksheets. I want to be able to copy from each sheet and paste it into the first blank row in the 1st sheet. So when cells from the first sheet (that is NOT the sheet I want to copy to) are pasted in the 1st sheet, they will be pasted starting in A3. Every subsequent paste needs to happen in the first available blank row. I probably haven't done a great job of explaining this, but would love some help. Haven't worked with win32com a ton.

I also have this code from one of my old scripts, but I don't understand exactly how it's copying stuff and how I can modify it to work for me this time around:

ws.Range(ws.Cells(1,1),ws.Cells(ws.UsedRange.Rows.Count,ws.UsedRange.Columns.Count)).Copy()
wsNew.Paste(wsNew.Cells(wsNew.UsedRange.Rows.Count,1))

use iter_rows in openpyxl. Refer to this answer: https://stackoverflow.com/questions/29792134/how-we-can-use-iter-rows-in-python-openpyxl-package — Vimanyu, Mar 26 '18 at 22:51
Thanks Vimanyu. The answer doesn't mention how I can copy the cell values over to a specified worksheet on the first available blank row each time data from one of the worksheets has been copied over. It also doesn't include how I can omit the header/first row from each worksheet when copying over the used cells. — gistech007, Mar 27 '18 at 15:02

score 2 · Accepted Answer · answered Mar 27 '18 at 22:43

If I understand well your problem, I think this code will do the job:

import win32com.client 

# create an instance of Excel
excel = win32com.client.gencache.EnsureDispatch('Excel.Application')

# Open the workbook
file_name = 'path_to_your\file.xlsx'
wb = excel.Workbooks.Open(file_name)
# Select the first sheet on which you want to write your data from the other sheets
ws_paste = wb.Sheets('Sheet1')

# Loop over all the sheets
for ws in wb.Sheets:
    if ws.Name != 'Sheet1': # Not the first sheet
        used_range = ws.UsedRange.SpecialCells(11) # 11 = xlCellTypeLastCell from VBA Range.SpecialCells Method
        # With used_range.Row and used_range.Col you get the number of row and col in your range
        # Copy the Range from the cell A2 to the last row/col
        ws.Range("A2", ws.Cells(used_range.Row, used_range.Column)).Copy()
        # Get the last row used in your first sheet
        # NOTE: +1 to go to the next line to not overlapse
        row_copy = ws_paste.UsedRange.SpecialCells(11).Row + 1
        # Paste on the first sheet starting the first empty row and column A(1)
        ws_paste.Paste(ws_paste.Cells(row_copy, 1))
# Save and close the workbook
wb.Save()
wb.Close()
# Quit excel instance
excel.Quit()

I hope it helps you to understand your old code as well.

score 1 · Answer 2 · answered Mar 27 '18 at 20:40

1

Have you considered using pandas?

import pandas as pd

# create list of panda dataframes for each sheet (data starts ar E6
dfs=[pd.read_excel("source.xlsx",sheet_name=n,skiprows=5,usecols="E:J") for n in range(0,4)]

# concatenate the dataframes
df=pd.concat(dfs)

# write the dataframe to another spreadsheet
writer = pd.ExcelWriter('merged.xlsx')
df.to_excel(writer,'Sheet1')
writer.save()

answered Mar 27 '18 at 20:40

EricGarlic

37
4

I did use pandas initially but was having issues so resorted back to win32com. Didn't try out your suggestion as the answer above worked for me. Thanks though! – gistech007 Apr 09 '18 at 19:19

Python 3x win32com: Copying used cells from worksheets in workbook

2 Answers2