Excel automation isn't working in C++Builder XE7

Question

I am trying to use the code below to open an .xlsx file from C++Builder in RAD Studio XE7:

#include "ComObj.hpp"

Variant Excel = CreateOleObject("Excel.Application");
Variant Books = Excel.OlePropertyGet("Workbooks");
Excel.OlePropertySet("Visible", true);
// An escape character is missing but the problem remains
Books.OleProcedure("Open", L"D:\1.xlsx"); // exception here

But the last line causes exception with message:

Project2.exe raised exception class EOleException with message 'Unfortunately, we were unable to find the file TRUE.xlsx. It may have been moved, renamed or deleted?'.

Screen with place where the source breaks

The code in Delphi seems to work fine:

uses ComObj;

var
  Excel, Books: Variant;
begin
    Excel := CreateOleObject('Excel.Application');
    Books := Excel.Workbooks;
    Excel.Visible := True;
    Books.Open('D:\1.xlsx'); // code passes
end;

Does anyone know the solution?

Update1: The following code in VB also works fine:

Sub Button1_Click()
 Dim xlApp As Excel.Application
 Dim xlBooks As Excel.Workbooks

 Set xlApp = CreateObject("Excel.Application")
 Set xlBooks = xlApp.Workbooks

 xlApp.Visible = True
 xlBooks.Open ("D:\1.xlsx")
End Sub

Update2: Sending a raw string literal causes the same exception.

Books.OleProcedure("Open", uR"(D:\1.xlsx)");

It also doesn't seem to be an environment problem. I tested the example at several computers with no effect.

Answer 1

In C++ the backslash character is the escape character in string literals and so needs itself to be escaped. Instead of

L"D:\1.xlsx"

you need to write

L"D:\\1.xlsx"

The error message is strange though. It is almost as though some conversion in the COM dispatch code interprets the 1 as a truth value and converts it to text. You could try passing the filename as a System::WideString which might side-step the issue.

System::WideString filename = L"D:\\1.xlsx";
Books.OleProcedure("Open", filename);

What you are reporting seems almost too weird to be true though! I have to confess I'm having some trouble believing it because it is so outlandish.

Answer 2

The problem seems very specifically related to the use of C++ and thus the way that C++ compilers deal with literal strings (or at least this particular C++ compiler). Quite what the problem is in this case I cannot say - it could even be a bug in the compiler since (seemingly) correct escaping doesn't address the problem.

You could employ various strategies to eliminate the possibility that it is handling of the literal string in this case. But since this does involve a literal string and since you are using XE7 I believe you should be able to bypass this more explicitly by expressing the literal as a raw string (as per C++11, which is/should be supported by C++ Builder XE7):

Books.OleProcedure("Open", uR"(D:\1.xlsx)"); // uR indicates UTF-16 Raw string

Note that with raw string literals you specifically do not escape \ chars.

Answer 3

Just faced a similar problem with C++ Builder XE7 and thought I would share what I found. Any attempt to set or send a string literal of any type caused either a Bad Variable Type error, was set as TRUE in Excel like Dmitrii or caused a memory error.

What I eventually found was that there is an OLEVariant type in C++ Builder that contains data types compatible with OLE automation and at runtime can convert as required.

I first tried changing all my Variant variables to OLEVariant without success but then was able to use a cast on any string I sent to make it work, even older char strings. So you might try

Books.OleProcedure("Open", (OleVariant)L"D:\1.xlsx");

even without the WideString formatting it worked for what I was doing so this might work as well

Books.OleProcedure("Open", (OleVariant)"D:\1.xlsx");

As for escaping I'm not sure if you need to escape the backslash in the second case with a simple string or not.