MS Excel Automation via Visual C++ 2005

See more:

C++

COM

MS-Excel

automation

I have been tasked with reading an Excel (.xlsx) file and load the data into a SQL Server database.

Here is my (incomplete) code; I have stripped out error checking and printf's:

#include "stdafx.h"

#include <string>
#include <iostream>
#include <errno.h>

#import "C:/Program Files (x86)/Common Files/microsoft shared/OFFICE15/MSO.DLL" \
    rename("RGB", "MSORGB")

using namespace Office;

#import "C:/Program Files (x86)/Common Files/microsoft shared/VBA/VBA6/VBE6EXT.OLB"

using namespace VBIDE;

#import "C:/Program Files (x86)/Microsoft Office/OFFICE14/EXCEL.EXE" \
    rename("DialogBox",   "ExcelDialogBox") \
    rename("RGB",         "ExcelRGB")       \
    rename("CopyFile",    "ExcelCopyFile")  \
    rename("ReplaceText", "ExcelReplaceText")

int main(int argc, char* argv[])
{
    // 1st parm is path of Excel file
    char xlFilepath[MAX_PATH];
    // strcpy_s(xlFilepath, MAX_PATH, argv[1]);    
    sprintf_s(xlFilepath, MAX_PATH, "%s", argv[1]);
    short firstRow = 5;                // Default first row

    if (argc >= 3)
    {
        firstRow = atoi(argv[2]) + 1;
    }

    // Initialize COM
    HRESULT hr = CoInitialize(NULL);

    // Create Excel Application Object pointer
    Excel::_ApplicationPtr pXL; ///// (1)
    if (FAILED(pXL.CreateInstance("Excel.Application")))
    {
        std::cout << "Failed to initialize Excel::_Application!" << std::endl;
        return 0;
    }

    // Open the Excel Workbook, but don't make it visible
    _bstr_t bFilepath(xlFilepath); ///// (2)
    Excel::WorkbooksPtr pWB = pXL->Workbooks->Open(xlFilepath);
    /*
    if (NULL == pWB)
    {
        std::cerr << "Failed to open Excel file: " << xlFilepath 
                  << ": " << errno << std::endl;
        _exit(3);
    }
    */
    pXL->PutVisible(0, FALSE);

    // Access Excel Worksheet and return pointer to Worksheet cells
    Excel::_WorksheetPtr pWksheet = pXL->ActiveSheet;
    Excel::RangePtr pRange = pWksheet->Cells;

    short currID;
    char  currProvider[31];
    short currentRow = firstRow;

    while (true)
    {
        // Have we reached the end of the data?
        if (pRange->Item[currentRow][1].vt == VT_EMPTY)
        {
            break;
        }
        currID = pRange->Item[firstRow][1].uintVal;
        char* p = _com_util::ConvertBSTRToString(pRange->Item[firstRow][2].bstrVal);
        strcpy_s(currProvider, 31, p);
        delete [] p;

        currentRow++;
    }

	return 0;
}

Input parameters are file path and optional # of rows to skip (default = 4).

I got a pointer to and Excel application (1), convert the file path to a BSTR, then call Open on the file path (2). I cannot tell if this works correctly or not. The documentation for Open() says it returns a pointer to the Workbook; this call returns NULL. It takes over 30 seconds (!) to complete the call. The code right below that terminates the program after displaying the value of errno: 0. So, I commented out that whole block, since there was no error.

Do I need to convert the C-style string to a BSTR for the call to Open()? I originally called it with the string, but changed it when I was not getting the results I wanted.

In the "while" loop is where the program should read each record and insert it into the database (not done). I guessed that when the Worksheet ran out of data, the first column of the current row would be empty, so my program compares it to VT_EMPTY.

Watching this code in the debugger, I can see that every row where the program checks the Variant::vt, its value is 9. For over 6000 records. There are only 500+ records in the xlsx file.

My guesses (documentation on Excel automation are sparse) have not done too well, so now I am asking for help in case anyone knows something that I don't (surely!).

It appears that the call to Excel::Workbooks::Open() succeeded, since errno is zero (does that apply to COM calls to the Excel API?) and nothing blows up when I continue the program after the call. pXL->ActiveSheet gives a valid address, as does pWkshee->Cells. (Should I use Range instead of Cells?)

Naturally, since the vt field of the VARIANT shows 9 (VT_DISPATCH), it makes sense that the value would be an address of an IDispatch instance, instead of a pointer to a C-style string.

What am I doing wrong? The call to Open() does not seem to affect errno, yet I feel that it may not be working correctly: it returns NULL.

Other areas where I do not know what I am doing are where I try to access the Cells property, interpretation of the RangePtr, and the access of each actual cell.

If anyone that knows about Excel automation (or Office automation), I am open for any ideas.

Thank you