Saving an excel file with automation (using       SaveWorkspace method)

Posted on 2004-11-10
Last Modified: 2013-11-20
I have an excel application I am writing to, but when I want to save it  using:


A window pops up saying:

"A file named 'C\............\......xls' already exists in this location.  Do you want to replace it?"

Is there a way to automatically say yes to this, or another way of saving so it doesn't popup?

thanks for the help.

I am using Visual C++ 6.0 and excel 9
Question by:galneweinhaw

    Author Comment

    I thik I might be trying to open up a template rather than a file:

                // Create workbook
                book = books.Add(COleVariant(fullFileName));

    which might be my problem... if this is the problem, how do I open an existing file?

    Author Comment

        I changed the way I was opening the file:

                      // Open workbook        
                      book = books.Open(fullFileName,
                      VOptional, VOptional, VOptional, VOptional,
                      VOptional, VOptional, VOptional, VOptional,
                      VOptional, VOptional, VOptional, VOptional);

    But I am still getting a "do you want to save your changes" pop-up when I try and close it.

    How can I auto accept this pop-up?
    LVL 86

    Expert Comment

    This might be a horrible hack, but would deleting the originaly file work, e.g.



    Author Comment

    heh....just tried that and it did some bad things.....
    LVL 12

    Expert Comment

    try to save book
    for example:
    book->SaveAs(COleVariant(fullFileName), COleVariant((long)xlExcel9795),COleVariant(""), COleVariant(""),COleVariant(VARIANT_FALSE),COleVariant(VARIANT_FALSE),xlExclusive);

    Author Comment

    It's not accepting the xl... constants that are there

    LVL 12

    Expert Comment

    Hi here is defs for enums
    enum XlFileFormat
        xlAddIn = 18,
        xlCSV = 6,
        xlCSVMac = 22,
        xlCSVMSDOS = 24,
        xlCSVWindows = 23,
        xlDBF2 = 7,
        xlDBF3 = 8,
        xlDBF4 = 11,
        xlDIF = 9,
        xlExcel2 = 16,
        xlExcel2FarEast = 27,
        xlExcel3 = 29,
        xlExcel4 = 33,
        xlExcel5 = 39,
        xlExcel7 = 39,
        xlExcel9795 = 43,
        xlExcel4Workbook = 35,
        xlIntlAddIn = 26,
        xlIntlMacro = 25,
        xlWorkbookNormal = -4143,
        xlSYLK = 2,
        xlTemplate = 17,
        xlCurrentPlatformText = -4158,
        xlTextMac = 19,
        xlTextMSDOS = 21,
        xlTextPrinter = 36,
        xlTextWindows = 20,
        xlWJ2WD1 = 14,
        xlWK1 = 5,
        xlWK1ALL = 31,
        xlWK1FMT = 30,
        xlWK3 = 15,
        xlWK4 = 38,
        xlWK3FM3 = 32,
        xlWKS = 4,
        xlWorks2FarEast = 28,
        xlWQ1 = 34,
        xlWJ3 = 40,
        xlWJ3FJ3 = 41

    enum XlSaveAsAccessMode
        xlExclusive = 3,
        xlNoChange = 1,
        xlShared = 2

    Author Comment

    thanks again migel, I'm getting this error:

    error C2660: 'SaveAs' : function does not take 7 parameters

    this is the line:
    book.SaveAs(COleVariant(saveFile), COleVariant((long)xlExcel9795),COleVariant(""), COleVariant(""),COleVariant(VARIANT_FALSE),COleVariant(VARIANT_FALSE),xlExclusive);

    I tried the book ->SaveAs(..)

    but got all this:

    error C2819: type '_Workbook' does not have an overloaded member 'operator ->'
    see declaration of '_Workbook'
    error C2227: left of '->SaveAs' must point to class/struct/union
    LVL 12

    Expert Comment

    I think you use MFC wizard generated wrappers?

    so please check signature for this method and use . insted ->

    Author Comment

    I'm not sure what you mean by MFC wiz generated wrappers.

    Either way, I think I solved the problem (why is it always so simple in hindsight?)

    This is what worked:


    Author Comment

    I've been trying to do quite a bit with excel automation so if anyone has a question I might be able to help them, feel free to contact me however you do that on this site.

    For the record in case anyone else needs to do this, here is how to open an existing excel file, edit a cell, save it, and close it:

    /*First add the excel library to your project by doing this:
    1.Open ClassWizard (CTRL+W), click the Automation tab, click Add Class, and select From a type library.
    2. Go to the directory where you installed Office (for example, C:\Program Files\Microsoft Office\Office) and choose Excel9.olb (or Excel8.olb or Excel.olb depending on your version of excel)
    3. Select all the classes that ClassWizard finds, and click OK to get back to your project. ClassWizard has generated some automation wrapper classes from the PowerPoint type library and created the files Excel9.h and Excel9.cpp. */

    // for Excel 1998, use #include "excel8.h"
    // for Excel 2000, use #include "excel9.h"
    // for Excel 2002, use #include "excel.h"

    #include "excel9.h"

    _Application app;     // Excel 8 Application object
    _Workbook book;     // Workbook object
    Workbooks books;     // Workbooks collection object
    _Worksheet sheet;     // Worksheet object
    Worksheets sheets;     // Worksheets collection object
    Range range;     // Worksheet range

    //Set the filename and path, or get it from somewhere
    CString filemame = "C:\\My Documents\\MyExcelFile.xls";

    // Start Excel and get Application object
    if (!app.CreateDispatch("Excel.Application"))
         AfxMessageBox("Could not open Excel");
         CWaitCursor wait;     // Display the hourglass (wait) cursor if you want

         app.SetVisible(false); //Do you want excel to be visible during automation?
         books = app.GetWorkbooks();

         // Instantiate Excel and open an existing workbook.
         // OLE Variant for Optional
         COleVariant VOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
         book = books.Open(filename,
                VOptional, VOptional, VOptional, VOptional,
                VOptional, VOptional, VOptional, VOptional,
                VOptional, VOptional, VOptional, VOptional);

         // Create worksheets collection object
         sheets = book.GetWorksheets();

         // Open Sheet1 worksheet and edit some values
         sheet = sheets.GetItem(COleVariant("Sheet1"));

         // Edit cell A1
         range = sheet.GetRange(COleVariant("A1"), COleVariant("A1"));
         range.SetValue("This is cell: A1");

         book.Save(); // Save file
         books.Close(); // Close File
         app.Quit(); // Close Excel

    Accepted Solution

    Closed, 500 points refunded.

    Community Support Moderator
    Experts Exchange

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Introduction: Finishing the grid – keyboard support for arrow keys to manoeuvre, entering the numbers.  The PreTranslateMessage function is to be used to intercept and respond to keyboard events. Continuing from the fourth article about sudoku. …
    Introduction: Database storage, where is the exe actually on the disc? Playing a game selected randomly (how to generate random numbers).  Error trapping with try..catch to help the code run even if something goes wrong. Continuing from the seve…
    This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now