0

In Excel VBA the code below selects the columns A to E.

worksheet.Columns(1).Resize(ColumnSize:=5).Select

But in Python (using the win32com.client module) the code below doesn't work.

worksheet.Columns(1).Resize(ColumnSize=5).Select()
# TypeError: __call__() got an unexpected keyword argument 'ColumnSize'

worksheet.Columns(1).Resize(ColumnIndex=5).Select()
# pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2146827284), None)

While the code below only selects the column E.

worksheet.Columns(1).Resize(5).Select()
worksheet.Columns(1).Resize(RowIndex=5).Select()

Workaround in the code below (I don't want to use worksheet.Columns('A:E') because the number of columns varies).

worksheet.Range(Cell1=worksheet.Cells(RowIndex=1, ColumnIndex=1), Cell2=worksheet.Cells(RowIndex=1, ColumnIndex=5)).EntireColumn.Select()

My question is why does the resize not work as expected, is it a bug? If so, is there a proper place to report it? Should I use the workaround I provided, or something else?

user7393973
  • 2,270
  • 1
  • 20
  • 58

1 Answers1

1

After searching for Resize in the file C:\Users\UserName\AppData\Local\Temp\gen_py\3.7\00020813-0000-0000-C000-000000000046x0x1x8\Range.py I found the lines:

from win32com.client import DispatchBaseClass
class Range(DispatchBaseClass):

  # *irrelevant code in between*

  # Result is of type Range
  # The method GetResize is actually a property, but must be used as a method to correctly pass the arguments
  def GetResize(self, RowSize=defaultNamedOptArg, ColumnSize=defaultNamedOptArg):
    ret = self._oleobj_.InvokeTypes(256, LCID, 2, (9, 0), ((12, 17), (12, 17)),RowSize
      , ColumnSize)
    if ret is not None:
      ret = Dispatch(ret, 'GetResize', '{00020846-0000-0000-C000-000000000046}')
    return ret

So I tried using GetResize instead and it works as Resize should work.

worksheet.Columns(1).GetResize(ColumnSize=5).Select()

There's no Resize declared so it actually uses the code:

    # Default method for this class is '_Default'
    def __call__(self, RowIndex=defaultNamedOptArg, 
ColumnIndex=defaultNamedOptArg):
        return self._ApplyTypes_(0, 2, (12, 0), ((12, 17), (12, 17)), '__call__', None,RowIndex
            , ColumnIndex)

So if I rename GetResize to Resize in that file I can use Resize in the code, but that file is created (if it's not already created) by the code win32com.client.gencache.EnsureDispatch(prog_id='Excel.Application').

In the module win32com.client.build the loop in DispatchItem.Build:

        for key, item in list(self.propMapGet.items()):
            self._propMapGetCheck_(key,item)

Gets the key 'Resize' and _propMapGetCheck_ turns it into GetResize and adds it to the mapFuncs dictionary.

user7393973
  • 2,270
  • 1
  • 20
  • 58