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

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

 

 

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

 

 

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

myFile = "C:\datainfo.txt"

 

 

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

myFile = Application.GetOpenFilename()

 

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 VBA 시트에 있는 내용을 파일, 텍스트로 출력하기 ▶ 엑셀 VBA 문자열 글자 하나씩 추출하기 ▶ 엑셀 VBA 데이터 입력하는 텍스트 박스(Text Box) 사용하기 ▶ 엑셀 VBA 문자열, 텍스트 숫자를 문자열 숫자로 변경하기 ▶ 엑셀 VBA 연산자 사용해서 String 문자열을 합치기

 

 

 

▼ 이렇게 리턴 받은 경로로 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

 

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 VBA 시트에 있는 내용을 파일, 텍스트로 출력하기 ▶ 엑셀 VBA 문자열 글자 하나씩 추출하기 ▶ 엑셀 VBA 데이터 입력하는 텍스트 박스(Text Box) 사용하기 ▶ 엑셀 VBA 문자열, 텍스트 숫자를 문자열 숫자로 변경하기 ▶ 엑셀 VBA 연산자 사용해서 String 문자열을 합치기