CODEWITHSUNDEEP
excelvisual-c++c++-clivba
WizzleWuzzle
WizzleWuzzle

Reputation: 160

BSTR value not being returned correctly to Excel from C++/CLI DLL

I am writing a C++CLI DLL in Visual Studio 2010 which I expect to return a string to Excel VBA:

    BSTR __stdcall fnGetData (const double *yr, const double *mon, const char *uid, const char *pwd)

I have the function working, except that the string ends up getting truncated when returned to Excel. Both the MessageBoxA calls in the code below display correct data (all 8760 characters in a giant messagebox dialog) but the resulting MsgBox in Excel displays only the first 998 characters of those 8760. The entire code block of this function is as follows:

    BSTR __stdcall fnGetData (const double *yr, const double *mon, const char *uid, const char *pwd) {
        //convert Excel ByRef arguments to useable values
        string userid(uid);
        string passwd(pwd);
        int year = *yr;
        int month = *mon;

        //transform user and pwd to String
        System::String ^ UserName = gcnew String(userid.c_str());
        System::String ^ PassWord = gcnew String(passwd.c_str());

        //call the HTTPWebRequest/Response function on the class
        htGetData2 ^ h2 = gcnew htGetData2();
        const char* vCharArray = h2->GetStuff(year, month, UserName, PassWord);
        //up to this point everything has worked fine
        MessageBoxA(0, vCharArray, "hi1 from fnGetData", MB_OK | MB_ICONINFORMATION);
        int retValLen = char_traits<char>::length(vCharArray);
        //retValLen is 8760
        BSTR retVal = SysAllocStringByteLen(vCharArray, retValLen);
        int retValLen2 = SysStringLen(retVal);
        //retValLen2 is 4380
        MessageBoxA(0, (LPCSTR)retVal, "hi2 from fnGetData", MB_OK | MB_ICONINFORMATION);
        return retVal;
    }

What am I doing wrong that the BSTR/String in Excel is not the full contents of the data in the vCharArray string? I am not using ATL or MFC, should I retry using those?

The Excel VBA call is:

    Private Declare Function GetData Lib "getpdata.dll" (ByRef year As Double, ByRef month As Double, ByRef uid As Byte, ByRef pwd As Byte) As String

Upvotes: 1

Views: 624

Answers (2)

WizzleWuzzle
WizzleWuzzle

Reputation: 160

Ok, I had been using the Excel VBA MsgBox function to read the string returned from the DLL while I have been writing the DLL. I was unaware until now that MsgBox has a limit of 1024 characters and simply ignores characters after that. The full string is actually being returned, but I was assuming the MsgBox was displaying its full length (as I have been having trouble all along getting more than the first 230 characters of data).

Upvotes: 1

ExceptionalHandler
ExceptionalHandler

Reputation: 72

Writing an intermediate function in your excel's VBA which calls the exported GetData function should fix the problem. Something like:

Private Declare Function GetData Lib "getpdata.dll" (ByRef year As Double, ByRef month As Double, ByRef uid As Byte, ByRef pwd As Byte) As String

Function GetDataEx(ByRef year As Double, ByRef month As Double, ByRef uid As Byte, ByRef pwd As Byte) As String
     GetDataEx = GetData(year, month, uid, pwd)
End Function

In your excel, use the intermediate function in place of the original exported one.

Upvotes: 1

Related Questions