none
Excel CopyPicture always return a null value RRS feed

  • Question

  •  

    Hi to all,

    I'm developing with Visual Studio 2010 an addin solution based on Excel 2007 and Framework 3.5.

    I have a problem with the CopyPicture method and in particular with this code :

     

    Dim myRange As Excel.Range = Globals.ThisAddIn.Application.ActiveSheet.Range(Globals.ThisAddIn.Application.ActiveSheet.Cells(1, 10), Globals.ThisAddIn.Application.ActiveSheet.Cells(10, 1))
    
    myRange.CopyPicture(Excel.XlPictureAppearance.xlScreen, Excel.XlCopyPictureFormat.xlBitmap)
    
    Dim myImage As System.Drawing.Image = Clipboard.GetData(DataFormats.Bitmap)
    

     

    myImage  object is always Nothing.

    I've also tried to use this code :

    myRange.CopyPicture(Excel.XlPictureAppearance,xlPrinter, Excel.XlCopyPictureFormat.xlPicture)
    

    and the result is always the same.

    Thanks for any help.

     

    Monday, May 2, 2011 3:20 PM

Answers

  • Hi guys,

    I found the solution for this problem !

    Scenario :

    Vsto Excel AddIn based on Excel 2007 + Visual Studio 2010 + Framework 3.5

    Problem :

    If you want to copy in the clipboard the range selection of cells and you want to save the result stored in clipboard into image YOU DON'T MUST USE THE COPYPICTURE METHOD BUT THE SIMPLE COPY METHOD.

    Code :

     

    Dim myRange As Excel.Range = Globals.ThisAddIn.Application.ActiveSheet.Range(Globals.ThisAddIn.Application.ActiveSheet.Cells(1, 10), Globals.ThisAddIn.Application.ActiveSheet.Cells(10, 1))
    
    myRange.Copy

    Dim myImage As System.Drawing.Image = Clipboard.GetImage myImage.Save(myFileName)

     

    This code works fine on Excel 2007 and Excel 2010.

    Thursday, May 5, 2011 9:33 AM

