Pasting images to Excel OLE server

Hi folks,

I have a problem where I can't found decent documentation for anywhere:

I accessed the Excel Ole server from a MFC program and now I want to put a bitmap into a cell of a worksheet; I already have a pointer Range pointer to this cell.

How is this done??
I know there's the Range.PasteSpecial() function, but what do I have to put into it?

(MSDN library is really a mess for this topic).

Help would really be welcomed..

Regards,
Matthias.

It may not have what you want but see

http://www.codeguru.com/forum/showth...el+And+Past%2A

for some web fingered knob twisting about pasting bitmaps into Excel.

Dear Tom,

Thanks for your reply.
However, I tried it that way, but I get an runtime exception when calling the sheet.Paste() method, which states the Paste method to be invalid.
Nevertheless, the bitmap is in the clipboard before calling Paste(): I tried to paste it externally (manually) into Excel , and that worked.
Is there something I have to consider when setting the parameters for the Paste method??

Regards,
Matthias (out of his depth)

Could you post the part of your code where you try to paste the bitmap?

Here's the code in question:

LPDISPATCH pRange, pWorkbooks;

// get excel window
CWnd* pWnd = CWnd::FindWindow("XLMAIN", NULL);
if (pWnd != NULL) {
TRACE("Excel window found\n");
pWnd->ShowWindow(SW_SHOWNORMAL);
pWnd->UpdateWindow();
pWnd->BringWindowToTop();
}

//set sheet
m_app.SetSheetsInNewWorkbook(1);

// attach to worksheet
VERIFY(pWorkbooks =m_app.GetWorkbooks());
m_workbooks.AttachDispatch(pWorkbooks);

LPDISPATCH pWorkbook = NULL;
if (m_workbooks.GetCount() == 0) {
// Add returns a Workbook pointer, but we don't have a Workbook class
pWorkbook = m_workbooks.Add(); // Save the pointer for later release
}
LPDISPATCH pWorksheets = m_app.GetWorksheets();
ASSERT(pWorksheets != NULL);
m_worksheets.AttachDispatch(pWorksheets);
LPDISPATCH pWorksheet = m_worksheets.GetItem(COleVariant((short) 1));
m_worksheet.AttachDispatch(pWorksheet);
m_worksheet.Select();

// attach to first cell
VERIFY(pRange = m_worksheet.GetRange(COleVariant("A1")));
m_range[0].AttachDispatch(pRange);

// Tom's code
OpenClipboard();
EmptyClipboard();
CBitmap MyBitmap;
MyBitmap.LoadBitmap(IDB_BITMAP1);
HBITMAP MyBitmapHandle = (HBITMAP)MyBitmap;
SetClipboardData(CF_BITMAP, MyBitmapHandle);
CloseClipboard();

// select first cell
m_range[0].Select();
// this worked: m_range[0].SetValue(COleVariant(3.14159));

// paste => exception!
m_worksheet.Paste(COleVariant(),COleVariant());

// tried this alternatively, but failed also:
/* COleVariant format((long)CF_BITMAP);
COleVariant link((BYTE)0);
COleVariant displayAsIcon((BYTE)0);
COleVariant iconFileName("blub");
COleVariant iconIndex((short)0);
COleVariant iconLabel("my");
m_worksheet.PasteSpecial(format,link,displayAsIcon,iconFileName,iconIndex,iconLabel);
*/

Quote:

---

Originally posted by mpscholz
Here's the code in question:

// paste => exception!
m_worksheet.Paste(COleVariant(),COleVariant());

*/

---

Try using the optional argument in paste as below.
See if that works.

COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR),covWidth((short)40);
m_worksheet.Paste(covOptional,covOptional);

I got Paste Special to work when I used the following list of parameters to paste a bitmap.


COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR),covWidth((short)40),

covFalse((short)FALSE),
covTrue((short)TRUE);

sheet.PasteSpecial(COleVariant("Picture (Enhanced Metafile)" ),covFalse,covFalse,covOptional,covOptional,covOpt
ional);

Dear Tom,

You are a wizard: the (first) solution worked!!

Gonna put up a golden Tom statue in front of our building :-)

Mille grazie,
Matthias

p.s. the COleVariant covOptional wasn't that intuitive, was it? How did you know?
p.p.s. just for the sake of being complete: the PasteSpecial() is still throwing the exception...:-(

Quote:

---

p.s. the COleVariant covOptional wasn't that intuitive, was it? How did you know?

---

Trial and error, looking at VBA help within Excel and MSDN.

see
http://support.microsoft.com/default...;en-us;q238981

and

http://msdn.microsoft.com/library/de...sofficedev.asp

Well, looking in from the Java universe (for the background of this all is the development of a Java-Excel bridge) I found MSDN pretty ...hm... confusing, particularly on the C++ part.
On the other hand, it seems to be rather difficult to find some books on windows automation in these modern .net - days, isn't it?

I agree. The documentation for Office Automation is kind of sketchy.

Who can help me?

///////////////////////////////////////
sheet1.Activate();
app.SetCutCopyMode(FALSE);

lpDisp = sheet1.GetRange(COleVariant("A1"), COleVariant("BZ100"));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);

range.Copy(covOptional);
sheet1.PasteSpecial(covOptional,covFalse,covFalse,
covOptional,covOptional,covOptional);
//range.PasteSpecial((long)(-4163) ,(long)(-4142),covFalse,covFalse);

////////////////////////////////////

Quote:

---

Originally Posted by Tom Frohman

