OLE bstr not null terminated?

Question

So, I am trying to automate Excel from C++ and OLE, using the Autowrap function provided by Microsoft in their automation examples.

I have gotten as far as that I can read a string value from a cell in the spreadsheet. However, it seems like for some values, something is wrong with the string, like it's not null terminated. Here is my code:

VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, range, L"Value", 0);
wprintf(L"%s, len: %d\n", result.bstrVal, SysStringLen(result.bstrVal));
wprintf(L"abc %s\n", SysAllocString(L"def"));

The output is:

John Doe  á, 11
abc def

Whereas, in the Excel file when displayed in Excel, it shows like:

John Doe

(Can the code page of the Excel document somehow be an issue? It's an xlsx file.)

1. Does that happen with any cell / value? 2 What do you get with `SysStringLen(results.bstrVal)`? 3. What do you get if you try `wprintf(L"abc %s\n", SysAllocString(L"def"));`? — Amit, Oct 06 '15 at 08:34
@Amit, updated question. 1 - no, it does not happen always, just for some values. — Prof. Falken, Oct 06 '15 at 08:47
@Amit, actually 11! So I guess data is dirty somehow in the cell... ?! — Prof. Falken, Oct 06 '15 at 08:54
I guess so... maybe it's excel that's hiding that extra characters — Amit, Oct 06 '15 at 08:56
I pasted the cell value into a text file, then viewed it with Double Commanders hex viewer. The last character is a non breaking space. (Hex A0). So that was the culprit all along... @Amit — Prof. Falken, Oct 06 '15 at 09:00
@Amit, so I guess the console can not display non breaking space. — Prof. Falken, Oct 06 '15 at 09:13

score 4 · Accepted Answer · edited Oct 08 '15 at 13:20

4

John Doe á, 11

Code is pretty buggy and needs to be fixed. The result variable cannot be IDispatch, it must be a VARIANT. You blindly assume that the Range.Cell.Value property returns a string, that's not how Excel works. Calling VariantChangeType() to force a conversion to VT_BSTR is required.

The á character is otherwise a simple text encoding problem. Dollars to donuts, you copied the string from a console window. Which uses code page 437 by default in Western Europe and the Americas. The character code for á in that code page is 0xA0. Which is actually U+00A0, a non-breaking space in Unicode, not an unusual codepoint in modern text.

Getting it to actually display as a space in a console mode app requires calling SetConsoleOutputCP() to switch it to CP_UTF8 and changing the font of the console from Terminal to a TrueType font like Consolas. Well covered in other Q+A at this site.

edited Oct 08 '15 at 13:20

Prof. Falken

24,226
19
100
173

answered Oct 06 '15 at 09:35

Hans Passant

922,412
146
1,693
2,536

Thank you. I will not need to actually display it, but I will account for proper unicode string handling in my program and above all fix the VARIANT bug. – Prof. Falken Oct 06 '15 at 10:04
Hm, I pasted in the code incorrectly. `result` was a `VARIANT` all along. But the string conversion call needed to ensure a string type, I had no idea about. – Prof. Falken Oct 08 '15 at 13:11
Maybe you can help me with https://stackoverflow.com/questions/33149605/writing-cell-in-excel-from-c-no-value-written-cell-is-blank too? – Prof. Falken Oct 15 '15 at 13:17

OLE bstr not null terminated?

1 Answers1