Autofill based on filename in same/different folder.

[Closed]
Report
-
Posts
17136
Registration date
Sunday June 8, 2008
Status
Contributor
Last seen
October 23, 2021
-
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

1 reply

Posts
17136
Registration date
Sunday June 8, 2008
Status
Contributor
Last seen
October 23, 2021
5
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.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

I thought it wil be tad easier, without need of using script. Nevertheless Thank You very much mate.
Posts
17136
Registration date
Sunday June 8, 2008
Status
Contributor
Last seen
October 23, 2021
5
It is probably possible to use that code in formulas if required.