Module 3. 여러 파일을 하나의 워크시트에 담기-(2)아스키 파일 열기 함수

(부제 : Import 기능 / QueryTables 이용하기)

이번 글에서는 아스키 파일을 현재의 워크북 내 워크시트에 담는 매크로입니다. 당연히 구분자에 따라 텍스트 나누기를 해주어야하고, 별도의 워크북이 아닌 현재의 워크북의 시트에 넣으려고 합니다. 

엑셀의 '매크로 기록' 기능을 사용하여, 텍스트 파일 열기를 실행하여 생성되는 매크로를 적절하게 변형한 것입니다. Function이 조금 길긴 하지만, 메모장으로 열릴 수만 있다면 제가 사용하는 아스키 파일은 다 열리더군요.

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

Function LoadTextFile(iSh As Worksheet, iFilename, Optional iInitial As Boolean = False, Optional iEncoding As Long = 949) As Boolean
  '함수에 입력해주는 것은 내용을 입력할 시트를 지정해주고, 파일명을 지정해줍니다.

  'iInitial은 여러개의 파일을 열때, 처음 열리는 경우, 파일열기 설정을 하기 위한 것입니다.

  'iEncoding은 처음 열때 인코딩을 지정해주면 나머지는 동일한 인코딩으로 열어주도록 설정하는 것입니다. 

  On Error GoTo ErrorHandler
  Dim tFSO As New FileSystemObject, tFExt As String, tFN As String, tStr As String, tEnc As Long, tEnc2 As Long, tMSG, tName As String
  If tFSO.FileExists(iFilename) Then: tFN = iFilename: Else: GoTo ErrorHandler
  tFExt = LCase(tFSO.GetExtensionName(tFN))
  

  If iInitial Then

  '만약 처음 열리는 파일이라면, 파일의 인코딩을 먼저 지정해줍니다. 기본 인코딩은 한국어입니다.

    tEnc = 949
    Do
      iSh.Cells(1, 1).Value = " "
      iSh.Cells(1, 1).TextToColumns DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=(tFExt = "csv"), Space:=False, Other:=False
      iSh.UsedRange.Delete xlToLeft
  '기본 인코딩으로 파일을 열기 위해 현재의 워크시트의 내용을 모두 삭제하여 초기화한 후, QueryTable을 작성해서 텍스트 파일을 열어줍니다. 

      With iSh.QueryTables.Add(Connection:="TEXT;" & tFN, Destination:=iSh.Cells(1, 1))
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = False
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = tEnc
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
        .MaintainConnection = False
        tName = .Name
      End With

  '열려진 파일의 글자가 깨지는 것은 아닌지 확인하고 필요하면 인코딩을 변경하도록 합니다. 인코딩이 잘못되었을 때에만 인코딩을 변경하게 될 것이므로, 현재의 인코딩이 아닌 자주 사용하는 다른 인코딩 번호를 디폴트로 해주면 사용할 때 좀더 편리하겠지요.
      tMSG = MsgBox("현재의 인코딩을 사용하시겠습니까?", vbYesNo, "인코딩 설정")

      If tMSG = vbNo Then
        Do
          If tEnc = 65001 Then tEnc2 = 949 Else: tEnc2 = 65001
          tStr = InputBox("인코딩 번호를 입력하세요. (현재 인코딩 : " & tEnc & ")" & vbCrLf & "(한국어 : 949, UTF-8 코드 : 65001, UTF-7 : 65000, ANSI : 1252, 일본어 : 932)" & vbCrLf & "오류발생시 '데이터-외부 데이터 가져오기-텍스트'를 실행하여 인코딩을 확인 후 변경하세요.", "인코딩 설정", tEnc2)
          If StrPtr(tStr) = 0 Then GoTo ErrorHandler
          tEnc = Val(tStr)
        Loop Until (tEnc > 0)
      End If
    Loop Until tMSG = vbYes

  '인코딩을 변경하여 파일을 열어보고 이상이 없을 때까지 반복합니다. 만약, 인코딩 번호를 잘 모르겠다면 취소를 선택하여 작업을 중지할 수 있게 합니다.

  

    iEncoding = tEnc

  '처음 열린 파일은 구분자가 아무것도 없기 때문에 A열에만 텍스트가 열립니다. 따라서, A열을 선택한 후, 엑셀에 내장된 텍스트 나누기 창을 열어 구분자를 입력할 수 있게 해줍니다.

    iSh.UsedRange.Columns(1).Select
    Application.Dialogs(xlDialogTextToColumns).Show

  '구분자 설정이 완료되면, QueryTable을 지워주기 위해 Names를 삭제하는 함수를 실행시켜줍니다. 별도의 함수로 작성되어있으니 확인 바랍니다. 특별히 지워주지 않더라도 별 문제는 없으나, 간혹 에러가 뜰 수 있습니다.
    DeleteNames ActiveWorkbook, tName

  '마지막으로 현재 매크로를 다시 실행시켜서, 인코딩과 구분자를 확정하여 다시 파일을 열어주게 됩니다. 일종의 재귀함수의 변형입니다.

    LoadTextFile iSh, iFilename, False, tEnc

  Else

  '첫번째 이후 파일이 열릴 때에는 인코딩이나 구분자를 변경할 필요가 없기 때문에, 별도의 입력없이 열릴 수 있도록 자동으로 파일이 열리게 합니다. 단, CSV 파일이 열릴 때에는 콤마를 무조건 구분자로 지정합니다.

    iSh.UsedRange.Delete xlToLeft
    With iSh.QueryTables.Add(Connection:="TEXT;" & tFN, Destination:=iSh.Cells(1, 1))
      .FieldNames = False
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = False
      .RefreshOnFileOpen = False
      .RefreshStyle = xlOverwriteCells
      .SavePassword = False
      .SaveData = False
      .AdjustColumnWidth = False
      .RefreshPeriod = 0
      .TextFilePromptOnRefresh = False
      .TextFileStartRow = 1
      .TextFileCommaDelimiter = (tFExt = "csv")
      .TextFilePlatform = iEncoding
      .Refresh BackgroundQuery:=False
      .MaintainConnection = False
      tName = .Name
    End With
    DeleteNames ActiveWorkbook, tName
  End If
  iSh.QueryTables.Item(iSh.QueryTables.Count).Delete
  LoadTextFile = True
  Exit Function
ErrorHandler:
  LoadTextFile = False
End Function
-------------------------------------------------

Function DeleteNames(iWB As Workbook, iName As String)
  Dim n As Long, tName As Name, tIsName As Boolean
  tIsName = False
  For Each tName In iWB.Names
    If Right(tName.Name, Len(iName)) = iName Then n = tName.Index: tIsName = True: Exit For
  Next
  If tIsName Then iWB.Names(n).Delete
End Function

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

DeleteNames는 현재 워크북의 이름 관리자에 등록된 이름을 삭제시킵니다. QueryTable로 파일을 열면, 이름 관리자에 등록되는데, 이게 간혹 에러를 발생시키기 때문에 삭제하는 것입니다. 파일을 여는 데에는 큰 문제가 없습니다.

이렇게 텍스트 파일을 열 수 있도록 함수를 만들어두면, 여러 개의 파일을 선택하여 원하는 워크시트에 열어올 수 있습니다. 다음 번에는 여러개의 파일을 선택하고, 자동으로 워크시트를 생성하면서 파일을 열 수 있도록 나머지 부분을 작성해보도록 하겠습니다.