Solved

how to cause Excel 2011 table cell to automatically expand to match size of an inserted image

Posted on 2015-02-03
24
59 Views
Last Modified: 2016-02-10
Using Excel 2011:

I have a workbook in which one worksheet is used to store images of expense receipts.

When receipts are copied into the worksheet, their image sits on top of the cells.

For example, if I want to insert, say a 4" x 6" image into cell C3, instead of cell C3 expanding to accommodate the inserted image, it remains at it's original size and the image overlays, say, range c3:z50 (coordinates are just an example)

How can I insert an image and have the cell expand automatically?
0
Comment
Question by:qeng
  • 12
  • 7
  • 2
  • +2
24 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40586296
You should also say: MACINTOSH QUESTION.

My question to you - do you have to have them inserted. Would a hyperlink work, so you can click on cell C3 and see the image in a separate window?
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40586331
Excel 2011, so you working on MAC?

I can give you a VBA for excel 2010 script to size cells based on contained images , but you can make a workaround and to maintain rows height when rows auto resized, is to use a column that is not visible or printable with auto wrap enabled, set the font for the column to maximum and make another sheet-b with cells contains any text that suitable for each size you may have then put that column in the original sheet formula to have value form the sheet-b for proper size
0
 

Author Comment

by:qeng
ID: 40586341
Phil,

On the main reporting worksheet in my workbook, I do use a label (for example 'myReceiptNumber5') in each row where I enter an expense.  I've hyperlinked that label to where I've inserted the image (the image is inserted into another worksheet in the same workbook).  The problem is that cutting and pasting those images into the Image Worksheet is messy and awkward to work with (for another user) when the images don't go into one cell.

One of my challenges is that I will be sending the workbook complete, with receipts contained within.  I can't easily (with the present approach) send a folder with images in it and a workbook that accesses that folder.  All of the information needs to reside within the workbook.

Thanks also for the suggestion to use the additional tag:  Macintosh Question.  How can I add an extra tag after I've already submitted my question?
0
 

Author Comment

by:qeng
ID: 40586365
FarWest:  thanks for chiming in also.  Before I go the VBA route (I may well want to take you up on that later), I'd just like to see if I can simply, manually, insert one image into a cell and have that cell expand to accommodate the full image size (that size can vary so I don't want to preset the column and row size).  Of course, I realize that the column width will take on the width of the widest image but that is not a problem for me at the present time.

If there is a way to do this, is the method the same whether the cell i'm inserting the picture into is a regular worksheet cell as opposed to a table cell.  I am in the process of converting the image worksheets (I have many worksheets, one per month for several years) from ranges to tables to facilitate data entry and sorting etc.
0
 

Author Comment

