Positioning Pictures in a Worksheet

[mindscape777]

Hi.

I am generating Excel reports for my company using VB.NET programming but
have a problem when inserting an image into the spreadsheet and attempting to
position it within the sheet.

Please see my code snippet:

'Specify cell range for logo and insert the image
oRng = osheet.Range("A1")
oRng.Cells.Select()

oexcel.ActiveSheet.Pictures.Insert(Application.StartupPath &
"\logo.gif").select()

oRng is my Excel.Range object. As you can see, I am trying to select the 1st
cell in the spreadsheet to position my picture but instead I just keep
getting it positioned where it wants to put it with the empty cell being
selected. (Not what I really want)

Is anyone here familiar with Excel programming in VB.NET that could advise
me of a solution or point me in the right direction if I'm in the wrong
place???

I would REALLY appreciate the help!

 

[Joel]

You have to move the picture after it is placed on the worksheet. And Scale
the size of the picture. There a 4 parameters that need to be adjusted.
Top, Left, Width, and Height.

The best way of gettiing the code is to record a macro as you postition and
scale the part. Then use the recorded code.

Tools - Macro - Record Macro.

Then stop recording when done.

 

[Jim Cone]

This should be close...
'--
oRng = osheet.Range("A1")
oRng.Select
osheet.Pictures.Insert (oexcel.StartupPath & "\logo.gif")
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"mindscape777"
<[email protected]>
wrote in message
Hi.
I am generating Excel reports for my company using VB.NET programming but
have a problem when inserting an image into the spreadsheet and attempting to
position it within the sheet.
Please see my code snippet:

'Specify cell range for logo and insert the image
oRng = osheet.Range("A1")
oRng.Cells.Select()

oexcel.ActiveSheet.Pictures.Insert(Application.StartupPath &
"\logo.gif").select()

oRng is my Excel.Range object. As you can see, I am trying to select the 1st
cell in the spreadsheet to position my picture but instead I just keep
getting it positioned where it wants to put it with the empty cell being
selected. (Not what I really want)
Is anyone here familiar with Excel programming in VB.NET that could advise
me of a solution or point me in the right direction if I'm in the wrong
place???
I would REALLY appreciate the help!

 

[mindscape777]

Hey Guys,

Thanks for the replies, I really appreciate it.
But I still can't get this to work under your advisement. The problem is:

Joel: Recording the macro is a great suggestion but I'd already tried that
to no avail. It seems that the macro only captures the cell selection,
resizing and etc; not anything with regard to the picture insertion oddly
enough. Note: I am using Excel 2007. I did try this using Excel 2003 and it
does capture the insertion and resizing but I don't know how the code
translates to VB.NET.

Selection.ShapeRange.ScaleWidth 1.59, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 1.59, msoFalse, msoScaleFromTopLeft

The Excel Selection Object has none of the methods that are generated and I
can't identify any of the constants either. (At least apparently not in Excel
2007)

Jim: Your code seems somewhat identical to the code I posted but taking your
reply to heart, I did change a few details to more closely mimic what you
posted. But the results were the same; Excel seems to ignore the programming
code.

Any other thoughts or suggestions? Could it be an issue that is specific to
the Excel 2007 programming library? I am truly at a lost.

Thanks guys.

 

[mindscape777]

Hi Joel.

Please see reply to Jim Cone.

Thanks.

You have to move the picture after it is placed on the worksheet. And Scale
the size of the picture. There a 4 parameters that need to be adjusted.
Top, Left, Width, and Height.

The best way of gettiing the code is to record a macro as you postition and
scale the part. Then use the recorded code.

Tools - Macro - Record Macro.

Then stop recording when done.

 

[Jim Cone]

I stuck my head in where I normally wouldn't have - I generally ignore XL2007 questions.
Also, VB.Net is a foreign language in this group and I don't use it.

But continuing on anyway <g>...
The Pictures object is a legacy object that is no longer officially part of VBA but
still was usable at least thru XL2003. Pictures, Buttons and other consistently reliable
objects with logical properties and methods were replaced with Shapes.

As you seem to be getting the picture dumped on the sheet, using Joel's
suggestion to position the picture, after the fact, should work.
The oRng cell has a Top and Left property that you can assign to the picture
(however VB.Net does that). That should put it where you want.

Another way that might work is to use the Shapes.AddPicture method which would
look something like this in VBA speak:

Syntax...
expression.AddPicture(FileName, LinkToFile, SaveWithDocument, Left, Top, Width, Height)
Example...
oSheet.Shapes.AddPicture "oexcel.StartupPath & "\logo.gif", _
True, True, oRng.Left, oRng.Top, 70, 70
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)





in message
Hey Guys,
Thanks for the replies, I really appreciate it.
But I still can't get this to work under your advisement. The problem is:

Joel: Recording the macro is a great suggestion but I'd already tried that
to no avail. It seems that the macro only captures the cell selection,
resizing and etc; not anything with regard to the picture insertion oddly
enough. Note: I am using Excel 2007. I did try this using Excel 2003 and it
does capture the insertion and resizing but I don't know how the code
translates to VB.NET.

Selection.ShapeRange.ScaleWidth 1.59, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 1.59, msoFalse, msoScaleFromTopLeft

The Excel Selection Object has none of the methods that are generated and I
can't identify any of the constants either. (At least apparently not in Excel
2007)

Jim: Your code seems somewhat identical to the code I posted but taking your
reply to heart, I did change a few details to more closely mimic what you
posted. But the results were the same; Excel seems to ignore the programming
code.

Any other thoughts or suggestions? Could it be an issue that is specific to
the Excel 2007 programming library? I am truly at a lost.

Thanks guys.

 

[mindscape777]

Thanks much Jim for that additional piece of information and offering the
generous response to my inquiry despite your tendency to typically ignore
these types of questions.

I ended up formatting around the defaulted placement of the picture to
achieve the results I was after, it isn't the most graceful manner of getting
the job done but fortunately it worked.

I am not familiar with the Shapes.AddPicture method but I am certainly going
to investigate it further in combination with the code snippet you provided.
Hopefully it will prove to be a more appropriate solution for future use.

Thanks again for all the assistance!