?
Solved

Saving an excel file with automation (using       SaveWorkspace method)

Posted on 2004-11-10
13
Medium Priority
?
2,349 Views
Last Modified: 2013-11-20
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
0
Comment
Question by:galneweinhaw
12 Comments
 

Author Comment

by:galneweinhaw
ID: 12548377
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
 

Author Comment

by:galneweinhaw
ID: 12548553
    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
 
LVL 86

Expert Comment

by:jkr
ID: 12549064
This might be a horrible hack, but would deleting the originaly file work, e.g.

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

?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:galneweinhaw
ID: 12549539
heh....just tried that and it did some bad things.....
0
 
LVL 12

Expert Comment

by:migel
ID: 12552850
Hi!
try to save book
for example:
book->SaveAs(COleVariant(fullFileName), COleVariant((long)xlExcel9795),COleVariant(""), COleVariant(""),COleVariant(VARIANT_FALSE),COleVariant(VARIANT_FALSE),xlExclusive);
            
0
 

Author Comment

by:galneweinhaw
ID: 12608376
It's not accepting the xl... constants that are there

thanks
0
 
LVL 12

Expert Comment

by:migel
ID: 12613393
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
 

Author Comment

by:galneweinhaw
ID: 12628683
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
 
LVL 12

Expert Comment

by:migel
ID: 12629680
Hmm
I think you use MFC wizard generated wrappers?

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

Author Comment

by:galneweinhaw
ID: 12630760
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
 

Author Comment

by:galneweinhaw
ID: 12647004
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
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12665104
Closed, 500 points refunded.

modulo
Community Support Moderator
Experts Exchange
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is to be the first in a series of articles demonstrating the development of a complete windows based application using the MFC classes.  I’ll try to keep each article focused on one (or a couple) of the tasks that one may meet.   Introductio…
Introduction: Dialogs (1) modal - maintaining the database. Continuing from the ninth article about sudoku.   You might have heard of modal and modeless dialogs.  Here with this Sudoku application will we use one of each type: a modal dialog …
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.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Suggested Courses
Course of the Month14 days, 5 hours left to enroll

807 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