Results 1 to 5 of 5

Thread: Insert Different Picture into Multiple Sheets

  1. #1
    Member
    Join Date
    Sep 2013
    Posts
    37
    Rep Power
    0

    Insert Different Picture into Multiple Sheets

    hi all...


    how to inserting different 3 pictures in different cell and different sheets ( i have over 50 sheets in 1 file)
    Code:
    Sub InsertPic() Dim I As Long
        Dim xPath As String
        Dim xShape As Shape
        Dim xRg As Range
        xPath = "G:\foto_test\1.jpg,2.jpg,3.jpg"
        If Dir(xPath) = "" Then
            MsgBox "Picture file was not found in path!", vbInformation, "test"
            Exit Sub
        End If
        For I = 1 To ActiveWorkbook.Sheets.Count
            Set xRg = Sheets(I).Range("B10:C10") ("D10:E10") ("f10:g10")
            Set xShape = Sheets(I).Shapes.AddPicture(xPath, True, True, xRg.Left, xRg.Top, xRg.Width, xRg.Height)
        Next End Sub
    file name of picture are e.g. 1.jpg, 2.jpg,3.jpg


    my target is how to insert picture in cell with criteria :
    1.jpg----inserted into cell B10:C10 for each sheet
    2.jpg----inserted into cell D10:E10 for each sheet
    3.jpg----inserted into cell F10:G10 for each sheet


    anybody would help me and greatly appreciated


    susanto
    Attached Files Attached Files

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    try this

    Code:
    Sub InsertPic()
        
        Dim I As Long
        Dim xPath As String
        Dim xShape As Shape
        Dim xRg As Range
        
        Dim xFiles
        Dim j   As Long
        Dim a   As Range
        
        xPath = "L:\test\"
        xFiles = Array("1.jpg", "2.jpg", "3.jpg")
        
        If Dir(xPath & "*.jpg") = "" Then
            MsgBox "Picture file was not found in path!", vbInformation, "test"
            Exit Sub
        End If
        
        For I = 1 To ActiveWorkbook.Sheets.Count
            Set xRg = Sheets(I).Range("B10:C10,D10:E10,f10:g10")
            For j = 1 To xRg.Areas.Count
                Set a = xRg.Areas(j)
                Set xShape = Sheets(I).Shapes.AddPicture(xPath & xFiles(j - 1), True, True, a.Left, a.Top, a.Width, a.Height)
            Next
        Next
    
    End Sub
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Member
    Join Date
    Sep 2013
    Posts
    37
    Rep Power
    0
    thank you Admin, that's i want it..
    [SOLVED]

  4. #4
    Member
    Join Date
    Sep 2013
    Posts
    37
    Rep Power
    0
    hello man...

    i have new problem..how to insert 40 pictures e.g. 1jpg, 2.jpg, etc...INTO ONLY in cell H10 for each sheet
    i try modify code Set xRg = Sheets(I).Range("h10") but not working..

    would you help me? please..

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    try

    Code:
        For i = 1 To ActiveWorkbook.Sheets.Count
            Set xRg = Sheets(i).Range("H10")
            Set xShape = Sheets(i).Shapes.AddPicture(xPath & xFiles(i - 1), True, True, xRg.Left, xRg.Top, xRg.Width, xRg.Height)
        Next
    I assume you have 40 sheets in the workbook. Also add the file names in xFiles variable.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Replies: 2
    Last Post: 02-27-2019, 05:35 PM
  2. Replies: 8
    Last Post: 12-05-2017, 03:20 PM
  3. Replies: 2
    Last Post: 09-19-2013, 10:17 AM
  4. Insert Picture in a Cell UDF
    By Admin in forum Download Center
    Replies: 10
    Last Post: 12-07-2012, 04:49 PM
  5. Replies: 4
    Last Post: 05-03-2012, 10:28 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •