Autofill based on filename in same/different folder.

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

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.

See more 
moins plus
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.
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 807Posts lundi 9 juin 2008Registration date December 3, 2016 Last seen - Nov 28, 2016 at 06:21 AM
It is probably possible to use that code in formulas if required.
Add 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!