[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 975
  • Last Modified:

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!
0
galneweinhaw
Asked:
galneweinhaw
1 Solution
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now