Excel Spreadsheet IO From C++

Question

I want to read from one Excel spreadsheet and write to another.   The code snippet below works fine.   When the Close() in the last line of the code is called, I'm prompted to save the new spreadsheet.  This works fine but I'd like to change it so that I can specify the filename of the file that the spreadsheet will be saved to in a string, so that the Close() call will save the output in that file.  Something like this:

LPCWSTR  pszOutputFile = {"C:\\My Documents\\OutputFile.xlsx"} ;

// Close all open workbooks and write and close the output file (overwriting an old file if it already exists.)

set at element or member of ExcelAppPtr->Workbooks to pszOutputFile

ExcelAppPtr->Workbooks->Close();  // close the workbook and write the output spreadsheet to the file

Here's the code that works that I'd like to change:

//MicroSoft Office Objects
#import \
"C:\\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14\mso.dll" \
rename("DocumentProperties", "DocumentPropertiesXL") \
rename("RGB", "RBGXL")
//Microsoft VBA Objects
#import \
"C:\\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\vbe6ext.olb"

//Excel Application Objects
#import "C:\\Program Files (x86)\Microsoft Office\OFFICE14\EXCEL.EXE" \
rename("DialogBox", "DialogBoxXL") rename("RGB", "RBGXL") \
rename("DocumentProperties", "DocumentPropertiesXL") \
rename("ReplaceText", "ReplaceTextXL") \
rename("CopyFile", "CopyFileXL") \
exclude("IFont", "IPicture") no_dual_interfaces

LPCWSTR  pszInFile = {"C:\\My Documents\\test.xlsx"} ;


Excel::_ApplicationPtr ExcelAppPtr;

//Initialise COM interface
CoInitialize(NULL);

//Start the Excel Application
ExcelAppPtr.CreateInstance(L"Excel.Application");

// Open the Workbook that contains the survey data as downloaded from qualtrix
ExcelAppPtr->Workbooks->Open(pszInFile);

// Get a pointer to the worksheet
Excel::_WorksheetPtr pSheet = ExcelAppPtr->ActiveSheet;

// Get a pointer to the cells in the worksheet
Excel::RangePtr pRange = pSheet->Cells;

// Process the data form the spreadsheet
string strUserName ;
strUserName = (bstr_t)(pRange->Item[2][1]) ;
string strUserPhone ;
strUserPhone = (bstr_t)(pRange->Item[2][2]) ;

// Do some work with the data from the spreadsheet
FixThePhoneNumber( strUserPhone ) ;

// Write the results to another spreadsheet
ExcelAppPtr->Workbooks->Add(Excel::xlWorksheet); 
// Get a pointer to the worksheet
Excel::_WorksheetPtr pSheetOut = ExcelAppPtr->ActiveSheet;
//Set the name of the sheet
pSheetOut->Name = "Output Data";
// Get a pointer to the cells in the worksheet
Excel::RangePtr pRangeOut = pSheetOut->Cells;
// Write the output cell
pSheetOut->Item[2][2] = strUserPhone.c_str() ;  // and lots of similar assignments

// Close all open workbooks
ExcelAppPtr->Workbooks->Close();

Any help appreciated,

Mike

Answer 1

If you are interesting in saving the new workbook without prompting, then call SaveAs (before calling Close) on _WorkbookPtr object that is returned by Workbooks->Add.

Answer 2

Thanks, I'll try that.

Answer 3

Hmm.   I cannot work out what goes in the parameters of SaveAs().  The examples I find are all C# or VB and I cannot work out what to write in c++.  The call I tried, below, fails to compile.  It does not like the way I'm trying to specify the format value. It then complains about the number of parameters I'm supplying to SaveAs().  The c# and vb examples seem to supply 11 or 12 values, but most are defaulted.

SaveAs(L"Errors.xlsx", Excel::XlFileFormat::xlWorkbookNormal, // what else?  );

I cannot find a c or c++ header file for the format values.  How do you default a value in the call?

Mike

Answer 4

For the rest of parameters specify vtMissing. See an example of SaveAs in C++:: http://code.msdn.microsoft.com/office/CppAutomateExcel-be36992e

Answer 5

Thanks that help me find the rest of what I needed.  This is the code that did what I needed:

ExcelAppPtr->DisplayAlerts = false; // Silently overwrite existing log
errorsWorkbookPtr->SaveAs(szLogFileName, Excel::xlOpenXMLWorkbook, vtMissing,
	vtMissing, vtMissing, vtMissing, Excel::xlExclusive );
ExcelAppPtr->DisplayAlerts = false; // Restore safer behavior

Thanks to all who offered help.

Mike

Answer 6

Sorry, there's a mistake in what I wrote as the final solution.  It should be:

ExcelAppPtr->DisplayAlerts = false; // Silently overwrite existing log
errorsWorkbookPtr->SaveAs(szLogFileName, Excel::xlOpenXMLWorkbook, vtMissing,
	vtMissing, vtMissing, vtMissing, Excel::xlExclusive );
ExcelAppPtr->DisplayAlerts = true; // Restore safer behavior, ie ask to overwrite