Results 1 to 4 of 4

Thread: Auto resize images while importing in Microsoft Excel

  1. #1
    Join Date
    Jan 2012
    Posts
    26

    Auto resize images while importing in Microsoft Excel

    Is there an add-in that will auto size a picture within a specified area without having to resize it all the time. I'm trying to design my own trading journal and need to import screen shots saved as jpeg file into a specified area. Any help would be greatly appreciated.

  2. #2
    Join Date
    Jul 2011
    Posts
    640

    Re: Auto resize images while importing in Microsoft Excel

    You need to read, for example, the width of both the range where you are pasting, and the width of the jpg:

    With ActiveSheet
    'Select the cell where the picture is placed
    Range("rngForJPG").Cells(1,1).Select
    'Insert the picture
    .Pictures.Insert(myImageFileName).Select
    'scale the picture to the width of the column
    myScale = Range("rngForJPG").Cells(1,1).EntireColumn.Width / Selection.ShapeRange.Width
    Selection.ShapeRange.ScaleWidth myScale, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight myScale, msoFalse, msoScaleFromTopLeft
    'Change the row height to the picture height
    Range("rngForJPG").Cells(1,1).EntireRow.RowHeight = Selection.ShapeRange.Height
    End With

  3. #3
    Join Date
    Jan 2012
    Posts
    26

    Re: Auto resize images while importing in Microsoft Excel

    Does any one have an addin already designed that you can just drag from the top left cell to the bottom right cell and like format it so that the pics will autosize within that area? What you posted is that a VBA program that has to be added for each area per sheet or is it an addin? I'm not very adept on programming yet but have bought some books that walk you step by step and also am using some online sites that have training. Thanks for the input so far, I couldn't get an answer from three other sites about this question.

  4. #4
    Join Date
    Jul 2011
    Posts
    640

    Re: Auto resize images while importing in Microsoft Excel

    Sirname,

    Select the cells (either manually or in code) where you want the picture to go, and then run this macro.

    Sub InsertAndResizePicture()

    Dim myR As Range
    Set myR = Selection
    'Insert the picture
    ActiveSheet.Pictures.Insert( _
    Application.GetOpenFilename( _
    "JPG picture files (*.jpg),*.jpg", , "Select the picture")).Select
    'scale the picture to the width of the column
    myScale = Application.Min(myR.Width / Selection.ShapeRange.Width, _
    myR.Height / Selection.ShapeRange.Height)
    Selection.ShapeRange.ScaleWidth myScale, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight myScale, msoFalse, msoScaleFromTopLeft
    myR.select

    End Sub


    You of course can modify it to choose the range and filenames in code...


    Sub InsertAndResizePicture2()

    Dim myR As Range
    Set myR = Range("Range_Name")
    'Insert the picture
    ActiveSheet.Pictures.Insert( "C:\PictureFiles\Picture1.jpg").Select
    'scale the picture to the width of the column
    myScale = Application.Min(myR.Width / Selection.ShapeRange.Width, _
    myR.Height / Selection.ShapeRange.Height)
    Selection.ShapeRange.ScaleWidth myScale, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight myScale, msoFalse, msoScaleFromTopLeft
    myR.Select
    End Sub

Similar Threads

  1. How to use Auto fill across multiple worksheets in Microsoft Excel
    By AdilShaikh18 in forum MS Office Support
    Replies: 4
    Last Post: 15-03-2012, 11:24 PM
  2. Replies: 2
    Last Post: 23-02-2012, 12:16 PM
  3. Importing a text file in vba in Microsoft Excel
    By Kaesav in forum MS Office Support
    Replies: 4
    Last Post: 24-01-2012, 05:16 PM
  4. Move and Resize Image not working in Microsoft Excel
    By SirName in forum Windows Software
    Replies: 2
    Last Post: 13-01-2012, 05:21 PM
  5. Replies: 3
    Last Post: 07-01-2012, 11:37 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,711,931.03476 seconds with 17 queries