Saving an excel file with automation (using SaveWorkspace method)

I have an excel application I am writing to, but when I want to save it  using:

            app.SaveWorkspace(COleVariant(fullFileName));
            app.Quit();

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
galneweinhawAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

galneweinhawAuthor Commented:
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?
0
galneweinhawAuthor Commented:
    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?
0
jkrCommented:
This might be a horrible hack, but would deleting the originaly file work, e.g.

         DeleteFile(fullFileName);
         app.SaveWorkspace(COleVariant(fullFileName));
         app.Quit();

?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

galneweinhawAuthor Commented:
heh....just tried that and it did some bad things.....
0
migelCommented:
Hi!
try to save book
for example:
book->SaveAs(COleVariant(fullFileName), COleVariant((long)xlExcel9795),COleVariant(""), COleVariant(""),COleVariant(VARIANT_FALSE),COleVariant(VARIANT_FALSE),xlExclusive);
            
0
galneweinhawAuthor Commented:
It's not accepting the xl... constants that are there

thanks
0
migelCommented:
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
};
0
galneweinhawAuthor Commented:
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
0
migelCommented:
Hmm
I think you use MFC wizard generated wrappers?

so please check signature for this method and use . insted ->
0
galneweinhawAuthor Commented:
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:


            book.Save();
            books.Close();
            app.Quit();
0
galneweinhawAuthor Commented:
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");
else
{
     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
}
0
moduloCommented:
Closed, 500 points refunded.

modulo
Community Support Moderator
Experts Exchange
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.