-2146826246 in win32com.client for empty #N/A cell in Excel

[Sven Boden]

Anyone know how to handle "#N/A" in Excel from win32com.client.

I'm extracting data from an Excel file using win32com.client. Everything works fine except for when the value "#N/A" is entered in excel. An empty cell. I assumed I do something as

if ws.Cells(r, c).Value is None:
...

But that doesn't seem to work. When I debug the piece of code while handling #N/A in a cell the type of the cell according to win32com.client is int and the value in the cell is -2146826246. Chances are small just this number will appear in Excel, but it looks dirty to depend on that value to decide if a cell is empty. Looked around the net for a solution, but nothing came up so far.

Anyone knows how to handle a "#N/A" cell in Excel in the proper way?

Regards,
Sven

[Chris Angelico]

On Mon, Aug 17, 2015 at 3:27 AM, Albert-Jan Roskam

Does that number happen to be -1 * sys.maxint?

No, it's -1 * 0x7ff5f806. As a signed 32-bit integer, it's 0x800a07fa.
Does either of those numbers mean anything?

Sven, you might do better to ask on a dedicated Python + Win32 mailing
list; I haven't used Python on Windows much for a while.

ChrisA

[Laura Creighton]

For what it's worth, I use xlrd for this. http://www.python-excel.org/

Laura

[Nobody]

Post by Sven Boden
Anyone knows how to handle a "#N/A" cell in Excel in the proper way?

0x800A07FA is how xlErrNA (error 2042) is marshalled. This isn't specific
to Python; you'll get the same value using e.g C# or VB.NET.

There's a fairly thorough article on this topic at:

https://xldennis.wordpress.com/2006/11/22/
https://xldennis.wordpress.com/2006/11/29/

[r***@fastmail.us]

Post by Chris Angelico
On Mon, Aug 17, 2015 at 3:27 AM, Albert-Jan Roskam

Does that number happen to be -1 * sys.maxint?

No, it's -1 * 0x7ff5f806. As a signed 32-bit integer, it's 0x800a07fa.
Does either of those numbers mean anything?

That's a COM error code, for Excel error 2042, which (unsurprisingly)
means N/A.

http://stackoverflow.com/questions/7526640/any-ideas-why-excel-interop-reads-many-decimals-as-2146826246

Here's some information from someone having the same problem in .NET,
which may or may not be helpful

https://xldennis.wordpress.com/2006/11/22/dealing-with-cverr-values-in-net-%E2%80%93-part-i-the-problem/

It looks like the values are actually returned as a VT_ERROR variant,
which .NET (and apparently python) reads as an integer.

[Sven Boden]

Post by Sven Boden

Date: Sun, 16 Aug 2015 09:53:32 -0700
Subject: -2146826246 in win32com.client for empty #N/A cell in Excel
Anyone know how to handle "#N/A" in Excel from win32com.client.
I'm extracting data from an Excel file using win32com.client. Everything

works fine except for when the value "#N/A" is entered in excel. An empty
cell. I assumed I do something as

...
But that doesn't seem to work. When I debug the piece of code while

handling #N/A in a cell the type of the cell according to win32com.client
is int and the value in the cell is -2146826246. Chances are small just
this number will appear in Excel, but it looks dirty to depend on that
value to decide if a cell is empty. Looked around the net for a solution,
but nothing came up so far.

Anyone knows how to handle a "#N/A" cell in Excel in the proper way?
Regards,
Sven
--
https://mail.python.org/mailman/listinfo/python-list

Hello,
Does that number happen to be -1 * sys.maxint?
Regards,
Albert-Jan
On python 3.x sys.maxint is gone... sys.maxsize is a lot larger on Windows

64bit (same laptop I run the code on).

Regards,
Sven