Autofill based on filename in same/different folder. Closed

Question

Hello, 
I'm looking for a way to pretty much automate part of my job, which is creating manhole sheets. 
In folder there are 10 files, each is called f.e. "aabbccddeeffgg.jpg" and aa = number 1 to X, in this example X=10, obviously. Is there a way to autofill cells based on firstly number I input (excell would have to search for a filename with said number at the beginning of filename) and secondly specific position in file name starting with specified number ?
Example - I write 7 in cell A1. Cell B2 is auto filled with letter X or left blank based on value/letter bb from filename starting with 7, cell C5 is autofilled with letter X or left blank based on value/letter cc from the same filename (starting with 7) and so on and on. 
Cheers

yg_be · Answer

I propose this :
Option Explicit
Sub jacek()
Dim filesys As Scripting.FileSystemObject
Dim myfolder As Scripting.Folder
Dim onefile As Scripting.File
Set filesys = New Scripting.FileSystemObject
Dim filename As String
Dim aa As String, bb As String, cc As String

Set myfolder = filesys.GetFolder("path to the folder")
For Each onefile In myfolder.Files
    filename = onefile.Name
    If Right(filename, 4) = ".jpg" Then
        aa = Left(filename, 2)
        bb = Mid(filename, 3, 2)
        cc = Mid(filename, 5, 2)
        If CInt(aa) = Range("a1") Then
            If CInt(bb) = 23 Then
                Range("b2").Value = "X"
            End If
            
        End If
    End If
Next
End Sub
You must, via the Tools/References menu in VBA Editor, add "Microsoft Scripting Runtime" to you project.

Autofill based on filename in same/different folder.

1 response

Similar discussions

Subscribe To Our Newsletter!