I agree. The documentation for Office Automation is kind of sketchy.

---

Tom,

I am trying to use the vc++/MS Office automation to print the **** word/excel documents.

Now the pprintout method works as long as I just want to print the wh=ole document, but when I try to print the selected pages, or a range of pages it just wont work,

If you have a sample where you are passing the printout fucntions options like which pages to be printed etc, it would be a real life saver, as I am not been able to find out how to pass the variables, whethere they are are to be passed as ColeVariant((long)3) for "from" and COleVarian((long)5) for "to" variables etc for the printout functions, as passing even strings did not work.

Thanks for your time.

Pauli

Quote:

---

Originally Posted by coolstones

Who can help me?

///////////////////////////////////////
sheet1.Activate();
app.SetCutCopyMode(FALSE);

lpDisp = sheet1.GetRange(COleVariant("A1"), COleVariant("BZ100"));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);

range.Copy(covOptional);
sheet1.PasteSpecial(covOptional,covFalse,covFalse,
covOptional,covOptional,covOptional);
//range.PasteSpecial((long)(-4163) ,(long)(-4142),covFalse,covFalse);

////////////////////////////////////

---

The following worked for me when I went to paste a screen captured image to the worksheet.

book=books.Add(covOpt);
sheets=book.GetWorksheets();
sheet=sheets.GetItem(COleVariant((long)1));
range = sheet.GetRange(COleVariant("A1"),COleVariant("A1"));
sheet.PasteSpecial(COleVariant("Bitmap"),covOpt,covOpt,covOpt,covOpt,covOpt);

Quote:

---

Originally Posted by paulina_lui

Tom,

I am trying to use the vc++/MS Office automation to print the **** word/excel documents.

Now the pprintout method works as long as I just want to print the wh=ole document, but when I try to print the selected pages, or a range of pages it just wont work,

If you have a sample where you are passing the printout fucntions options like which pages to be printed etc, it would be a real life saver, as I am not been able to find out how to pass the variables, whethere they are are to be passed as ColeVariant((long)3) for "from" and COleVarian((long)5) for "to" variables etc for the printout functions, as passing even strings did not work.

Thanks for your time.

Pauli

---

The following successfully printout pages 2 and 3 of a 4 page document.
Using Visual C++ 6 and Word 2000.

doc=docs.Open(COleVariant("c:\\fourpage.doc"));

doc.PrintOut(covTrue,covFalse,
COleVariant((long)4),covOpt,covOpt,covOpt,covOpt,covOpt,
COleVariant("2,3"),covOpt,covOpt,covOpt,covOpt,
covOpt,covOpt,covOpt,covOpt,covOpt);

I could not get to the whole thread from the link you had in the your reply,

Anyways I tried that but still it prints the whole **** document.

I am using
myDoc.PrintOut(covTrue, // Background.
covFalse,//covOptional, // Append.
COleVariant((long)5),//covOptional, // Range.
covOptional, // OutputFileName.
covOptional, // // From.
covOptional, // // To.
covOptional, // Item.
COleVariant(numCps), // Copies.
COleVariant(_T("2,3")),//covOptional, // Pages.
covOptional, // PageType.
covOptional, // PrintToFile.
covOptional, // Collate.
covOptional, // ActivePrinterMacGX.
covOptional, // ManualDuplexPrint.
covOptional, // PrintZoomColumn New with Word 2002
covOptional, // PrintZoomRow ditto
covOptional, // PrintZoomPaperWidth ditto
covOptional);

I have to use COleVariant(_T("2,3")) as COleVariant("2,3") gave the error:: error C2440: 'type cast' : cannot convert from 'char [4]' to 'class COleVariant'

I am opening the document like this word::Documents myDocs = myWord.GetDocuments();
myDoc= myDocs.Open(COleVariant(szFileName),
covFalse,covTrue,covFalse,covOptional,covOptional,covFalse,covOptional,
covOptional,covOptional,covOptional,covTrue);

Thanks for your time!
Pauli

I tried this on my home machine with office XP and tried out your printout command.
It printed out pages 2 and 3 alone after I changed the 5 to a 4 as below.
I always rewrite the Open method so that it has only one argument which is why my open looks different.


_Application objWord;
COleVariant covTrue((short)TRUE),
covFalse((short)FALSE),
covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

if (!objWord.CreateDispatch("Word.Application.10"))
{
AfxMessageBox("Couldn't get Word object.");
return;
}

objWord.SetVisible(TRUE); //This shows the application.

Documents docs(objWord.GetDocuments());
_Document myDoc;
long numCps;
numCps=1;
myDoc=docs.Open(COleVariant("c:\\test.doc"));


myDoc.PrintOut(covTrue, // Background.
covFalse,//covOptional, // Append.
COleVariant((long)4),//covOptional, // Range.
covOptional, // OutputFileName.
covOptional, // // From.
covOptional, // // To.
covOptional, // Item.
COleVariant((long)numCps), // Copies.
COleVariant(_T("2,3")),//covOptional, // Pages.
covOptional, // PageType.
covOptional, // PrintToFile.
covOptional, // Collate.
covOptional, // ActivePrinterMacGX.
covOptional, // ManualDuplexPrint.
covOptional, // PrintZoomColumn New with Word 2002
covOptional, // PrintZoomRow ditto
covOptional, // PrintZoomPaperWidth ditto
covOptional);

As there seems to be something that I am missing.

I did try this time with a new project just to try it out, but still the same , prints all pages..

Thanks for your help.. a lot

Pauli