How to insert picture from resources to Excel shee

Question

I'm trying to add a picture to an Excel sheet, using a picture in the resources file, using VB2010. I can do it using: xlDATAWorkSheet.Shapes.AddPicture(...) with the path coded in, but I want to use it from the resources file. This is what I've tried: xlDATAWorkSheet.Shapes.AddPicture(CType(My.Resources.ResourceManager.GetObject("Logo"), Bitmap), False, True, 0, 0, 300, 50) , but I get an error: file not found (pointing to the Logo word)*

I also tried: xlDATAWorkSheet.Shapes.AddPicture(My.Resources.Logo, False, True, 0, 0, 300, 50) , but I get same error: file not found.

It works fine with the path to the picture hard coded, like xlDATAWorkSheet.Shapes.AddPicture("C:/Logo.jpg", False, True, 0, 0, 300, 50), but I want to use it from the resource file (so it will run on another machine)

Also, this works ok, so Logo image is really there:

frmMain.pic1.Image = CType(My.Resources.ResourceManager.GetObject("Logo"), Image)

It seems though, according to the MSDN docs for Shapes, there does not appear that AddPicture takes anything other than a string to determine what image you want to insert.

Can anyone help insert a picture from the resources to an Excel sheet in VB2010?

Answer 1

You can add an image to excel using the clipboard automatically in your code.

The below code works in vb net 2008.

Dim MyScoreCard As Excel.Worksheet = Nothing
Dim MyRange As Excel.Range = Nothing
Dim AwayBit As Bitmap =CType(My.Resources.ResourceManager.GetObject("picturename"),Bitmap)

Clipboard.Clear()
Clipboard.SetDataObject(AwayBit, True)
MyRange = CType(MyScoreCard.Range("Q41:Q41"), Excel.Range)
MyScoreCard.Paste(MyRange, AwayBit)

'Count will return the index of the last shape pasted to the worksheet
Dim idx As Integer
idx = MyScoreCard.Shapes.Count

'The code below will allow you to scale the image in excel from vb net
MyScoreCard.Shapes.Item(idx).LockAspectRatio =   Microsoft.Office.Core.MsoTriState.msoFalse

MyScoreCard.Shapes.Item(idx).ScaleWidth(0.75, Microsoft.Office.Core.MsoTriState.msoFalse, Office.MsoScaleFrom.msoScaleFromTopLeft)

MyScoreCard.Shapes.Item(idx).ScaleHeight(0.75, Microsoft.Office.Core.MsoTriState.msoFalse, Office.MsoScaleFrom.msoScaleFromTopLeft)

Answer 2

The argument you have to pass to that excel interop function unfortunately requires a string of the filepath.

You can try the following though, and see if it works for you:

1. set the resource to a picturebox
2. Save the picture from your picturebox as a jpeg file and save it in a definite location (Maybe a folder in Drive C:) using this code: PictureBox1.Image.Save(SaveFilePath, System.Drawing.Imaging.ImageFormat.Jpeg) Note that savefilepath doesn't have to include the .jpeg extension in it's name
3. pass the savefilepath to a string
4. use savefilepath as the string to submit to .AddPicture
5. Remove the temporary JPEG file you saved in drive C.

Lengthy solution, yes. But should work given the restrictions.