copying excel sheets using automation

i'm using automation to automate excel and do a few things automatically.. (isn't auto such a great word?) anyway, i'm trying to copy a worksheet from one workbook to another, but i can't figure out how.. documentation on this is pretty vague.. the program is actually an mfc based program, but to automate excel, i just created the excel classes from the excel type library..

i figure i need to use the worksheet::add function, BUT i don't understand the parameters if that's what i'm really supposed to use..

any suggestions?
adreskoAsked:
Who is Participating?
 
izi13Connect With a Mentor Commented:
Hi,
I don't know if you still need the answer, but anyway here is a function, which can help you:

void copy()
{
      _Application            app;
      COleVariant            wsSource("Sheet1"), // name of sheet to copy
                        wsDest("Sheet2");      // name of sheet to copy before
      

      LPCTSTR wbSource="Book1.xls";      // name of source workbook
      LPCTSTR wbDest="Book2.xls";            // name of destination workbook

      if (!app.CreateDispatch("Excel.Application"))
      {
            MessageBox("Failed to instantiate Excel");
            return;
      }

      try
      {

            Workbooks wbs(app.GetWorkbooks());

            _Workbook wbToCopyFrom(wbs.Open(wbSource,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing));
            _Workbook wbToCopyTo(wbs.Open(wbDest,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing));

            Worksheets wsheetsSource(wbToCopyFrom.GetWorksheets());
            Worksheets wsheetsDest(wbToCopyTo.GetWorksheets());

            _Worksheet wsToCopy(wsheetsSource.GetItem(wsSource));
            _Worksheet wsToCopyBefore(wsheetsDest.GetItem(wsDest));

            VARIANT            var;
            var.vt=VT_DISPATCH;
            var.pdispVal=wsToCopyBefore.m_lpDispatch;

            wsToCopy.Copy(var,vtMissing);
            wbToCopyTo.Save();

      }
      catch(COleDispatchException* e)
      {
            TRACE(e->m_strDescription);
            MessageBox(e->m_strDescription,e->m_strSource, MB_OK);
      }

      app.Quit();
      app.ReleaseDispatch();
}

0
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.

All Courses

From novice to tech pro — start learning today.