Results 1 to 5 of 5

Help with modifying VBA Macro - Works fine but needs fine tuning !

Threaded View

  1. #1
    Registered User
    Join Date
    10-03-2015
    Location
    Perth
    MS-Off Ver
    2013
    Posts
    13

    Help with modifying VBA Macro - Works fine but needs fine tuning !

    Below is VBA Macro Code that uploads images from Desktop Folder onto Worksheet. The code is Functional ie it works fine, but need some modification.

    Sub InsertPic()
        Dim path As String, pic As String, pname As String
        Dim lastrow As Long, r    As Range
        Dim bExists               As Boolean
    
        path = "C:\Users\user\Desktop\NewArrivals\images"    'change as req
        lastrow = Range("A" & Rows.Count).End(xlUp).Row
    
        For Each r In Range("A2:A" & lastrow)
            bExists = False
            pic = r.Value
            pname = path & pic & ".jpg"
            ' check existence of file as jpg
            If Dir(pname) = vbNullString Then
                ' check existence of file as png
                pname = path & pic & ".png"
                If Dir(pname) <> vbNullString Then bExists = True
            Else
                bExists = True
            End If
            
            If bExists Then
                Rows(r.Row).RowHeight = 150
                With ActiveSheet.Pictures.Insert(pname)
                    With .ShapeRange
                        .LockAspectRatio = msoTrue
                        .Height = 100
                        .Width = 100
                    End With
                    .Left = Columns("E").Left + Columns("E").Width / 2 - .Width / 2
                    .Top = Rows(r.Row).Top + Rows(r.Row).Height / 2 - .Height / 2
                End With
                r.Offset(0, 2).Value = pic
            Else
                Cells(r.Row, "E") = "**** File Not Found *****"
            End If
        Next
    End Sub
    The code seems to ALTER the size of image during upload, which I DON'T want it to.

    I have already created standard, uniform size of all images.

    I simply want the code to pick the image & place it on Worksheet, without re-sizing it.

    The Image size is 210 px X 150 px. - the code should also adjust size of Row Height & Col Width accordingly.

    Anyone care to modify the code to remove that aspect of it's functionality ?

    Thanks in Advance.
    Last edited by JBeaucaire; 10-05-2015 at 05:54 PM. Reason: Fixed CODE tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Combo Box fine tuning
    By FLHR01 in forum Excel General
    Replies: 15
    Last Post: 09-30-2014, 06:00 PM
  2. Fine tuning a macro...
    By cafc_fuller in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-30-2013, 07:41 AM
  3. [SOLVED] Need help fine tuning a macro
    By jaimeteele in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-27-2013, 04:43 PM
  4. [SOLVED] need help fine tuning a formula
    By nje in forum Excel General
    Replies: 4
    Last Post: 07-10-2012, 04:55 AM
  5. Great macro for filtering a unique list - some fine tuning
    By pekde in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-24-2010, 01:32 AM
  6. Fine-tuning VBA code
    By smurray444 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-08-2007, 11:49 AM
  7. help w/ fine tuning
    By Mike in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 10-19-2005, 04:05 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1