How can I insert all the jpg files to each page in excel using vba

[ehero]

Hi, I'm trying to insert all the images in folder E:\Image to sheet "intrang", the first image will be placed at A1, then the next image will be placed in the next page, i have this code but it's have error Object required at this line If topPosition + .Height > Sheets("intrang").PageSetup.PrintArea.Height Then
I've check page setup, nothing seem odd
Please, help me fix this

Private Sub ToggleButton2_Click()
Dim folderPath As String
   folderPath = "E:\Image" 
   
   Dim file As String
   file = Dir(folderPath & "*.jpg") 
   
   Dim pic As Picture
   Dim leftPosition As Double
   Dim topPosition As Double
   leftPosition = Sheets("intrang").Range("A1").Left 
   topPosition = Sheets("intrang").Range("A1").Top
   
   Dim currentPage As Integer 
   currentPage = 1
   
   Do While file <> ""
       Set pic = Sheets("intrang").Pictures.Insert(folderPath & file)
       With pic
     
           If topPosition + .Height > Sheets("intrang").PageSetup.PrintArea.Height Then
               currentPage = currentPage + 1 ' tang s? trang gi?y l?n
               leftPosition = Sheets("intrang").Range("A1").Left
               topPosition = Sheets("intrang").Range("A1").Top
           End If
     
           .Top = topPosition
           .Left = leftPosition
      
           topPosition = topPosition + .Height
      
           If currentPage > Sheets("intrang").HPageBreaks.Count Then
               Sheets("intrang").HPageBreaks.Add Before:=pic.Range
           End If
       End With
       file = Dir
   Loop
End Sub

 

[Micron]

I'm guessing that it's because you have no backslash between the folder and file name. Maybe folderPath = "E:\Image" should be folderPath = "E:\Image\"
When you Set an object variable, make sure it actually holds something when testing your code.

 

[John_w]

i have this code but it's have error Object required at this line If topPosition + .Height > Sheets("intrang").PageSetup.PrintArea.Height Then

Because PrintArea is a string denoting the print area, for example $A$1:$I$300, not a range.

Try this macro:

Public Sub Import_Images_To_Sheet_Pages()

    Dim imagesFolder As String
    Dim currentView As Long
    Dim fileName As String
    Dim pic As Shape
    Dim pageNumber As Long
    Dim imageDestCell As Range
    
    imagesFolder = "E:\Image\"
    
    If Right(imagesFolder, 1) <> "\" Then imagesFolder = imagesFolder & "\"
    
    Set imageDestCell = ThisWorkbook.Worksheets("intrang").Range("A1")
    
    Application.ScreenUpdating = False
    
    With ActiveWindow
        currentView = .View
        .View = xlPageBreakPreview
    End With
        
    pageNumber = 0
    fileName = Dir(imagesFolder & "*.jpg")
    While fileName <> vbNullString And pageNumber <= imageDestCell.Worksheet.HPageBreaks.Count
        With imageDestCell
            Set pic = .Worksheet.Shapes.AddPicture(fileName:=imagesFolder & fileName, LinkToFile:=msoFalse, SaveWithDocument:=msoTrue, _
                                                   Left:=.Left, Top:=.Top, Width:=-1, Height:=-1)
        End With
        pageNumber = pageNumber + 1
        If pageNumber <= imageDestCell.Worksheet.HPageBreaks.Count Then
            Set imageDestCell = imageDestCell.Worksheet.HPageBreaks(pageNumber).Location
        End If
        fileName = Dir
    Wend
    
    ActiveWindow.View = currentView
            
    Application.ScreenUpdating = True
    
End Sub

 

[jolivanes]

If you post in multiple forums, it is only polite to let us know so we don't have to waste time if someone in the other forum(s) has supplied you with an answer.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Insert all Images in a Folder and fit them to each page
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.