Please Enable JavaScript!
Gon[ Enable JavaScript ]

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

엑셀(Excel)/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 문자열을 합치기
Posted by 녹두장군1
,