Using VB.Net, How Do I Put a Picture (jpg) Into a Cell ?

[eBob.com]

I am creating an Excel 2002 sheet programmatically using VB.Net. It all
works fine. But now I'd like to add a photograph to each row. I've done
searches but all the hits I get must be for VBA rather than VB.Net. In any
event the hits have not helped. I'm aware of
HOWTO: Automate Microsoft Excel from Visual Basic .NET
http://support.microsoft.com/default.aspx?scid=kb;en-us;301982


but that doesn't illustrate how to put a photograph (jpg or similar) into a
cell.

Any pointers will sure be appreciated.

Thanks, Bob

 

[JW]

I am creating an Excel 2002 sheet programmatically using VB.Net. It all
works fine. But now I'd like to add a photograph to each row. I've done
searches but all the hits I get must be for VBA rather than VB.Net. In
any
event the hits have not helped. I'm aware of
HOWTO: Automate Microsoft Excel from Visual Basic .NET
http://support.microsoft.com/default.aspx?scid=kb;en-us;301982


but that doesn't illustrate how to put a photograph (jpg or similar) into
a
cell.

Any pointers will sure be appreciated.

Thanks, Bob

For anything you don't know how to do programmatically in Excel a good
starting point is the Macro recorder. Start the Macro recorder, choose/move
input cell, click menus etc, stop the recorder and see what code it
produces.

For the VB .Net Excel automation program in your link, the following inserts
a picture:

oSheet.Range("K2").Select()
oSheet.Pictures.Insert( "C:\temp\myPicture.JPG")

 

[eBob.com]

Thank you so much JW. I had tried that aproach but gave up on it when
Visual Studio didn't seem to know about a Sheet method named Pictures. In
VS, after I type "objSheet" ("Dim objSheet As Excel._Worksheet") the choices
I get do not include Pictures. The list is alphabetic and goes ..., Paste,
PasteSpecial, PivotTables, and PivotTableWizard, ... no Pictures.

But if I persist and type in the code similar to what you posted, ...

objSheet.Range("a1").Select()
objSheet.Pictures.insert("c:a.jpg")

.... there's no indication of an error after I type those statements AND I
get no Build errors AND it does ALMOST exactly what I want it to. The
picture does end up in the spread sheet, but the depth or height of the row
is not increased to accomodate it. So when viewing the spreadsheet you can
see only a tiny sliver of each picture. Any idea of how I address that
problem?

Thanks again, Bob

 

[JW]

Thank you so much JW. I had tried that aproach but gave up on it when
Visual Studio didn't seem to know about a Sheet method named Pictures. In
VS, after I type "objSheet" ("Dim objSheet As Excel._Worksheet") the
choices I get do not include Pictures. The list is alphabetic and goes
..., Paste, PasteSpecial, PivotTables, and PivotTableWizard, ... no
Pictures.

But if I persist and type in the code similar to what you posted, ...

objSheet.Range("a1").Select()
objSheet.Pictures.insert("c:a.jpg")

... there's no indication of an error after I type those statements AND I
get no Build errors AND it does ALMOST exactly what I want it to. The
picture does end up in the spread sheet, but the depth or height of the
row is not increased to accomodate it. So when viewing the spreadsheet
you can see only a tiny sliver of each picture. Any idea of how I address
that problem?

Bob,

I don't know why all the object properties like Pictures don't come up in
the VS IDE. It's another good reason for following my advice of using the
Macro Recorder in Excel. For example, moving to cell K2, inserting a
picture and adjusting the row and column sizes results in:

Sub Macro2()
Range("K2").Select
ActiveSheet.Pictures.Insert( _
"C:\temp\myPicture.JPG" _
).Select
Rows("2:2").EntireRow.AutoFit
Rows("2:2").RowHeight = 80.25
Columns("K:K").ColumnWidth = 31.29
End Sub

I also double-clicked the row line to see what the auto row size would give
in terms of VBA code. Sure enough, the RowHeight, ColumnWidth and
EntireRow.AutoFit properties all give a good clue as to how the same can be
done in VB .Net:

oSheet.Range("K2").Select()
oSheet.Pictures.Insert("C:\temp\myPicture.JPG")
oSheet.Rows("2:2").RowHeight = 90
oSheet.Rows(2).RowHeight = 90
oSheet.Columns("K").ColumnWidth = 40
oSheet.Columns(11).ColumnWidth = 40

oSheet.Rows(2).EntireRow.AutoFit()
oSheet.Columns(11).EntireColumn.AutoFit()

I couldn't get the EntireRow.AutoFit and EntireColumn.AutoFit to resize to
the size of my picture - they resized to the normal column and width size,
so a bit more expirentation might be needed there.

 

[eBob.com]

JW, you're my hero! Thanks. I had no idea that the width and height of
rows and columns could be adjusted. I too am not having success with
AutoFit. But I should be able to figure out the size of the pictures and
adjust the width and height.

Thanks again, Bob