by:qeng
ID: 40586534
More issues :( ... as I've kept working on this to try to find at least a workaround, I'm noticing that the maximum row height is 5.68".  I'm wanting to make the row heights on one of my worksheets equal to 11" (or a shade above that) since I have many receipts in one category with are letter-sized.

Googling this max row height issue suggest this is a hard-coded limitation in Excel.

Merging adjacent cells to create more height doesn't work in a Table since rows can't be merged :(
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40586541
Rows themselves can be merged, but two cells, one underneath each other, can be.
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40586542
f there is a way to do this, is the method the same whether the cell i'm inserting the picture into is a regular worksheet cell as opposed to a table cell

as far as I know no way to auto expand row height, and it is the same for tables and none table cells

it looks for me that you are working on history data, so analyzing is required + archiving
I would recommend to make a simple access solution to contain all sheets, images can be save by using attachments data type in fields,
the proposed vba will resize the image as will to fit on column width with respect of ratio, so you will not have a problem with width
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40586624
Does it have to be Excel?

Excel is intended for numbers (with labels as required) and calculations/reporting on said numbers.

How about using a table in Word. When pasting an image into a Table in Word, the cell does expand to accommodate.

Alternatively, you can use PowerPoint if you have it. That would allow for Free Form pasting of images anywhere without being bound by a table; you might however lose the structured format that you would have with a Word table.

Both PowerPoint and Word would then be multi-page "documents" that you distribute as required.

Thanks
Rob H
0
 

Author Comment

by:qeng
ID: 40586714
Thanks for responding Rob.

It would be very awkward to try to do what I'm doing in Word or Powerpoint because my workbook allows numerous types of sorts (by project, travel location, expense type, date, etc.) and also does calculations (some conditional) on the cell contents, based on included tax tables.  It does some geocoding, etc..  Not easily done (or re-done) in Word or Powerpoint.
0
 

Author Comment

by:qeng
ID: 40586721
Phil, I meant to write that vertically adjacent cells (I didn't mean 'rows') can't be merged in a Table (they can in a regular worksheet range).  I'm specifically trying to make us of Excel's Table features.
0
 

Author Comment

by:qeng
ID: 40586735
Farwest:  you are correct in terms of what I'm trying to do in terms of analyzing and archiving.  Unfortunately, I'm barely familiar with Access (just starting to use it this week for another project).  As mentioned above, my workbook also does a lot of arithmetic (not terribly complicated stuff) but some of it conditional, on the enclosed data.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 12

Expert Comment

by:FarWest
ID: 40586871
then go back to VBA :)

I will get back to you with the code, but I have to leave my office now
0
 

Author Comment

by:qeng
ID: 40586890
ok great FarWest.

I'll gladly look forward to your suggested VBA code.
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40587378
here is the code,
you can call it from a macro and assign a key to it, and use it as ctrl-v (paste) for pictures
Public Sub PicturePaste()
    Dim cCell As Range
    Set cCell = ActiveCell
    Dim cPict As Shape
    Dim ShapesCount As Integer
    ShapesCount = ActiveSheet.Shapes.Count()
    ActiveSheet.Pictures.Paste.Select
    Set cPict = ActiveSheet.Shapes(ShapesCount + 1)
    cPict.LockAspectRatio = msoTrue
    If cPict.Width > cCell.Width Then
       cPict.Width = cCell.Width - 10
    End If
    cPict.Left = cCell.Left + 5
    cPict.Top = cCell.Top + 5
    ActiveSheet.Rows(cCell.Row).RowHeight = cPict.Height + 10
End Sub

Open in new window

0
 

Author Comment

by:qeng
ID: 40597404
Thanks FarWest.  I'm trying to work with it.  It's throwing some weird behaviour in Excel 2011 at the moment.  Troubleshooting why so I can provide some useable feedback.
0
 

Author Comment

by:qeng
ID: 40623338
Here is what i get when I run it from cell C5:

Result of running PicturePaste VBA

If I try to pre-select a range, I get the same result, meaning the actual text of the macro gets inserted instead of a picture.

I'm not understanding what I was to do with that VBA code.  I would have assumed that it would ask the user to chose a picture to insert and then would have merged as many cells as necessary to fit the pic (or something like that).

What do you think FarWest?
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40623354
The VBA as you can see assumes that the required picture is in the clipv
Board. You apperantly has text in the last copy/ cut operation.
0
 

Author Comment

by:qeng
ID: 40653460
Hi FarWest,

Sorry for the delay in getting back (I've been swamped on other projects).

Thanks for clarifying that I needed to load the clipboard first prior to using the code.

I'm running into a few snags.  The first one is that I don't seem to be able to load the clipboard via Preview with an image already on my Mac.  If I view the image in Finder and select the image (right-click > Copy) or double-click to display it in Preview and then use the VBA code to try to insert it into a cell, I get a large icon with letters representing the image type (such as png or jpg for example).  See image below.

Unable to load image from Preview
If instead I browse the internet and right-click > copy and image directly to the clipboard, I can use the code and it will copy the image to a cell but unless I pre-size the row to it's maximum height, the row height is only increased marginally and image is inserted only at a fraction of its size (for example, an image which should have taken at least several inches in height gets loaded into a row of 0.61 inches (the un-resized image flashes for a moment and then gets shrunk down to this size).  This seems to occur for an image which would have been larger than the 5.68" max row height.  If the image copied to the clipboard isn't larger than the max row height, the code seems to leave a small thumbnail into a row of approx 0.61 inches high but it also leaves, behind that image, a second larger copy of the same image but this one is overlaid onto the spreadsheet, as opposed to being within the cell.  This overlaid image shows odd behaviour by gradually disappearing if one clicks over parts of it in the spreadsheet.  See image below.

In the image below, the top most image would have required a row height taller than the max row height.  It got shrunk down to an 0.61" row by the code.  The second image, would normally have fit inside a correctly re-sized row but instead resulted in the 'double-image insertion with overlay' behaviour i'm referring to.

Double image gets inserted with second one escaping the cell boundaries and being erasable in parts if overlaid cell is clicked on
For the application I have, all of the images I'm trying to load into a workbook are scans, with the images already existing as files on my HD.  The natural way to get at them is via Finder or Preview.  As mentioned above, if I right click on the image in finder and copy it to the clipboard using that method, i get the png or jpg icon.

If I instead, drag the image directly onto the spreadsheet (in which case it doesn't go into a cell but just sits on top of the worksheet) , then pre-size the row-height to take the image, then right-click > copy the image to the clipboard while it's in the spreadsheet, then delete the picture, then select the cell where I pre-sized the row height, and then use the code to insert it, it gets inserted correctly as long as it isn't an image which would have been larger than the max row height.  See image below.

Result if I pre-size a cell and drag an image into the spreadsheet, copy it to the clipboard, then delete the image, then use the code to insert it into the pre-sized cell.
If I try to first merge two rows (say, to accommodate an image which was taller than max row height), and use the above procedure, instead of the image being loaded into the merged cell (which would have been large enough to accommodate it) i get the 0.61" inch thumbnail of the image inserted by the code and a temporary second image (but overlaid onto the spreadsheet) which disappears as soon as the mouse wheel is scrolled.

Is there any way for the code to load an image based on a user's filename selection and at least have the code resize the row up to maxrowheight before it insert's it?
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40653475
it was a long time :)
quick explanation that the logic is to preserve column width and set height with maintaining aspect ratio,
so it assumes that you need the picture not exceeding the column width

regarding selecting files instead of using clipboard it can be done, I will provide you later with a script that will do it
meanwhile you can just select those files in a word document and use the copy and paste
0
 

Author Comment

by:qeng
ID: 40653858
Thx FarWest.

Yes it was a long time, sorry, I've been buried under a ton of other work.

OK that makes sense about the column width.

I will wait for your suggested mods about selecting files instead of using the clipboard because having to transfer hundreds of images already scanned onto the hard-drive into a word document so I can copy and paste them would be overly time consuming.

The best for me would be, if you imagine two rows in a spreadsheet, one with filenames like 'dog1.jpg' (although the file extension could be any typical image format like png, jpg, gif, jpeg, tiff, etc.) in a range table, 'cat2.jpg' in the next row, 'bird3.jpg' in the next row and so on (meaning the filenames don't all have the same naming convention) and I were able to run the macro starting at the first filename, until it runs out of filenames, and have the macro load the image corresponding to the filename into the cell immediately to the right of the filename.  We would supply, in the macro, the path to the folder where the images are stored.

Let me know if this description isn't clear and I'll post a screen grab.

Thx
0
 
LVL 12

Accepted Solution

by:
FarWest earned 500 total points
ID: 40653883
OK, I understand

what about if the solution check if the clipboard has an image file name then it will paste its contents
so you only need to copy and paste file names where ever you like on the sheets?
0
 

Author Comment

by:qeng
ID: 40665722
That in itself is quite useful.  So yes, I'd be able to make good use of that solution.  Thank you.

For this particular application, a very useful variant of this solution would be, because all the images are in monthly folders, it's easy to get a list (in a column) of all the filenames.  It would be fantastic if the procedure could take the filename, say, "somefilename1.jpg" in column X, then look in a user-defined folder, copy picture1.jpg to the clipboard, then insert it into the next adjacent column, then move down one row to "somefilename2.jpg", and so on until it ran out of filenames.

Both these procedures would be huge bonuses for different reasons.

I don't mind re-posting a second question if that is better for you.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40720417
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now