CppAutomateExcel.zip
AutoMation CPP Code
#include "stdafx.h"
#include // OLE2 Definitions
#include
// AutoWrap() - Automation helper function...
HRESULT AutoWrap(int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, int cArgs...) {
// Begin variable-argument list...
va_list marker;
va_start(marker, cArgs);
if (!pDisp) {
MessageBox(NULL, L"NULL IDispatch passed to AutoWrap()", L"Error", 0x10010);
_exit(0);
}
// Variables used...
DISPPARAMS dp = { NULL, NULL, 0, 0 };
DISPID dispidNamed = DISPID_PROPERTYPUT;
DISPID dispID;
HRESULT hr;
char szName[200];
// Convert down to ANSI
WideCharToMultiByte(CP_ACP, 0, ptName, -1, szName, 256, NULL, NULL);
// Get DISPID for name passed...
hr = pDisp->GetIDsOfNames(IID_NULL, &ptName, 1, LOCALE_USER_DEFAULT, &dispID);
if (FAILED(hr)) {
//sprintf(buf, "IDispatch::GetIDsOfNames(\"%s\") failed w/err 0x%08lx", szName, hr);
//MessageBox(NULL, buf, "AutoWrap()", 0x10010);
_exit(0);
return hr;
}
// Allocate memory for arguments...
VARIANT *pArgs = new VARIANT[cArgs + 1];
// Extract arguments...
for (int i = 0; iInvoke(dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, autoType, &dp, pvResult, NULL, NULL);
if (FAILED(hr)) {
//sprintf(buf, "IDispatch::Invoke(\"%s\"=%08lx) failed w/err 0x%08lx", szName, dispID, hr);
//MessageBox(NULL, buf, "AutoWrap()", 0x10010);
_exit(0);
return hr;
}
// End variable-argument section...
va_end(marker);
delete[] pArgs;
return hr;
}
int main()
{
// Initialize COM for this thread...
CoInitialize(NULL);
// Get CLSID for our server...
CLSID clsid;
HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);
if (FAILED(hr)) {
::MessageBox(NULL, L"CLSIDFromProgID() failed", L"Error", 0x10010);
return -1;
}
// Start server and get IDispatch...
IDispatch *pXlApp;
hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp);
if (FAILED(hr)) {
::MessageBox(NULL, L"Excel not registered properly", L"Error", 0x10010);
return -2;
}
/* Make it visible (i.e. app.visible = 1)
{
VARIANT x;
x.vt = VT_I4;
x.lVal = 1;
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlApp, L"Visible", 1, x);
}
*/
// Get Workbooks collection
IDispatch *pXlBooks;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Workbooks", 0);
pXlBooks = result.pdispVal;
}
// Call Workbooks.Open() to get a new workbook...
IDispatch *pXlBook;
{
VARIANT vtFileName;
vtFileName.vt = VT_BSTR;
vtFileName.bstrVal = SysAllocString(L"C:\\Users\\UserName\\Desktop\\test.xls");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks, L"Open", 1, vtFileName); // reversed order is using more options
pXlBook = result.pdispVal;
}
// Get ActiveSheet object
IDispatch *pXlSheet;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0);
pXlSheet = result.pdispVal;
}
// pXlBook->SaveAs
{
// Convert the NULL-terminated string to BSTR.
VARIANT vtFileName;
vtFileName.vt = VT_BSTR;
vtFileName.bstrVal = SysAllocString(L"C:\\Users\\UserName\\Desktop\\test_sheet1.csv");
VARIANT vtFormat;
vtFormat.vt = VT_I4;
vtFormat.lVal = 6; // XlFileFormat::xlCSV
// If there are more than 1 parameters passed, they MUST be pass in
// reversed order. Otherwise, you may get the error 0x80020009.
AutoWrap(DISPATCH_METHOD, NULL, pXlBook, L"SaveAs", 2, vtFormat,
vtFileName);
VariantClear(&vtFileName);
}
//Changing the ActiveSheet
{
{
VARIANT itemn;
itemn.vt = VT_I4;
itemn.lVal = 2;
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Worksheets", 1, itemn);
pXlSheet = result.pdispVal;
}
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXlSheet, L"Activate", 0);
}
}
// pXlBook->SaveAs
{
// Convert the NULL-terminated string to BSTR.
VARIANT vtFileName;
vtFileName.vt = VT_BSTR;
vtFileName.bstrVal = SysAllocString(L"C:\\Users\\UserName\\Desktop\\test_sheet2.csv");
VARIANT vtFormat;
vtFormat.vt = VT_I4;
vtFormat.lVal = 6; // XlFileFormat::xlCSV
// If there are more than 1 parameters passed, they MUST be pass in
// reversed order. Otherwise, you may get the error 0x80020009.
AutoWrap(DISPATCH_METHOD, NULL, pXlBook, L"SaveAs", 2, vtFormat,
vtFileName);
VariantClear(&vtFileName);
}
// Set .Saved property of workbook to TRUE so we aren't prompted
// to save when we tell Excel to quit...
{
VARIANT x;
x.vt = VT_I4;
x.lVal = 1;
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlBook, L"Saved", 1, x);
}
// Tell Excel to quit (i.e. App.Quit)
AutoWrap(DISPATCH_METHOD, NULL, pXlApp, L"Quit", 0);
// Release references...
pXlSheet->Release();
pXlBook->Release();
pXlBooks->Release();
pXlApp->Release();
// Uninitialize COM for this thread...
CoUninitialize();
return 0;
}
