Move an Image in Excel to be within a single cell - via VBA 1

[DanAuber]

Hi there,

I'm trying (and failing) to write a script to take an image that is already in an Excel work sheet and put it into a single cell - preferably so there is a 1mm gap between the image and the borders of the cell. No idea how to write this code and can't locate much on the internet. It might actually be a case of resizing cells rather than moving the image.

Any help gratefully received.

I've attached an excel sheet to demonstrate what I mean

Thanks !

Dan

 

[SkipVought]

Hi,

By observing your visual example, you want to 1) position the Top Left corner of the shape within cell A2 and then change the width of column A and the height of row 2 to correspond with the width and height of the shape, not just put put the shape within the cell.

Of course this can be accomplished via VBA code, and this would be best addressed in forum707.

But my first question is why change the column width and cell height? If you were to AutoFit the columns and rows of this sheet as it fills with data, which is what a spreadsheet is designed for, column A and row 2 width & height will change. You already have the shape property set to "Move but do not size with cells," because a shape's properties are with respect to the sheet and not any particular cell. So any change to the sheet that affects the column widths and row heights will require that the shape be re-positioned.

You asked as specific question, so here's a specific answer.

Sub ReposPX()
    Dim nLeft As Single, nTop As Single, nWid As Single, nHgt As Single
    Dim px As Picture

    Set px = Pictures("Picture 2")
    
    With Cells(2, 1)
        px.Top = .Top
        px.Left = .Left
        .RowHeight = px.Height
        .ColumnWidth = px.Width * (9.09 / 48)
    End With
End Sub

Of course, there are limits to Column Width & Row Height as well!!!

 

[DanAuber]

Thanks for that Skip. This spreadsheet is created via a Business Objects export function and I don't have any control over how it starts out. My macro then steps in to tidy it up - I hope that explains why I have to do it this way.

I tried running your script but got the error below. Am I doing something wrong ??

Thanks !

Dan

 

[SkipVought]

I guess I did not say exactly what I thought. Why does this shape need to fit within a cell? Is there a good reason?

What kind of shape are you working on and what is the name of this instance?

 

[SkipVought]

The most important stuff coming out of the BO Report is, of course, THE DATA.

So when you artificially expand column A, it makes less room for DATA. I'd be greatly annoyed getting a report in Excel where I could not optimize the columns for the data that I'm most interested in ingesting.

What difference does it make that an image spans several rows/columns? That's a nit in my book. Columns not optimized for data? THAT's not a nit!

 

[DanAuber]

Sorry Skip.

Once I have the data in an Excel sheet, it is then linked to from a Word Document. In order to get the graphs to have borders looking OK I need the images nicely positioned in a single cell. - I have to link to the cells rather than the images because I'm using a link that I can then refresh at a later date. If these were graphs produced directly from Excel I could link directly to them (rather than to the cells) - but I can't with graphs that are a BI created export.

I knew when I put this up that I'd get people asking "why do you want to do that ?". Believe me if I didn't have to, if I could think of another way, I would.....

I hope this helps to explain it a bit....trust me on this one - I have already lost half a head of hair looking for other workarounds and am steadily working through the other half.

In the meantime - do you know why I get the compile error above ?

Dan

 

[SkipVought]

June 26 02:18, I asked a question regarding the image.

 

[DanAuber]

Hi Skip,

Sorry again. I'm not sure what kind of shape I'm working on or what the name of the instance is. I'm attaching an example of what I get out of BI. 1000 thanks for any help you can provide

Dan

 

[SkipVought]

Sub ReposPX()
    Dim nLeft As Single, nTop As Single, nWid As Single, nHgt As Single
    Dim px As Picture

'[highlight #FCE94F][b]select your image FIRST[/b][/highlight]
    Set px = Selection
    
    With px
        If px.Name = "Picture -766" Then
            With Cells(2, 1)
                px.Top = .Top
                px.Left = .Left
                .RowHeight = px.Height
                .ColumnWidth = px.Width * (9.09 / 48)
            End With
            
        End If
    End With
    
End Sub

 

[DanAuber]

Thanks for this Skip - it doesn't seem to quite work - but it might be enough for me to play with your code

Dan

 

[SkipVought]

Specifically what do you mean?

 

[DanAuber]

Flag that - working just as expected - thanks !

 

[SkipVought]

You might need to make a slight adjustment to the 9.09 Factor. Or not. Definite maybe.

 

[SkipVought]

Regarding the BI created report:

I've worked with BI. We were pressing the capabilities of BI a year ago. BI is sadly deficient, IMNSHO!

I'd export the DATA that the BI chart is based on, IMPORT the data into a pre-designed Excel workbook plot the data. There's no reason to use Word. All the formatting can be done in Excel!