엑셀 VBA 텍스트 내용을 읽어 와서 시트에 분리해서 넣는 방법

엑셀(Excel)/VBA

엑셀 VBA 텍스트 내용을 읽어 와서 시트에 분리해서 넣는 방법

 

환경: Microsoft Excel 2013

 

엑셀에서는 외부에서 데이터를 가져올 수 있는 다양한 방법들을 제공하고 있습니다. Access, mssql, txt, csv, 웹 페이지, xml 등 대부분의 데이터 포맷 형태를 지원합니다. 그런데 데이터를 가져오는 과정에서 사용자가 필요 없다고 생각하는 것들을 걸러내는 기능이 미흡합니다. 이 때 VBA 를 이용해 보세요. 시간은 조금 오래 걸리겠지만 얼마든지 데이터를 분석해서 필요한 것들만 엑셀 시트에 추가할 수 있습니다.

 

오늘은 txt 파일에 있는 내용을 불러와 보도록 하겠습니다. 내용은 아래와 같습니다. 각 데이터는 Tab 크기만큼의 공백으로 분리가 되어 있습니다.

 

먼저 VBA 에서 파일을 읽는 방법은 2가지가 있습니다. 첫 번째는 파일을 위치를 알고 있는 경우 소스에 경로를 입력하는 것입니다.

 

myFile = "C:\datainfo.txt"

 

두 번째 방법은 GetOpenFileName() 함수를 이용하는 것입니다. 함수를 실행하면 파일 선택 팝업창이 뜹니다. 그리고 반환 값으로 파일 경로를 받을 수 있습니다.

 

myFile = Application.GetOpenFilename()


 

이렇게 리턴 받은 경로로 Open For Input As 로 텍스트에 내용을 읽어 와서 변수에 담게 됩니다. 아래 소스처럼 # 뒤에 있는 1 에 모든 데이터가 들어갑니다.

 

Open myFile For Input As #1

 

이제 전체 데이터를 Do Until EOF 반복문으로 null 이 나올 때까지 반복해서 한 줄씩 읽게 됩니다. 그리고 마지막에 열어 놓은 파일 개체는 Close 함수로 닫아야 합니다.

 

Do Until EOF(1)

 

Loop

Close #1

 

다음은 한 줄씩 읽어 보겠습니다. Line Input #1 에 담긴 내용을 한 줄씩 textline 변수에 담게 됩니다.

 

Do Until EOF(FileNum)

Line Input #1, textline

Loop

 

이제 한 줄씩 담긴 데이터를 각 셀에 집어 넣기 위해서는 분리해야 합니다. 텍스트에 담긴 내용을 보면 데이터 중간에 공백으로 구분이 되어 있습니다. 이것을 Split 함수로 하나씩 분리해 보겠습니다

 

그런데 텍스트에 나와 있는 데이터간의 구분은 스페이스바 두 칸이 아닙니다. 보통 엑셀에서 복사한 데이터일 경우 Tab 크기만큼 띄워 진 데이터 입니다. 그 말은 Split 함수를 사용해서 데이터를 분리하고자 할 때 분리 인자로 “ “ 공백을 넣어서는 안 된다는 것입니다. Tab 을 나타내는 상수값과 Char vbTab 또는 chr(9) 입니다.

 

arr() = Split(textline, vbTab)

 

마지막으로 배열에 저장된 데이터를 셀에 분리해서 넣는 소스 입니다. For 문을 돌면서 배열에 저장된 데이터들을 셀에 하나씩 집어 넣습니다.

 

For i = 0 To UBound(arr)

    Cells(nRow, i + 1).Value = arr(i)

Next i

 

위에서 설명한 전체 함수의 내용입니다. 아래 함수를 실행한 결과 텍스트에 있는 내용이 그대로 시트에 들어갔습니다. 이렇게 외부 파일의 내용을 하나씩 분리해서 검증할 수 있기 때문에 사용자가 원한다면 데이터를 걸러내는 로직을 삽입할 수가 있습니다.

 

Sub FileRead()

 

    Dim myFile As String, text As String, textline As String

    Dim arr() As String, i As Integer, nRow As Integer

   

   

    myFile = "C:\datainfo.txt"

   

    'myFile = Application.GetOpenFilename()

   

   

    Open myFile For Input As #1

   

    Do Until EOF(1)

               

        nRow = nRow + 1

        Line Input #1, textline

           

        arr() = Split(textline, vbTab)

       

        For i = 0 To UBound(arr)

            Cells(nRow, i + 1).Value = arr(i)

        Next i

       

    Loop

   

    Close #FileNum

  

End Sub

Posted by 녹두장군