Module 3. 여러 파일을 하나의 워크시트에 담기-(3)현재 워크북에 여러 파일 열기

지난 글에서는 아스키 파일(텍스트 파일)을 시트에 불러오는 함수(LoadTextFile)를 작성했습니다. 이 함수에는 대상 파일명과 불러오고자 하는 시트, 인코딩 등의 정보를 입력하였을 때 작동하는 함수입니다.

오늘은 실제로 사용자가 활용할 수 있도록 Sub 프로시저를 작성하려고 합니다.

실제 Sub 프로시저를 작성하기 전에 함수를 2개 더 작성하려고 합니다. 바로 새로운 워크시트를 만들기 위해 워크시트의 이름을 지정해주는 함수와 불러올 파일명에서 경로와 확장자를 삭제하는 함수입니다.

첫번째 함수가 필요한 이유는.. 엑셀에서 워크시트를 삽입하도록 하면, 자동으로 Sheet1, Sheet2, ... 와 같이 시트명을 붙여줍니다만, 여러 개의 파일을 불러올 때, 이런 이름이 붙어있으면 어떤 파일을 불러왔는지 알 수가 없습니다. 따라서, 시트명을 기왕이면 불러온 파일명으로 지정해주면 해당 시트가 어떤 파일을 불러왔는지 쉽게 알 수 있기 때문이지요.

물론, 이런 함수 없이, 파일을 다 로딩한 후, Cells(1,1)에 파일명을 삽입해주는 방법도 있습니다만, 여러개의 시트가 있을 때, 특정 파일로 접근하려면 일일이 시트를 클릭해봐야하니 그만큼 사용자 입장에서는 불편하기 때문이지요.

새로운 시트를 삽입할 때, 시트명은 아래와 같이 만들어줍니다.

----------------------------------

Function NewSheetName(iName As String, iShIndex As Long, Optional iCount As Long = -1) As String

  '새로운 시트명의 기본이름과 새로 작성하려는 시트의 인덱스, 그리고 혹시 중복되는 시트명이 있다면 시트명 뒤에 숫자로 구분할 수 있도록 시작값을 지정합니다. 만일, 시트명을 "ABC"라고 하고, 새로운 시트가 해당 워크북의 5번째 시트이며, 중복되었을 때, 1부터 시작하겠다고 한다면, NewSheetName("ABC", 5, 1)과 같이 입력합니다.

  Dim tName As String, tiName As String
  Dim i As Long, j As Long

  '만약 시트명을 입력하지 않았다면, 시트명을 "Sheet"로 시작하고, 어떤 이름이든 입력했다면, 시트명은 입력된 시트명으로 시작합니다.
  If iName = "" Then
    tiName = "Sheet"
  Else
    tiName = iName
  End If

  '시작하려는 카운트값을 입력하지 않았다면, 기본값은 0에서 시작하되, 특정값을 입력했다면 최초 시트명은 "시트명+카운트"로 지정합니다. 예를 들어, 카운트값이 입력되지 않았다면, "ABC"로 시작하고, 중복된 시트명이 있다면, "ABC1", "ABC2",...와 같이 만들지만, 카운트를 미리 "5"로 지정했다면, "ABC5"로 시작하되, 중복된 시트명이 있다면, "ABC6", "ABC7",... 과 같이 만드는 것이지요. 기본 시트명과 카운트를 구분하지 않고, 처음부터 시트명에 "ABC5"를 입력했다면, 중복된 시트명이 있을 때, "ABC51", "ABC52",... 와 같이 시트명이 생성됩니다.
  If iCount < 0 Then
    j = 0
    tName = tiName
  Else
    j = iCount
    tName = tiName & j
  End If

  '기본 시트명을 만들고 나면, 현재 시트(iShIndex로 지정된 시트)를 제외한 나머지 시트명과 비교해서 같은 시트명이 있으면, 시트명 뒤의 카운트값을 1씩 증가시켜서 새로운 시트명을 만듭니다. i=0 을 입력하면 For 문에서 i값을 초기화시켜버리므로 모든 시트에 대한 비교를 다시 시작하게 됩니다. 모든 시트명에 대해 중복이 없다면 새 파일명 후보로 남겨줍니다.
  For i = 1 To ActiveWorkbook.Sheets.Count
    If tName = ActiveWorkbook.Sheets(i).Name And iShIndex <> i Then
      j = j + 1
      tName = tiName & j
      i = 0
    End If
  Next

  '엑셀의 시트명은 31글자를 넘기게 되면 에러가 발생됩니다. 강제로 31자를 초과하는 시트명으로 지정하게 되면, 자동으로 31글자만 남기고 나머지는 삭제해버립니다. 만약, 원본 파일명이 너무 길다면 파일명+카운트로 시트명을 만들었는데, 이미 중복된 시트명이 될 수 있습니다. 따라서, 총 길이가 31글자가 넘는다면, 카운트수를 제외한 나머지 시트명에서 1글자씩 줄여서 다시 시트명을 생성하도록 하여 최종 시트명을 결정합니다.
  If Len(tName) > 31 Then
    NewSheetName = NewSheetName(Left(tiName, Len(tiName) - 1), iShIndex, iCount)
  Else
    NewSheetName = tName
  End If