All replies

  • Hi Fabrizio,

    You need to use:

    Dim myImage As System.Drawing.Bitmap = Clipboard.GetData(DataFormats.Bitmap) 


    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    Wednesday, May 4, 2011 8:24 AM
  • Dear  Andrei Smolin,

    thanks for you reply but I've just tested your code and doesn't work.

    myImage object returned is always Nothing.

     

    Thanks for your help.

    Wednesday, May 4, 2011 9:32 AM
  • Hello Fabrizio,

    You actual code works fine for me. I suggest that you run that code in an Excel add-in to check if it works correctly.


    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    Wednesday, May 4, 2011 9:59 AM
  • Dear Andrei,

    I've tested your solution on my project : Excel AddIn based on Office 2007 with Framework 3.5 and Visual Studio 2010 on a Windows 7 machine.

    You can download at follow link a simple and small test project (with your solution code) that have the same problem.

    http://www.fabriziodilello.com/vsto/ExcelAddInPreviewTest.zip

    Many many thanks for your suggestions...

    Wednesday, May 4, 2011 10:30 AM
  • Your code works fine on my side: Excel 2010 32-bit + Windows 7 64 bit + VS 2010 + .NET Framework 3.5.

    Try to run the macro below and then paste the Clipboard content to a Word document. Does it work?

    Sub test()
    Dim wks As Excel.Worksheet
    Set wks = ActiveSheet
    Dim r As Excel.Range
    Set r = wks.Range("A1:D10")
    r.CopyPicture xlScreen, xlBitmap
    End Sub


    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    Wednesday, May 4, 2011 10:53 AM
  • Dear Andrei,

    I tested your solutions on Excel 2010 and I think that the problem is the vsto add in based on Excel 2007 (it's my case).

    I tried the first code you posted

    Dim myImage As System.Drawing.Bitmap = Clipboard.GetData(DataFormats.Bitmap) 
    

    and this works fine on my Excel 2010 (but it isn't my case) installation but not on my developing machine with Excel 2007.

    The macro approach you posted, sincerely, isn't right, second me, because if I test the following code (that doesn't woks) on Excel 2007

    Dim myRange As Excel.Range = Globals.ThisAddIn.Application.ActiveSheet.Range(Globals.ThisAddIn.Application.ActiveSheet.Cells(1, 10), Globals.ThisAddIn.Application.ActiveSheet.Cells(10, 1)) 
    
    myRange.CopyPicture(Excel.XlPictureAppearance.xlScreen, Excel.XlCopyPictureFormat.xlBitmap)
    
    Dim myImage As System.Drawing.Image = Clipboard.GetData(DataFormats.Bitmap)
    
    
    and then I open Paint or Word I can paste the image contained in the Clipboard but the myImage object is Nothing.
    Wednesday, May 4, 2011 1:43 PM
  • Hi Fabrizio,

    I've tested my macro on an Excel 2007 machine and the macro works correctly. That means, the Range.CopyPicture method works exactly as described in the documentation. The macro itself is a conversion of your code to VBA. There's no equivalent to Clipboard.GetData(DataFormats.Bitmap), however. Let's look at this part more closely.

    Dim myImage As System.Drawing.Image = Clipboard.GetData(DataFormats.Bitmap)

    You get Nothing because you implicitely convert the value returned by Clipboard.GetData(...) to the System.Drawing.Image type declared in the left part of the statement above. And this conversion fails.

    You can check if that the case is the conversion case by checking the values below:

    Dim myObj As Object = Clipboard.GetData(DataFormats.Bitmap)
    Dim myImage As System.Drawing.Image = myObj
    Dim myBitmap As System.Drawing.Bitmap = myObj


    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    Wednesday, May 4, 2011 2:57 PM
  • Dear Andrei,

    I know that your macro on Excel 2007 works correctly and that it's a conversion of my "original" code but If you can try my vsto project that I posted before on Excel 2007 you'll realize that the myImage object is Nothing.

    The "CopyPicture" method works on Excel 2007 and Excel 2010, there's no problem.

    The problem is to access to the relative image contained in Clipboard with the classic methods in vsto project based on Excel 2007.

    I'm sorry but your code

    Dim myObj As Object = Clipboard.GetData(DataFormats.Bitmap)
    Dim myImage As System.Drawing.Image = myObj
    Dim myBitmap As System.Drawing.Bitmap = myObj
    

    doesn't works on vsto project based Excel 2007.

    Many many many thanks for your patience and help.

    Wednesday, May 4, 2011 3:37 PM
  • Hello,

     

    This code works for me:

     

    rangeToSave.CopyAsPicture();
    IDataObject data = Clipboard.GetDataObject();
    if (data.GetDataPresent("System.Drawing.Imaging.Metafile"))
    {
       System.Drawing.Image image = (System.Drawing.Image)data.GetData("System.Drawing.Imaging.Metafile", true);
    }
    

     

    Another code that works for me is:

    Image image = null;
    using (MemoryStream ms = new MemoryStream(rangeToSave.EnhMetaFileBits as byte[]))
    {
      image = new Bitmap(ms,true);
    }
    

     

    Hope this helps,

    Silviu.


    http://www.rosoftlab.net/
    Thursday, May 5, 2011 6:30 AM
  • Dear Silviu,

    the code you posted works on Excel 2007 ?

    Thanks.

    Thursday, May 5, 2011 8:07 AM
  • Hello,

     

    Sorry for not mentioning that it was for Word 2007. But I thought that the concept could be applied to Excel 2007 also.

     

    Best regards,

    Silviu.


    http://www.rosoftlab.net/
    Thursday, May 5, 2011 8:40 AM
  • Dear Silviu,

    unfortunately is not the same. I don't have problems with another project based on Word.

    In Excel the range object has no EnhMetaFileBits property and the code you posted doesn't works.

    Thursday, May 5, 2011 8:51 AM
  • Hi guys,

    I found the solution for this problem !

    Scenario :

    Vsto Excel AddIn based on Excel 2007 + Visual Studio 2010 + Framework 3.5

    Problem :

    If you want to copy in the clipboard the range selection of cells and you want to save the result stored in clipboard into image YOU DON'T MUST USE THE COPYPICTURE METHOD BUT THE SIMPLE COPY METHOD.

    Code :

     

    Dim myRange As Excel.Range = Globals.ThisAddIn.Application.ActiveSheet.Range(Globals.ThisAddIn.Application.ActiveSheet.Cells(1, 10), Globals.ThisAddIn.Application.ActiveSheet.Cells(10, 1))
    
    myRange.Copy

    Dim myImage As System.Drawing.Image = Clipboard.GetImage myImage.Save(myFileName)

     

    This code works fine on Excel 2007 and Excel 2010.

    Thursday, May 5, 2011 9:33 AM
  • Hi Fabrizio,

    My congratulations!

    I beg your pardon: I was leading you in a wrong direction because I thought the issue is a result of the implicit casting in your code.

    Now I understand that you were loking why CopyPicture doesn't work. I've tested your code and code suggested by Silviu on Excel 2007 + VS 2008 + .NET Framework 3.5. I confirm: CopyPicture does work correctly. Now I think the problem you were having may be caused by not installing all updates to Excel 2007.


    Regards from Belarus (GMT + 2),

    Andrei Smolin
    Add-in Express Team Leader
    Thursday, May 5, 2011 9:48 AM
  • I was using the simple formula of Range(A1:B3).CopyPicture and when I pasted the contents I got a blank rectangle.

    After surfing the internet, I stumbled on the solution; I had set Application.ScreenUpdating = False at the start of the code, and this was preventing it from taking a copy of the screen contents. Solution code was simply:

    Application.ScreenUpdating = True ' turn screen updating on while you copy the cells

    Range(A1:B3).CopyPicture

    Application.ScreenUpdating = False  ' turn screen updating back off again

    Hope this helps.


    Tuesday, January 7, 2014 9:32 AM