AttributeError: .Range

Question

I'm trying to autofill with "fill series" formatting the value of cell A11 into A12 on two worksheets. This needs to be achieved using win32com module. My code is:

from win32com.client import Dispatch
from win32com.client import constants
xl = Dispatch('Excel.Application')
xl.Visible = True
wb = xl.Workbooks.Open ('S:\\Height Peak.xls')
ws = wb.Worksheets(['Sheet1','Sheet2'])
ws.Select()
ws.Range('A10:A11').AutoFill(ws.Range('A11:A12'), xlFillSeries)

As soon as I run the code, I'm encountering the following error:

AttributeError: unknown.Range

I am not familiar with method yet, but they way you use it seems to ring some alarm bells. `ws` is not a single sheet, but a list of two sheets, according to your code. So it might have an issue with identifying the ranges that way. Secondly, what are you trying to achieve by having the `Range` before and within the AutoFill call to be the same range? What are you trying to fill in? Lastly: The constant wont work! Within python, you need to save them into a variable and call them differently. I can show you once you clarified your question a bit. — tst, Dec 18 '19 at 11:53
Hello @tst . Allow me to provide some clarifications. Thank you for pointing out the same range bit, I've corrected this to reflect correct cells reference. I'm trying to autofill strings, in my case cell A10 is a unique reference number followed by slash and person number eg. 1914/P03. The 1914 reference can be used for multiple persons. Hence, in cell A11 the reference is 1914/P04. So in cell A12 using Autofill method I should get 1914/P05. That's what I'm trying to achieve. The `ws` variable is an easy to remember variable for me. I can change that to `wsheets` if necessary. — makman, Dec 18 '19 at 12:08

tst · Accepted Answer · 2019-12-18T12:24:57.820

1

There were 3 Problems:

1) You need to iterate over your worksheets!
2) The source Range needs to be a subrange of the fill Range. That is not documented well and I basically just figured that out from looking at examples in the docs.
3) You import constants, but you need to actually specify your constants' source! (see below

Code:

from win32com.client import Dispatch
from win32com.client import constants as const

xl = Dispatch('Excel.Application')
xl.Visible = True
wb = xl.Workbooks.Open ('S:\\Height Peak.xls')

ws = wb.Worksheets
for sheet in ws:
    if sheet.Name.endswith("1") or sheet.Name.endswith("2"):
        sourceRange = sheet.Range('A1:A10')
        fillRange = sheet.Range('A1:A12')
        sourceRange.AutoFill(fillRange, const.xlFillSeries)

edited Dec 18 '19 at 12:24

answered Dec 18 '19 at 12:19

tst

371
1
11

The website wont let me format this correctly.. hang on – tst Dec 18 '19 at 12:22
Thank you for that, I'll test the code now. Is it possible to edit your answer so you can give me a solution based on alphanumerical character sheet name eg. 'Records2019', 'Records2020'? The above code is part of my overall code and other workbooks have alphanumerical sheet names. – makman Dec 18 '19 at 12:29
Basically, all you need to change is the if statements. I made it look for `endswith("1")`, as the default names for Excel Sheets are "Sheet1", "Sheet2", "Sheet3", but localized. So if you use Excel in Dutch, it would still work! If the source and fill ranges are the same on all sheets, you can just make a list of all the sheetnames you want to apply this to and check `if sheet.Name in listOfSheetnames` – tst Dec 18 '19 at 12:32
That's perfect! And can confirm both suggestions work as expected. Thank you so much, you saved me a great deal of time. – makman Dec 18 '19 at 13:33
The code has failed again. It succeeded for the first two parts, then breaks with **sourceRange.AutoFill(fillRange, const.xlFillSeries) AttributeError: type object 'Constants' has no attribute 'xlFillSeries'** . Please can you help? I'm not sure what I'm doing wrong, as I've specified the code you suggested above. – makman Dec 18 '19 at 15:08
It works fine for me... Have you also changed the import like I have? Alternatively you can just save it to a variable like so: `const = win32com.client.constants` - strange either way though – tst Dec 18 '19 at 15:32
It worked when I saved it in the variable `const`. Back to normal now and thank you again. – makman Dec 18 '19 at 15:44

AttributeError: .Range

1 Answers1