title: Setting an Excel Range with an Array using Python and comtypes?
body: <p>Using <a href="http://starship.python.net/crew/theller/comtypes/" rel="nofollow noreferrer"><code>comtypes</code></a> to drive Python, it seems some magic is happening behind the scenes that is not converting tuples and lists to <code>VARIANT</code> types:</p>
  
  <pre><code># RANGE(“C14:D21”) has values
  # Setting the Value on the Range with a Variant should work, but
  # list or tuple is not getting converted properly it seems
  
  &gt;&gt;&gt;from comtypes.client import CreateObject
  &gt;&gt;&gt;xl = CreateObject("Excel.application")
  &gt;&gt;&gt;xl.Workbooks.Open(r'C:\temp\my_file.xlsx')
  &gt;&gt;&gt;xl.Visible = True
  &gt;&gt;&gt;vals=tuple([(x,y) for x,y in zip('abcdefgh',xrange(8))])
  # creates: 
  #(('a', 0), ('b', 1), ('c', 2), ('d', 3), ('e', 4), ('f', 5), ('g', 6), ('h', 7))
  &gt;&gt;&gt;sheet = xl.Workbooks[1].Sheets["Sheet1"]
  &gt;&gt;&gt;sheet.Range["C14","D21"].Value()
  (('foo',1),('foo',2),('foo',3),('foo',4),('foo',6),('foo',6),('foo',7),('foo',8))
  &gt;&gt;&gt;sheet.Range["C14","D21"].Value[()] = vals
  # no error, this blanks out the cells in the Range
  </code></pre>
  
  <p>According to the <code>comtypes</code> <a href="http://starship.python.net/crew/theller/comtypes/" rel="nofollow noreferrer">docs</a>:</p>
  
  <blockquote>
    <p>When you pass simple sequences (lists or tuples) as <code>VARIANT</code>
    parameters, the COM server
    will receive a <code>VARIANT</code> containing a <code>SAFEARRAY</code> of <code>VARIANT</code>s with the
    typecode <code>VT_ARRAY</code> | <code>VT_VARIANT</code>.</p>
  </blockquote>
  
  <p>This seems to be inline with <a href="http://msdn.microsoft.com/en-us/library/aa139976%28office.10%29.aspx" rel="nofollow noreferrer">what MSDN says</a> about passing an array to a Range's Value. I also found <a href="http://blogs.msdn.com/eric_carter/archive/2004/05/04/126190.aspx" rel="nofollow noreferrer">this page</a> showing something similar in C#. Can anybody tell me what I'm doing wrong?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>I've come up with a simpler example that performs the same way (in that, it does not work):</p>
  
  <pre><code>&gt;&gt;&gt;from comtypes.client import CreateObject
  &gt;&gt;&gt;xl = CreateObject("Excel.application")
  &gt;&gt;&gt;xl.Workbooks.Add()
  &gt;&gt;&gt;sheet = xl.Workbooks[1].Sheets["Sheet1"]
  # at this point, I manually typed into the range A1:B3
  &gt;&gt;&gt; sheet.Range("A1","B3").Value()
  ((u'AAA', 1.0), (u'BBB', 2.0), (u'CCC', 3.0))
  &gt;&gt;&gt;sheet.Range("A1","B3").Value[()] = [(x,y) for x,y in zip('xyz',xrange(3))]
  # Using a generator expression, per @Mike's comment
  # However, this still blanks out my range :(
  </code></pre>