[Excel] 파일처리(파일목록 및 하이퍼링크 생성)

엑셀에서 중요한 기능중 하나가 파일을 READ 하고 WRITE하고 이긴 한데

단독적으로 쓸일은 많지는 않았다 그보다 현재 많은 정보들이 있다보니 각종 소스들에 대해서 목록으로 처리해야될일이 많아졌다 생각 되어진다.

프로젝트에서 말하자면 현황파악

제일먼저 특정경로에 소스를 가져왔을때 해당 소스하위까지 포함하여 폴더에 목록을 엑셀로 작성해보자

 

1) 엑셀시트 화면 구성 

   B1에는 검색하려는 기본경로 기입

   B2에는 기존 목록을 삭제하고 다시 작성할건지 여부

   B3에는 전체작성된 건수를 엑셀함수를 써서 기입 =COUNT(A9:A1048576)

 

2) 소스작성

   먼저 파일을 사용하기 위해 참조를 하자

   직접 CreationObject를 사용하여도 되지만 ctrl+space로 자동완성기능을 사용하기 위하여

   참조를 통하여 객체를 넣는 작업을 한다

 

 

 

다음은 엑셀 코딩을 하자

Option Explicit   '명시적으로 항상 어떤변수를 사용했는지 확인하는게 신상에좋다

Dim wRow        '파일을 엑셀시트에 저장하기 위해 선언==> 말하자면 전역변수선언

Public Sub getFileList()   '==>실행하는 함수
    Dim rc
   
    wRow = 9        '기본은 9부터
    If ActiveSheet.Range("B2") = "Y" Then    '삭제여부를 보고 삭제작업
        rc = MsgBox("삭제해도 될까요?", vbYesNo, "삭제경고")
        If rc = vbYes Then
            Rows(wRow & ":" & wRow).Select
            Range(Selection, Selection.End(xlDown)).Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.Delete Shift:=xlUp
        Else
            Exit Sub
        End If

        '타이틀을 작성한다
        ActiveSheet.Cells(wRow, 1) = "NO"
        ActiveSheet.Cells(wRow, 2) = "파일명"
        ActiveSheet.Cells(wRow, 3) = "경로"
        ActiveSheet.Cells(wRow, 4) = "타입"
        ActiveSheet.Cells(wRow, 5) = "Size"
        ActiveSheet.Cells(wRow, 6) = "비고"
       
        '이건별도 나만에 함수로 생략 setHeaderStyle wRow     'header 스타일 적용
        wRow = wRow + 1
    Else
        wRow = ActiveSheet.Range("B3") + 10 '삭제를 안하고 추가하는경우 헤더포함하여 다음써야될행

    End If
    
    'b1에 입력된 기본 경로하위 폴더 및 파일목록을 작성시작
    FolderFile ActiveSheet.Range("B1")
   
End Sub


Function FolderFile(fn As String)


 Dim fs As FileSystemObject
 Set fs = New FileSystemObject

 Dim foldeer As Folder
 Dim fileinfo
 Dim Filnavn() As String
 Set foldeer = fs.GetFolder(fn)

 '해당폴더에 파일이 있는경우
 For Each fileinfo In foldeer.Files

        ActiveSheet.Cells(wRow, 1) = wRow - 9  '기본자릿수
        ActiveSheet.Cells(wRow, 2) = fileinfo.Name  '파일명
        ActiveSheet.Cells(wRow, 3) = Mid(fileinfo.path, 1, Len(fileinfo.path) - Len(fileinfo.Name)) 'path에 파일명까지 같이 들어오므로 파일명 길이만큼 잘라path만입력
        ActiveSheet.Cells(wRow, 4) = fileinfo.Type
        ActiveSheet.Cells(wRow, 5) = fileinfo.Size
        wRow = wRow + 1   '작성후 다음위치 + 1
        Debug.Print "F " & fileinfo
 Next

 For Each Folder In foldeer.SubFolders  'sub폴더가 있는경우

        Debug.Print "D " & Folder
        FolderFile (Folder)             '재귀함수로 자신을 다시 부름
 Next

End Function 

파일처리 목록완성 예시

 

3) 하이퍼링크를 활용하여 클릭시 자동열리게 하자

   요즘은 하도 참조하는 문서가 많아 헷갈리는경우가 많다 저같은경우 목록을 작성하고

뒤에 비고란에 확인한 내용 및 주요 참고내용등을 적어놓고 다시 열때는 클리하여 자동열기를 주로 사용하고 있다

파일을 write할때 하이퍼링크 코딩을 넣어주면 된다.

 

'#하이퍼 링크 추가
ActiveSheet.Cells(wRow, 2).Select     '해당위치를 select
'address에는 전체파일명포함 경로, textToDisplay는 파일명
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=fileinfo.path, TextToDisplay:=fileinfo.Name 

 

추가하고 처리시 하이퍼링크건은 파란색 밑줄로 표시가 되고 클리하면 해당소스를 바로 읽게 됨