How To Read Data From ODBC And Send It Into Excel Defined Names in C++

Hello C++ COM Experts,

We have developed a way of storing SQL in the cloud, reading it, resolving parameters, running it, fetching results, and sending the results into Excel PowerPivot and refreshing the charts. This is very useful in the world of BI/Reporting. But what we have done requires a third party vendor product so I won't mention it here. That's not fair.

While inventing all this it has occurred to me that for the simpler SQL / Reports we could just write a C++ program. We have mountains of C++ that read ODBC so that's no problem. I have been writing C++ for 25 years but I am not a hard core C++ programmer.

We have zero code writing to the Excel using COM and we believe that COM is the "recommended approach".

We got the code snippet below from co-pilot. When we imported EXCEL.EXE we get errors for unresolved references and so then we imported MSO.DLL. And it got unresolved references and co-pilot said to import VBE7EXT.OLE. But we can't find that file anywhere on any of our machines. So we are guessing that is a mistake.

When I re-create a new project to test some times the imports generate the Excel.tlh file etc and sometimes not. We have not been able to figure out how to reliably create a project in VS 2026 and get this code snippet to compile. We can't even get it to reliably generate the tlh files. We are creating a C++ windows console application at the beginning because that's what co-pilot said to create for this code snippet.

We have searched all over for examples and videos and that sort of thing. Amazingly to us we can't find any reliable detailed instructions on how to go about getting C++ to read/write to Excel via the COM model. (Or another seemingly recommended way.) I don't know if we are just missing something obvious because I would have thought that lots of people are doing this because it's useful. Everything we find is pretty much a repeat of the code snippet below.

If anyone knows of any post or video or reliable source that shows exactly how this should work? I would really appreciate the link please.

I don't know what the cause of these problems of unresolved references and not generating the tlh files is. I have tried "fresh installs" of VS 2026. When the tls files do get generated we don't know why and how, they just suddenly appear and then go away again after a clean. We have tried cleaning and rebuilding. I have pretty much spent about 12 hours trying to get this piece of code to compile and I just can't figure out why it's not working. So if you have any pointers I would very much appreciate them.

Thank you in advance for any assistance you may be able to offer me.

Best Regards
Peter Jones.

#include <iostream>

#include <vector>

#include <string>

#include <windows.h>

#include <sqlext.h>

#include <comdef.h>

//VBA COM

#import "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA7.1\VBE7EXT.OLE" \

rename("Reference", "VBReference") \

rename("References", "VBReferences")   
```// MSO COM

#import "C:\\Program Files\\Common Files\\Microsoft Shared\\OFFICE16\\MSO.DLL" \

```javascript
rename("RGB", "MSORGB") \

rename("DocumentProperties", "MSODocumentProperties")
```// Excel COM

#import "C:\\Program Files\\Microsoft Office\\root\\Office16\\EXCEL.EXE" \

```r
rename("DialogBox", "ExcelDialogBox") \

rename("RGB", "ExcelRGB") \

exclude("IFont", "IPicture") \

rename("CopyFile", "ExcelCopyFile") \

rename("ReplaceText", "ExcelReplaceText") \

named_guids \

auto_rename
```using namespace Excel;

// =======================

// SQL SERVER (ODBC PART)

// =======================

std::vector<std::vector<std::wstring>> FetchDataFromSQL() {

```cpp
SQLHENV hEnv;

SQLHDBC hDbc;

SQLHSTMT hStmt;

std::vector<std::vector<std::wstring>> result;

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);

SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);

SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);

// ✅ Adjust connection string

SQLWCHAR connStr[] =

    L"DRIVER={ODBC Driver 17 for SQL Server};"

    L"SERVER=localhost;"

    L"DATABASE=TestDB;"

    L"Trusted_Connection=yes;";

SQLDriverConnect(hDbc, NULL, connStr, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);

SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);

// ✅ Query

SQLExecDirect(hStmt, (SQLWCHAR*)L"SELECT TOP 10 Name, Value FROM MyTable", SQL_NTS);

SQLWCHAR col1[256], col2[256];

while (SQLFetch(hStmt) == SQL_SUCCESS) {

    SQLGetData(hStmt, 1, SQL_C_WCHAR, col1, sizeof(col1), NULL);

    SQLGetData(hStmt, 2, SQL_C_WCHAR, col2, sizeof(col2), NULL);

    result.push_back({ col1, col2 });

}

SQLFreeHandle(SQL_HANDLE_STMT, hStmt);

SQLDisconnect(hDbc);

SQLFreeHandle(SQL_HANDLE_DBC, hDbc);

SQLFreeHandle(SQL_HANDLE_ENV, hEnv);

return result;
```}

// =======================

// EXCEL AUTOMATION PART

// =======================

void PushToExcelAndRefresh(const std::vector<std::vector<std::wstring>>& data) {

```cpp
CoInitialize(NULL);

try {

    _ApplicationPtr app;

    app.CreateInstance(L"Excel.Application");

    app->Visible = VARIANT_FALSE;

    _WorkbookPtr wb = app->Workbooks->Open(L"C:\\temp\\Sample.xlsx");

    // ✅ Named range

    RangePtr range = wb->Names->Item("InputData")->RefersToRange;

    long rows = (long)data.size();

    long cols = (long)data[0].size();

    // ✅ Build SAFEARRAY

    SAFEARRAYBOUND bounds[2];

    bounds[0].lLbound = 1; bounds[0].cElements = rows;

    bounds[1].lLbound = 1; bounds[1].cElements = cols;

    SAFEARRAY* sa = SafeArrayCreate(VT_VARIANT, 2, bounds);

    long idx[2];

    for (long i = 0; i < rows; i++) {

        for (long j = 0; j < cols; j++) {

            idx[0] = i + 1;

            idx[1] = j + 1;

            VARIANT val;

            VariantInit(&val);

            val.vt = VT_BSTR;

            val.bstrVal = SysAllocString(data[i][j].c_str());

            SafeArrayPutElement(sa, idx, &val);

        }

    }

    // ✅ Push data into Excel

    range->Value2 = _variant_t(sa);

    std::cout << "Data written to Excel.\n";

    // ✅ Refresh everything

    wb->RefreshAll();

    // ✅ Wait for async queries (Power Query)

    app->CalculateUntilAsyncQueriesDone();

    // ✅ Explicit Power Pivot refresh (if available)

    try {

        wb->Model->Refresh();

    }

    catch (...) {

        std::cout << "Model refresh not supported.\n";

    }

    wb->Save();

    wb->Close(VARIANT_FALSE);

    app->Quit();

    std::cout << "Excel refresh complete.\n";

}

catch (_com_error& e) {

    std::wcout << "COM Error: " << e.ErrorMessage() << std::endl;

}

CoUninitialize();
```}

// =======================

// MAIN PIPELINE

// =======================

int main() {

```cpp
std::cout << "Fetching data from SQL Server...\n";

auto dataset = FetchDataFromSQL();

if (dataset.empty()) {

    std::cout << "No data returned.\n";

    return 0;

}

std::cout << "Pushing data to Excel...\n";

PushToExcelAndRefresh(dataset);

std::cout << "Pipeline complete.\n";

return 0;
```}