Open Excel file, edit some cells, close excel file

How can I open an excel file, edit a few cells on a particular sheet, then close the excel file.

I would also like to do this without excel ever being visible

thanks for the help!
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've got the Excel 9 class added, not quite sure what to do with it though.
0
galneweinhawAuthor Commented:
ok, I'm working away on this:

http://support.microsoft.com/kb/178749/EN-US/

but I'm getting an error:

fatal error C1083: Cannot open include file: 'excel.h': No such file or directory


coming from
#include "excel.h"
0
AxterCommented:
Did you try "Excel8.h" ??
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

AxterCommented:
These files generated by class wizard from Excel type library. Go View->Class Wizard->Add class->From type library, find file Excel9.olb (or Excel8.olb) its present where your Excel.exe file is. Select classes you want to add. Click OK and class wizard will generate Excel9.h and .cpp for you.
Regards.
0
galneweinhawAuthor Commented:
As I mentioned above, I had added the Excel 9 Class.

needed to include Excel9.h

got excel open, if anyone has some quick answers how to open a specific file and edit cells it would be appreciated!
0
galneweinhawAuthor Commented:
Here's where I'm at so far.... having some trouble figureing out how to edit specific cells....


            _Application app;  // app is the Excel _Application object
            _Workbook book;            // Workbook object
Workbooks books;      // Workbooks collection object

          // Start Excel and get Application object...
         if(!app.CreateDispatch("Excel.Application"))
         {
            AfxMessageBox("Couldn't start Excel.");
         }
         else
         {
                  CWaitCursor wait;      // Display wait cursor
                  //Make Excel Visible and display a message
                  app.SetVisible(TRUE);
                  // Get workbooks collection object
                  books = app.GetWorkbooks();
                  books.AttachDispatch(app.GetWorkbooks());
                  
                  // dummy variable used to fill up the method arguments
                  COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
                  
                  LPDISPATCH lpWorkbookToUse;
                  lpWorkbookToUse = books.Open(
                  fullFileName,
                  covOptional,
                  covOptional,
                  covOptional,
                  covOptional,
                  covOptional,
                  covOptional,
                  covOptional,
                  covOptional,
                  covOptional,
                  covOptional,
                  covOptional,
                  covOptional);


         }
0
galneweinhawAuthor Commented:
moving right along I'm now upto this, but the program crashes after excel opens at the location pointed out below.  Any help would be greatly appreciated.... most of this is gobbeldy gook to me, really just copy and pasting.  I would be very grateful if someone could walk me through what I am trying to do :)


            _Application app;  // app is the Excel _Application object
            _Workbook book;            // Workbook object
            Workbooks books;      // Workbooks collection object
            _Worksheet sheet;
            Worksheets sheets;

            Range range;          // Used for Microsoft Excel 97 components.

            LPDISPATCH lpDisp;    // Often reused variable.
            

          // Start Excel and get Application object...
         if(!app.CreateDispatch("Excel.Application"))
         {
            AfxMessageBox("Couldn't start Excel.");
         }
         else
         {
                  CWaitCursor wait;      // Display wait cursor
                  //Make Excel Visible and display a message
                  app.SetVisible(TRUE);
                  // Get workbooks collection object
                  lpDisp = app.GetWorkbooks();
                  books.AttachDispatch(lpDisp);
                  
                  // dummy variable used to fill up the method arguments
                  COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
                  
                  LPDISPATCH lpWorkbookToUse;
                  lpWorkbookToUse = books.Open(
                  fullFileName,
                  covOptional,
                  covOptional,
                  covOptional,
                  covOptional,
                  covOptional,
                  covOptional,
                  covOptional,
                  covOptional,
                  covOptional,
                  covOptional,
                  covOptional,
                  covOptional);

                  // Get the Sheets collection and attach the IDispatch pointer to your
                  // sheets object.
                  lpDisp = book.GetSheets();       <<<<<<<< CRASHING ***********
                  sheets.AttachDispatch(lpDisp);  <<<<<<<< HERE *************

                  sheet = sheets.GetItem( COleVariant((short)(1)) );
                  sheet.AttachDispatch(lpDisp);

                  // Fill range A1 with "1/25/98", the settlement date.
                  lpDisp = sheet.GetRange(COleVariant("A1"), COleVariant("A1"));
                  ASSERT(lpDisp);
                  range.AttachDispatch(lpDisp);
                  range.SetValue(COleVariant("test")); // Excel 97 & Excel 2000

  }

0
grg99Commented:
Hmm, IMHO these hooks into Excel are like doing brain surgery with blinders on.

One way around it is to export the file as comma-separated-values, edit the values in the file using good-old text-file operations, then write out the file as csv's.  

0
galneweinhawAuthor Commented:
I figured this guy out.  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 wants to try this:

/*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
C++

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.