Solved

copying excel sheets using automation

Posted on 1999-01-14
1
302 Views
Last Modified: 2010-04-02
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?
0
Comment
Question by:adresko
1 Comment
 

Accepted Solution

by:
izi13 earned 50 total points
ID: 1182114
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

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Written by John Humphreys C++ Threading and the POSIX Library This article will cover the basic information that you need to know in order to make use of the POSIX threading library available for C and C++ on UNIX and most Linux systems.   [s…
What is C++ STL?: STL stands for Standard Template Library and is a part of standard C++ libraries. It contains many useful data structures (containers) and algorithms, which can spare you a lot of the time. Today we will look at the STL Vector. …
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

813 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now