CREO] Get selected Folder and file names

• Select a folder to get the selected folder and subfolder names.
• Enter a folder to retrieve the file names and information contained in the folder.

Folder Name	File Name

 

□ Folder Name Code

Sub GetFoldersList()
    Dim fso As Object
    Dim targetFolder As String
    Dim rowCounter As Long
    Dim ws As Worksheet
    
    ' 1. FolderList 시트 확인/생성
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets("FolderList")
    On Error GoTo 0
    
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        ws.Name = "FolderList"
        ' 신규 시트 생성 시 헤더 추가 (A10:E10)
        ws.Range("A10:E10") = Array("NO", "Folder Name", "Folder Path", "Level", "Date Modified")
        ws.Range("A10:E10").Font.Bold = True
    End If
    
    With ws
        ' 2. 기존 데이터 초기화 (A11부터)
        .Range("A11:E" & .Rows.Count).ClearContents
        
        ' 3. 폴더 선택 대화상자
        With Application.FileDialog(msoFileDialogFolderPicker)
            .Title = "폴더 선택"
            If .Show <> -1 Then Exit Sub
            targetFolder = .SelectedItems(1)
        End With
        
        ' 4. 파일 시스템 객체 생성
        Set fso = CreateObject("Scripting.FileSystemObject")
        rowCounter = 11 ' 데이터 시작 행 설정
        
        ' 5. 재귀 폴더 탐색
        ListSubFolders fso.GetFolder(targetFolder), rowCounter, ws
        
        ' 6. 서식 설정
        .Columns("E").NumberFormat = "yyyy-mm-dd hh:mm:ss"
        .Columns("A:E").AutoFit
        MsgBox "총 " & (rowCounter - 11) & "개 폴더 처리 완료!", vbInformation
    End With
End Sub

Sub ListSubFolders(folder As Object, ByRef rowCounter As Long, ws As Worksheet)
    Dim subFolder As Object
    
    With ws
        ' 현재 폴더 정보 기록
        .Cells(rowCounter, 1).Value = rowCounter - 10 ' NO 자동생성
        .Cells(rowCounter, 2).Value = folder.Name
        .Cells(rowCounter, 3).Value = folder.Path
        .Cells(rowCounter, 4).Value = UBound(Split(folder.Path, "\")) ' 레벨 계산
        .Cells(rowCounter, 5).Value = folder.DateLastModified
    End With
    
    rowCounter = rowCounter + 1
    
    ' 하위 폴더 처리
    For Each subFolder In folder.SubFolders
        ListSubFolders subFolder, rowCounter, ws
    Next subFolder
End Sub

 

□ File Name Code

Sub ExtractCreoFiles()
    Dim fso As Object
    Dim folder As Object
    Dim file As Object
    Dim dict As Object
    Dim fileName As String
    Dim baseName As String
    Dim ext As String
    Dim version As Long
    Dim row As Integer
    Dim counter As Integer
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set dict = CreateObject("Scripting.Dictionary")
    
    folderPath = Cells(8, "C").Value
    If Not fso.FolderExists(folderPath) Then
        MsgBox "폴더 경로가 유효하지 않습니다."
        Exit Sub
    End If
    Set folder = fso.GetFolder(folderPath)
    
    row = 11
    For Each file In folder.Files
        fileName = file.Name
        
        ' 확장자 필터링 로직 개선 (예: "prt.1" → "prt")
        Dim extParts() As String
        extParts = Split(fileName, ".")
        If UBound(extParts) >= 2 Then
            ext = extParts(UBound(extParts) - 1) ' 마지막에서 두 번째 요소가 확장자 (e.g., "prt")
            If LCase(ext) = "prt" Or LCase(ext) = "asm" Or LCase(ext) = "drw" Then
                version = Val(extParts(UBound(extParts))) ' 마지막 요소가 버전
                baseName = Left(fileName, InStrRev(fileName, "." & ext) - 1) & "." & ext
            Else
                GoTo SkipFile ' 허용되지 않은 확장자
            End If
        Else
            GoTo SkipFile ' 유효한 버전 번호 없음
        End If
        
        ' 최고 버전 선택
        If dict.Exists(baseName) Then
            If version > dict(baseName)("Version") Then
                dict(baseName)("Version") = version
                dict(baseName)("Size") = file.Size / 1024 / 1024
                dict(baseName)("Date") = file.DateLastModified
            End If
        Else
            dict.Add baseName, CreateObject("Scripting.Dictionary")
            dict(baseName).Add "Version", version
            dict(baseName).Add "Size", file.Size / 1024 / 1024
            dict(baseName).Add "Date", file.DateLastModified
        End If
SkipFile: ' 레이블 추가
    Next file
    
    ' 결과 출력
    If dict.Count > 0 Then
        Cells(11, "A").Resize(dict.Count, 6).ClearContents
    End If
    row = 11
    counter = 1
    For Each Key In dict.Keys
        Cells(row, "A").Value = counter
        Cells(row, "B").Value = Key
        Cells(row, "C").Value = Split(Key, ".")(UBound(Split(Key, "."))) ' 확장자 추출 (e.g., "prt")
        Cells(row, "D").Value = dict(Key)("Version")
        Cells(row, "E").Value = Round(dict(Key)("Size"), 2)
        Cells(row, "F").Value = Format(dict(Key)("Date"), "yyyy-mm-dd hh:mm:ss")
        row = row + 1
        counter = counter + 1
    Next Key
    
    MsgBox "처리 완료! 총 " & dict.Count & "개 파일 추출됨"
End Sub

 

It was developed with China's AI (Deepseek).
I designed the screen, captured the screen, and entered the content to be developed.

Since it is general content, it quickly creates code. What's better than American AI is that it shows the results. It shows the plan for how to make it. China's robotics technology is developing very quickly. Maybe it's because of Deepseek?

There is also an AI called CLOVA X in Korea. However, it still lacks functions. 
I hope CLOVA X's performance will improve.

 

by korealionkk@gmail.com

---