How to create a range with xlwings based on two cells ranges

  pandas, python-3.x, range, xlwings

For an iterative process where I need to overwrite an output, I am trying to create a dynamic range in xlwings so as to be able to clear the contents before pasting the updated values.

The code is as follows:

Int_Retail = xw.Book('012021 Reporting International Retail.xlsx') 
#7.1. Dutch (51 rows in lenght, potential optimization automatically erase)
Dutch_Data_Tab = Int_Retail.sheets['Daten Details Dutch']
Dutch_Starting_Point = Dutch_Data_Tab.range('A1').end("down").offset(1,0)
Dutch_Starting_Point
Clean_Dutch_Range_Beg = Dutch_Starting_Point.offset(-(len(dutch)),0)
Clean_Dutch_Range_End = Clean_Dutch_Range_Beg.offset(len(dutch),len(dutch.columns))
type(Clean_Dutch_Range_End)

Output:

xlwings.main.Range

Code:

Clean_Dutch_Range_Beg
Clean_Dutch_Range_End

Output:

<Range [012021 Reporting International Retail.xlsx]Daten Details Dutch!$A$511>
<Range [012021 Reporting International Retail.xlsx]Daten Details Dutch!$V$562>

The problem is when I try to create the merged range (A511:V562). I have tried the following without success:

Clean_Dutch_Range = xw.Range(Dutch_Data_Tab,"{0}:{1}".format(Clean_Dutch_Range_Beg, Clean_Dutch_Range_End))

Error Message:

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-59-f1a846dc4dd9> in <module>
----> 1 Clean_Dutch_Range = xw.Range(Dutch_Data_Tab,"{0}:{1}".format(Clean_Dutch_Range_Beg, Clean_Dutch_Range_End))

~Anaconda3libsite-packagesxlwingsmain.py in __init__(self, cell1, cell2, **options)
   1012                 impl = sheets.active.range(cell1, cell2).impl
   1013             else:
-> 1014                 raise ValueError("Invalid arguments")
   1015 
   1016         self.impl = impl

ValueError: Invalid arguments

Any hints would be extremely appreciated 🙂

Thank you very much!

Source: Python-3x Questions

LEAVE A COMMENT