Pasting images to Excel OLE server

[mpscholz]

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.

[Tom Frohman]

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.

[mpscholz]

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)

[Tom Frohman]

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

[mpscholz]

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);
*/

[Tom Frohman]

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);

[Tom Frohman]

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);

[mpscholz]

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...:-(

[Tom Frohman]

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

[mpscholz]

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?

[Tom Frohman]

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

[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);

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

[paulina_lui]

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

[Tom Frohman]

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);

[Tom Frohman]

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);