Autofill based on filename in same/different folder.

Ask a question Jacek - Last answered on Nov 28, 2016 at 06:21 AM by yg_be

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.

plus moins
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
End Sub

You must, via the Tools/References menu in VBA Editor, add "Microsoft Scripting Runtime" to you project.
Was this answer helpful?  
Jacek- Nov 28, 2016 at 04:51 AM
I thought it wil be tad easier, without need of using script. Nevertheless Thank You very much mate.
yg_be 3810Posts lundi 9 juin 2008Registration date ContributorStatus November 18, 2017 Last seen - Nov 28, 2016 at 06:21 AM
It is probably possible to use that code in formulas if required.
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!