Autofill based on filename in same/different folder.

Closed
Jacek - Nov 25, 2016 at 10:07 AM
yg_be Posts 23323 Registration date Sunday June 8, 2008 Status Contributor Last seen November 12, 2024 - Nov 28, 2016 at 06:21 AM
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
Related:

1 response

yg_be Posts 23323 Registration date Sunday June 8, 2008 Status Contributor Last seen November 12, 2024 5
Nov 25, 2016 at 01:16 PM
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
I thought it wil be tad easier, without need of using script. Nevertheless Thank You very much mate.
0
yg_be Posts 23323 Registration date Sunday June 8, 2008 Status Contributor Last seen November 12, 2024 5
Nov 28, 2016 at 06:21 AM
It is probably possible to use that code in formulas if required.
0