locked
Excel Spreadsheet IO From C++ RRS feed

  • 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



    Wednesday, February 19, 2014 12:09 AM

Answers

  • 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

    • Marked as answer by Anna Cc Tuesday, February 25, 2014 7:49 AM
    Wednesday, February 19, 2014 9:50 PM

All replies

  • 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.

    Wednesday, February 19, 2014 6:49 AM
  • Thanks, I'll try that.

    Wednesday, February 19, 2014 5:18 PM
  • 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


    Wednesday, February 19, 2014 7:27 PM
  • For the rest of parameters specify vtMissing. See an example of SaveAs in C++:: http://code.msdn.microsoft.com/office/CppAutomateExcel-be36992e

    Wednesday, February 19, 2014 8:09 PM
  • 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

    • Marked as answer by Anna Cc Tuesday, February 25, 2014 7:49 AM
    Wednesday, February 19, 2014 9:50 PM
  • 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

    Tuesday, February 25, 2014 5:08 PM