2

Hello community I'm new in here and can not "add a comment" now. So I created this question.

I've a problem and I don't understand why. The problem was explained in here as well, but didn't work for me. How to reference Excel table column names in XLWings?

I want to read all the elements of a table with:

import xlwings as xw

s_table_name = 'test1'
wb = xw.Book(r'D:\Python\test.xlsx')
d_table = xw.Range(f'{s_table_name}[String]') # this works
d_table1 = xw.Range(f'{s_table_name}[[#All],[String]]') # this didn't -> raise an exception -> see below

Here the exception:

Traceback (most recent call last):
  File "D:/Python/Experiments/test.py", line 6, in <module>
    d_table1 = xw.Range(f'{s_table_name}[[#All], [String]]').value # this didn't -> raise an exception
  File "D:\Python\Experiments\venv\lib\site-packages\xlwings\main.py", line 1244, in __init__
    impl = apps.active.range(cell1).impl
  File "D:\Python\Experiments\venv\lib\site-packages\xlwings\main.py", line 404, in range
    return Range(impl=self.impl.range(cell1, cell2))
  File "D:\Python\Experiments\venv\lib\site-packages\xlwings\_xlwindows.py", line 427, in range
    xl1 = self.xl.Range(arg1)
  File "D:\Python\Experiments\venv\lib\site-packages\xlwings\_xlwindows.py", line 66, in __call__
    v = self.__method(*args, **kwargs)
  File "C:\Users\marti\AppData\Local\Temp\gen_py\3.8\00020813-0000-0000-C000-000000000046x0x1x8.py", line 42299, in Range
    ret = self._oleobj_.InvokeTypes(197, LCID, 2, (9, 0), ((12, 1), (12, 17)),Cell1
pywintypes.com_error: (-2147352567, 'Ausnahmefehler aufgetreten.', (0, None, None, None, 0, -2146827284), None)

I tried all commands from this page: https://peltiertech.com/structured-referencing-excel-tables/ Like:

  • [#All]
  • [#Headers]
  • [#Data]
  • [#Totals]

And other ways of addint the parameter to:

d_table1 = xw.Range(f'{s_table_name}[[#All]]')
d_table1 = xw.Range(f'{s_table_name}[#All]')

But I got the same exception very time. To be sure, that it isn't a problem with my excel-version (I'm using 2013), I created a small macro with the build in macro recorder:

Sub Makro1()
    Range("test1[#All]").Select
End Sub

I don't understand where my problem is coming from. Maybe the parameters with # at the beginning aren't supported in xlwings anymore, but didn't found anything about that.

Please help me!

TenchiMuyo1984
  • 21
  • 3

1 Answers1

1

It seems the syntax is dependent on localization. I use Excel in german and I have to write [#Alle] instead of [#All], and a semicolon instead of comma:

wsh.range('Tabelle1[[#Alle];[Spalte1]]')

As this doesn't apply to VBA (like you pointed out), I think it can be seen as bug in xlwings. I will leave a comment in the answer you referenced.

You can check localized syntax, when you interactively edit a cell outside the table and create a reference, e. g.

=Tabelle1[#Ergebnisse]
Redoute
  • 208
  • 1
  • 8
  • Ok, that works. Thank you! But what is with: '#Headers', '#Data' and '#Totals'? '#Überschriften', '#Daten' and '#Gesamt'? And you can add a comment of my ticket at the repo on devs git: https://github.com/xlwings/xlwings/issues/1597 – TenchiMuyo1984 Jun 03 '21 at 17:15
  • #Kopfzeilen, #Daten and #Ergebnisse. Also see edited answer. – Redoute Jun 03 '21 at 19:35