CODEWITHSUNDEEP
pythonexcelwin32com
makman
makman

Reputation: 139

Find last row of column A and fill cell below it using win32com module - Python

I'm trying to find the last row in column A and fill the cell below the last row using "fill series" formatting. My code is shown below.

A couple of notes here 1) the worksheets are group selected prior to addressing the source range and fill range 2) The column data type may differ from sheet to sheet but the fill series formatting works when I specify the exact range

import win32com.client as win32

excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = True
wb = excel.Workbooks.Open ('D:\\Data Str\\Test Files\\Book1.xls')
ws = wb.Worksheets(['Sheet1','Sheet2'])
ws.Select()
for sheet in ws:
       lastRow = sheet.UsedRange.Rows.Count
       print (lastRow)
       sourceRange = sheet.Range("A") & lastRow
       fillRange = sheet.Range("A") & lastRow + 1
       sourceRange.AutoFill(fillRange, win32.constants.xlFillSeries)

The code runs as expected up to sourceRange variable where I'm getting the below error.

com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2146827284), None)

My expected output and a sample dataset are shown in the table below. Both columns correspond to column A of Sheet1 and Sheet 2 accordingly.

Key (Sheet1)    Date (Sheet 2)
1               01/11/2019
2               02/11/2019
3               03/11/2019
4               04/11/2019
5               05/11/2019
6               06/11/2019
7               07/11/2019
8               08/11/2019
9               09/11/2019
10              10/11/2019
11              11/11/2019

Expected Result

Key (Sheet1)    Date (Sheet 2)
1               01/11/2019
2               02/11/2019
3               03/11/2019
4               04/11/2019
5               05/11/2019
6               06/11/2019
7               07/11/2019
8               08/11/2019
9               09/11/2019
10              10/11/2019
11              11/11/2019
12              12/11/2019

Upvotes: 1

Views: 4542

Answers (1)

makman
makman

Reputation: 139

It seems the issue was that I wasn't correctly referencing and defining the Range of cells within the grouped sheets. Please see comments (#) on my code:

import win32com.client as win32

excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = True
wb = excel.Workbooks.Open ('D:\\Data Str\\Test Files\\Book1.xls')
ws = wb.Worksheets(['Sheet1','Sheet2'])
ws.Select()
for sheet in ws:
       lastRow = sheet.UsedRange.Rows.Count 
       print (lastRow)
       nRow = lastRow + 1  # In case of different row length, save last row in nRow variable
       sourceRange = sheet.Range("A1" , sheet.Cells(lastRow,1)) 
# Use comma separation and sheet.cells function to define the ending point of Range class method.
       fillRange = sheet.Range("A1" , sheet.Cells(nRow ,1))
       sourceRange.AutoFill(fillRange, win32.constants.xlFillSeries)

Upvotes: 2

Related Questions