Add every file inside a folder

DanoTheKid

New Member
Joined
Feb 23, 2020
Messages
6
Office Version
2016
Platform
Windows
Hello, I'm trying to do a macro that let's the user select a folder with more folders inside, this folders could have images .jpg or .png. What I want is that the macro adds only the image files in the excel, any image file. What it does right now is that adds the images but only if they have for name 1.jpg, 2.jpg, 3.jpg and so on.

VBA Code:
   Dim Secfolder As String
    MsgBox ("Busque y seleccione la carpeta que contiene las carpetas de los sectores en el punto que realizará.")
    With Application.FileDialog(msoFileDialogFolderPicker)
    
    .Title = "Buscar carpeta"
    .ButtonName = "Aceptar"
    .InitialFileName = "C:\"
    
    If .Show = -1 Then
     Secfolder = .SelectedItems(1)
    End If
    
    Sheets("Matriz_de_Hallazgos").Select
    
    l = 1
    
    For i = 1 To 200
    idm = (Worksheets("Matriz_de_Hallazgos").Cells(i + 2, 1))
    
    If idm = 1 Then
    Application.SpellingOptions.IgnoreCaps = True
     ' Colocar la ruta de las fotos; las fotos deben llamarse como números. Ej: 1.jpg'
        RutaCompleta = Secfolder & "\" & "sector " & idm & "\" & l & ".jpg"
        ActiveSheet.Cells(i + 2, 3).Select
        With ActiveSheet.Shapes.AddPicture(Filename:=RutaCompleta, linktofile:=msoFalse, _
            SaveWithDocument:=msoCTrue, Left:=0, Top:=0, Width:=0, Height:=0)
            .LockAspectRatio = 0
            .Top = ActiveCell.Top
            .Left = ActiveCell.Left
            .Width = ActiveCell.Width
            .Height = ActiveCell.Height
        End With
        l = l + 1
    End If
    Next i
Any ideas? Thank you
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,320
Office Version
365
Platform
Windows
Function searches for filename with both extensions ( jpg and png )

If neither file exists then an EMPTY string is returned
If jpg is found : jpg file path is returned
If jpg not found: if png is found png file path is returned

VBA Code:
'REPLACE
RutaCompleta = Secfolder & "\" & "sector " & idm & "\" & l & ".jpg"

'WITH
RutaCompleta = GetFilePath(Secfolder & "\" & "sector " & idm & "\" & l)
Debug.Print RutaCompleta
and insert this function in the same module as your code
VBA Code:
Private Function GetFilePath(fPath As String) As String
    Dim jpg As String, png As String
    On Error Resume Next
        jpg = Dir(fPath & ".jpg")
        png = Dir(fPath & ".png")
    On Error GoTo 0
    If Len(jpg) > 0 Then
        GetFilePath = jpg
    ElseIf Len(png) > 0 Then
        GetFilePath = png
    End If
End Function
Debug.Print
Debug.Print writes the found path to the Immediate Widow, which is useful when testing
Display the Immediate Window In VBA editor with {CTRL} g
After testing you can delete line containing Debug.Print

Note :
To prevent your code failing, I recommend that you amend your code to handle the situation where neither file is found
.
.
.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,042
Messages
5,484,340
Members
407,437
Latest member
alfaroM

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top