Inserting picture in a specific cell in Excel

[gkratnam]

How do we insert a picture in a specific location in excel and adjust it's size?

This inserts the picture. Need assistance with scaling.

oExcel = CreateObject("Excel.Application")
oWorkbook = oExcel.Workbooks.Add()
oSheet1 = oWorkbook.Worksheets(1)
oPictures = oSheet1.Pictures
oPictures.Insert("logo.png").Select

Thanks!

 

[tomk3]

Hello,

record a macro and have a look on it, its not too hard to adopt it to vfp.

You need the values for the constants used in the generated code, get an "Excel.h".
Or let the mouse hover on the constant, it shows the value in a tooltip.
If that does not work : right click - object catalog - constants - search, it shows value in status bar (for example xlbottom -4107)
There is a post from cetin on foxite on how to generate the excel.h by yourselft, it does not work for me.

Regards
tom

 

[GriffMG]

This is a snippet of what I use...

** NOW NEED TO OPEN THE SHEET AND PUT IN THE EXTRA COLUMNS
OEXCEL = CREATEOBJECT("Excel.Application")
* make excel visible during development
OEXCEL.VISIBLE = .F.
OEXCEL.WORKBOOKS.ADD

OEXCEL.CELLS.SELECT
OEXCEL.CELLS.VERTICALALIGNMENT= -4160
OEXCEL.SELECTION.ROWS.AUTOFIT
OEXCEL.SELECTION.COLUMNS.AUTOFIT
OEXCEL.RANGE("A1").SELECT

IF m.LOGO
    OEXCEL.ACTIVESHEET.SHAPES.AddPicture(m.TEMPDIR+"tmplogo.bmp",.f.,.t.,1,1,-1,-1).select
    OEXCEL.Selection.Left = OEXCEL.ActiveSheet.Cells(1,8).Left
    OEXCEL.SELECTION.Top = OEXCEL.ActiveSheet.Cells(1,8).Top
    OEXCEL.SELECTION.Placement = 1
    OEXCEL.SELECTION.PrintObject = True
    IF OEXCEL.SELECTION.SHAPERANGE.HEIGHT+30 > OEXCEL.CELLS(1,7).ROWHEIGHT
	OEXCEL.CELLS(1,7).ROWHEIGHT = OEXCEL.SELECTION.SHAPERANGE.HEIGHT + 30
    ENDIF
ENDIF

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Rick C. Hodgin]

How do we insert a picture in a specific location in excel and adjust it's size?

One of the best ways to figure out how to do it programmatically is to go into Excel, begin recording a macro, and then do the thing you want to do manually, and then stop recording your macro.

That will have recorded the program steps to do the work. If you then go in to view / edit your macro, and press F8 one time to engage it, you can see all of the source there in the exact steps required, plus if you hover over the xl* constants, you'll see their value.

You can pretty much figure out how to do everything in Excel through automation that way. The code it generates is VB code which will need to be tweaked slightly to work with VFP, but the instructions and steps are almost 1:1.

--
Rick C. Hodgin

 

[Mike Lewis]

One of the best ways to figure out how to do it programmatically is to go into Excel, begin recording a macro, and then do the thing you want to do manually, and then stop recording your macro.

Rick,

I have to disagree with you - at least, up to a point. You are right that, in many cases, recording a macro can provide a good start in working out what code you write. But there several arguments against it. Tamar has often written about this (as have other developers); see for example her conference paper Office Automation Tips Tricks and Traps.

I'll try to summarise the issues:

1. The code generated by the macro recorder often does a lot more than the basic task at hand. And that might include things that you don't want to do. The example Tamar gives is changing a cell to bold. The generated code will do that, but it will also include instructions to change all the other font settings (font name, font size, underline, italic, strike through, etc). If you are not careful, this could lead to unwanted side effects when converting to VFP.

2. In Excel, macros tend to work with selections rather than ranges. There are lots of reasons that it's usually better for your code to use ranges.

3. The way you perform a task when recording a macro is not always the best way to perform that task in code. Her example is formatting an entire column. In Excel, you might do that by formatting the first cell in the column, and then copying the format down to the rest of the column. But in your code, it would probably be less verbose and more efficient to format the entire column object at once.

I'm not saying you should never use the macro recorder. On the contrary, I use it quite a lot myself - but only to give me a hint to help me start writing my code. But you do need to use it with caution, and to take care when interpreting its output.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Rick C. Hodgin]

I disagree with Tamar. Some common sense is required in extrapolating what Excel macros (or Word macros or whatever macros) give you, compared to your actual need.

But with macros, you will always get the job done.

I truly do not understand Tamar on most of what I've read of hers.

--
Rick C. Hodgin

 

[Mike Lewis]

I see what you mean, Rick. In the paper I quoted, she argued strongly against using the macro recorder, but there are clearly times when it can be valuable in writing Automation code. I hope my post didn't completely put anyone off using it. But it's always worth keeping the caveats in mind.

Mikw

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TamarGranor]

The paper doesn't say not to use the Macro Recorder. What it says is not to just modify the code you get from the Macro Recorder and that sometimes, when you use a macro, you end doing a lot more work than you need, because it makes more sense to do the bulk of the work in VFP.

The Macro Recorder is a great way to start, but simply converting a macro into VFP code without actually spending some time culling it is a bad idea.

Tamar

 

[gkratnam]

Thanks for all of your inputs.

Able to insert a picture at a specific location. Recorded a macro to resize the picture. But having some trouble converting into VFP code.

This is the excel macro for resizing.

Selection.ShapeRange.ScaleWidth 0.6465763895, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.7867755625, msoFalse, msoScaleFromTopLeft

Tried VFP conversion in few ways, but not working.

OEXCEL.Selection.ShapeRange.ScaleWidth.ScaleFromTopLeft = 0.6465763895
OEXCEL.Selection.ShapeRange.ScaleHeight =  0.7867755625

Is there any tools or documentation available for this? Please assist. Thanks!

 

[Rick C. Hodgin]

Go into that macro's source code and look at it. Press F8 in the Excel macro editor to hover over those msoFalse and msoScaleFromTopLeft parameters, and then pass those in to your VFP code. VFP will use Intellisense to guide you on parameters. Populate them as indicated.

I don't have a machine with Excel right now or I'd tell you the exact code to use.

It might be as simple as this:

OEXCEL.Selection.ShapeRange.ScaleWidth(0.6465763895)
OEXCEL.Selection.ShapeRange.ScaleHeight(0.7867755625)

It may use defaults if the other parameters are not provided.

--
Rick C. Hodgin

 

[gkratnam]

Thanks Rick, that did it!

Those parameters are 0 in Excel.

OEXCEL.Selection.ShapeRange.ScaleWidth(0.6465763895,0,0)
OEXCEL.Selection.ShapeRange.ScaleHeight(0.7867755625,0,0)

Thank you everyone for all your inputs.