End Function

----------------------------------

단순히 새 시트명을 작성하는 함수치고는 너무 길다고 느끼실 수 있습니다. 그러나, 어떠한 상황에서 오류가 발생할지 예측하지 않는다면, 열심히 작업하다가 매크로 오류로 인해 작업이 중단될 수도 있고, 오히려 이러한 오류로 인해 시간을 더 소모해버릴 수 있기 때문에 발생 가능한 오류를 최대한 줄이는 것이 중요합니다. 그럼에도 불구하고 오류가 날 가능성이 있다면, On Error Resume Next 와 같이 오류 처리 구문을 추가하여 처리할 수도 있습니다.

두번째 함수는 파일명에서 확장자와 경로를 제외한 나머지 파일명을 찾는 함수입니다. FileSystemObject에서 지원하는 method도 있지만, 이걸 사용하면, 8글자를 초과하는 파일명에 대해 'abcdef~1' 과 같이 전체 파일명을 출력해주지 않고 짧은 파일명을 만들어버리기 때문에 파일명을 추출하는 함수를 따로 작성한 것입니다. 그다지 길지 않은 내용이라 주석은 생략했습니다.

------------------------------------------------------

Function GetShortFileName(iStr As String) As String
  On Error GoTo ErrorHandler
  Dim tStr As String, tSplit
  tSplit = Split(iStr, "\")
  tStr = tSplit(UBound(tSplit))
  If InStr(tStr, ".") > 0 Then
    tSplit = Split(tStr, ".")
    GetShortFileName = Left(tStr, Len(tStr) - Len(tSplit(UBound(tSplit))) - 1)
  Else
    GetShortFileName = tStr
  End If
ErrorHandler:

End Function

------------------------------------------------------

다음으로는 실제 사용자가 쓰게되는 Sub 프로시저를 작성하도록 하겠습니다. 필요한 함수를 미리 다 만들어두었기 때문에 Sub 프로시저는 그다지 길지 않습니다.

================================

Sub OpenMultiFiles()
  On Error GoTo ErrorHandler
  Dim tFN(), tStr As String, tWB1 As Workbook, tWB2 As Workbook, tSh As Worksheet, tSh2 As Worksheet

  Dim n As Long, tFSO As New FileSystemObject, tFName As String, tFExt As String, tEnc As Long, tLoad as Boolean

  '우선 현재 작업하려는, 지금 활성화된 워크북을 1번 워크북으로, 현재 워크북에 포함된 마지막 시트(제일 우측의 시트)를 변수에 입력해둡니다.

  Set tWB1 = ActiveWorkbook
  Set tSh = tWB1.Sheets(ActiveWorkbook.Sheets.Count)

  '엑셀의 파일열기 창을 활성화하여, 여러개의 파일을 선택할 수 있도록 하여 파일목록을 만듭니다.

  tFN = Application.GetOpenFilename(MultiSelect:=True)

  'n에는 엑셀 파일이 아닌 다른 종류의 파일(아스키 데이터가 포함된 파일)이 열릴 때마다 1씩 증가하는 카운트입니다. 시작값은 0으로 하고, 아스키 파일이 열릴 때마다 1씩 증가시킵니다. 파일목록에 대해 파일 확장자가 엑셀 파일 종류라면 엑셀의 열기 기능으로 파일을 열고, 엑셀 파일이 아닌 아스키 파일이라면 앞서 만들어둔 OpenTextFile 함수로 불러옵니다.

  n = 0
  For i = LBound(tFN) To UBound(tFN)
    tFName = tFN(i)
    tFExt = LCase(tFSO.GetExtensionName(tFName))
    Select Case tFExt

  '엑셀 파일을 열면 새로운 워크북으로 열리게 됩니다. 불러온 워크북에 포함된 차트나 다른 그림이 필요하다면 아래의 코드를 조금더 손을 봐야겠지만, 단순히 그 속에 있는 데이터만 필요하다면, 새로 열린 워크북의 각 시트의 데이터를 순차적으로 선택해서 현재 워크북으로 카피해줍니다. 데이터가 복사된 시트의 탭 색을 엑셀의 색과 유사하게 진한 초록색을 입혀주면, 엑셀에서 불러온 데이터라는 것을 조금 쉽게 구분할 수 있겠지요.

  '시트명은 원본 시트명과 원본 파일명을 연결하여 작성합니다. 만약, 원본 파일명을 합쳐주지 않는다면, 모든 시트가 'Sheet1+카운트'가 될 수도 있습니다.

  '불러온 엑셀 파일에서 데이터 복사가 끝났다면, 저장하지 않고 그 파일은 닫습니다.
      Case "xls", "xlt", "xla", "xlm", "xlw", "xlb", "xlsx", "xlsm", "xlsb", "xltx", "xltm", "xlam"
        Set tWB2 = Workbooks.Open(tFName)
        For j = 1 To tWB2.Sheets.Count
          Set tSh2 = tWB2.Sheets(j)
          If Not (tSh2.UsedRange.Rows.Count = 1 And tSh2.UsedRange.Columns.Count = 1 And tSh2.UsedRange.Cells(1, 1).Value = Empty) Then
            Set tSh = tWB1.Worksheets.Add(After:=tSh)
            tSh2.UsedRange.Copy tSh.Cells(1, 1)
            tSh.Name = NewSheetName(tSh2.Name & "_" & GetShortFileName(tWB2.Name), tSh.Index)
            tSh.Tab.Color = RGB(102, 255, 102)
          End If
        Next
        tWB2.Close SaveChanges:=False

  '엑셀 파일이 아닌 파일이 열리면, 처음 열리는 파일에 대해서는 인코딩이나 데이터 구분자를 지정할 수 있도록 하고, 그 이후에 열리는 파일은 설정된 인코딩과 구분자에 맞게 불러옵니다. 파일을 불러올 때마다 시트가 1개씩 추가되고, 시트명은 파일명으로 지정해줍니다. 동일한 파일명이 동일폴더에 있지 못하므로 시트명도 중복되지는 않습니다만, 파일명이 지나치게 길다면 시트명이 중복될 수 있기 때문에, 앞에서 작성한 시트명생성 함수를 실행해줍니다.

  '탭 색은 밝은 하늘색으로 지정되었습니다만, 원하는 색으로 변경하시면 됩니다.

  '만약 첫번째 파일이 열릴 때, 취소 버튼을 누르면, 아스키파일은 열지 않습니다.

      Case Else
        If n = 1 Then

          Set tSh = tWB1.Worksheets.Add(After:=tSh)

          tLoad = LoadTextFile(tSh, tFName, n = 1, tEnc)

          tSh.Name = NewSheetName(GetShortFileName(tFName), tSh.Index)

          tSh.Tab.ColorIndex = 8

        Else

          If tLoad Then

            Set tSh = tWB1.Worksheets.Add(After:=tSh)

            tLoad = LoadTextFile(tSh, tFName, n = 1, tEnc)

            tSh.Name = NewSheetName(GetShortFileName(tFName), tSh.Index)

            tSh.Tab.ColorIndex = 8

          End If

        End If

    End Select
  Next
ErrorHandler:
  Erase tFN
End Sub

================================

서로 구분자가 다른 데이터파일을 열 때에는 엑셀에서 지원하는 구분자를 모두 클릭해놓으면 됩니다. 예를 들어, 일부 파일은 구분자가 '탭'이고 일부 파일은 '쉼표'라고 한다면, 첫 아스키파일이 열릴 때 구분자를 탭과 쉼표를 모두 클릭해두면 됩니다. 다만, 구분자를 여러개 지정했을 때, 불필요하게 데이터가 분할되는 수도 있기 때문에 가급적 구분자가 동일한 파일들을 일괄적으로 열어주는 게 좋습니다.

저의 경우엔, 일련의 데이터들은 모두 동일한 형식으로 된 파일로 받기 때문에 불편함없이 쓸 수 있지만, 서로 형식이 다른 형식의 파일을 한꺼번에 열게 되면, 워크시트에서 또다시 수작업을 해야하는 수가 있으니 굳이 이러한 매크로를 사용할 필요가 없을 수도 있겠습니다. 혹은 개인별로 자주쓰는 파일 유형을 구분해서 워크시트들을 모아주는 기능을 만들 수도 있겠구요. 이러한 부분은 각 사용자들이 더 개발하시면 좋을 듯 합니다.

여기까지, 엑셀 또는 아스키 파일을 한꺼번에 여는 매크로였습니다. 다음엔 동일한 구조의 파일이 열린 상태에서 특정 영역만 추출하는 매크로를 작성해보도록 하겠습니다.