출처: http://simon-k.tistory.com/11
실무적인 엑셀 VBA에 관한 질문이 들어와서, 엑셀 VBA 기초 강의와는 별개로 실전 예제도 연재해 보려고 합니다.
익숙하지 않은 내용들이겠지만 실전 예제를 통해 공부하는 것도 큰 도움이 될 것이라 생각합니다.
같이 진행되는 엑셀 VBA 기초 강의와는 번외적인 내용으로, 진도에 맞지 않는 점 인지하여 주시기 바랍니다.
엑셀 VBA는 매우 강력한 프로그래밍 툴으로써, 외부파일과 연계도 가능합니다.
이번 포스팅은 텍스트 파일을 엑셀에 입력하는 예제를 소개해드리도록 하겠습니다.
위와 같이 열과 열이 일정한 관계를 갖고 있는 텍스트 파일의 경우, 동일한 배열으로 엑셀에 옮겨담을 수 있습니다.
위의 텍스트 파일은 열 간의 간격이 탭키로 설정되어 있지만, 콤마( , ), 세미콜론( ; ) 등 뿐만 아니라 어떠한 규칙도 그 규칙이 일정 하다면 충분히 엑셀로 옮길 수 있습니다.
이 텍스트 파일의 첫번째 줄을 제외하고 숫자부분을 열에 맞춰서 엑셀에 옮기고자 합니다.
이 명령을 실행하는 엑셀 VBA 코드는 아래와 같습니다.
Sub text_to_excel()
Const loadf As Long = 2 '텍스트 파일 속에서 가져와야 하는 시작하는 행 위치
Const loadt As Long = 99999 '텍스트 파일 속에서 가져와야 하는 마지막 행 위치보다 큰 값을 설정
Dim strFileName As String '텍스트 파일 이름 설정
Dim objText As Object '텍스트 문서 값 개체변수
Dim i As Long '변하는 행값 변수
Dim varValue As Variant '엑셀에 입력되는 값
Application.ScreenUpdating = False
With Application.FileDialog(msoFileDialogFilePicker)
.Show '파일피커 열기
If .SelectedItems.Count = 0 Then '아무것도 선택되지 않았을 경우 프로시저 종료
Exit Sub
Else
strFileName = .SelectedItems(1) '파일 이름 지정
End If
End With
If Len(strFileName) > 0 Then '파일 이름의 길이가 0보다 크면 (파일이 존재하면)
Set objText = CreateObject("Scripting.FileSystemObject").OpenTextFile(strFileName, _
IOMode:=1, Create:=False, Format:=-2) '지정된 이름의 파일을 열기
For i = 1 To loadt '지정한 행 수 만큼 반복하기
If Not objText.AtEndOfStream Then '텍스트파일의 끝이 아니라면
If i < loadf Then '행의 위치가 지정된 시작 행(loadf)보다 작다면
objText.skipline '그 행을 뛰어 넘는다
Else
varValue = Split(objText.ReadLine, vbTab) '텍스트파일의 한 행의 값을 탭으로 분리해서 가져온다
'vbTab을 "," ";" " " 등으로 바꿔서 사용할 수 있다.
Cells(Rows.Count, 1).End(3)(2).Resize(, UBound(varValue) + 1) = varValue '셀에 텍스트파일 값을 넣기
End If
End If
Next i
End If
objText.Close
Set objText = Nothing
End Sub
위 엑셀 vba 프로시저를 끝낸 후 엑셀에 출력되는 값은 아래와 같습니다.
만약에 특정 열의 값만 추출하고 싶다면 파란색으로 표시된 varValue 를 varValue(숫자) 로 바꿔주시고, 주황색으로 표시된 UBound(varValue)+1 부분을 1로 바꿔주시면 됩니다.
varValue의 숫자값은 0부터 시작하는 정수가 되어야 합니다.
예를 들자면, varValue(1)는 2번째 열만 가져오게 됩니다.
이러한 방법을 통해서 텍스트 파일을 엑셀으로 옮겨올 수 있습니다.
그냥 넘어가긴 아쉬우니 제가 엑셀 VBA에서 자주 쓰는 간단한 편법을 하나 공개하도록 하겠습니다.
엑셀 VBA에서는 Worksheetfunction을 사용해서 엑셀 고유의 함수 기능을 구현할 수 있습니다.
하지만 Worksheetfunction을 굳이 사용하지 않고 엑셀 함수를 적용할 수 있는 방법 또한 존재합니다.
예를 들어 텍스트 파일의 2번째 열만 추출해서 평균값을 "E2"셀에 넣고, 평균값을 제외한 모든 값을 삭제해 보겠습니다.
Sub text_to_excel()
Const loadf As Long = 2 '텍스트 파일 속에서 가져와야 하는 시작하는 행 위치
Const loadt As Long = 99999 '텍스트 파일 속에서 가져와야 하는 마지막 행 위치보다 큰 값을 설정
Dim strFileName As String '텍스트 파일 이름 설정
Dim objText As Object '텍스트 문서 값 개체변수
Dim i As Long '변하는 행값 변수
Dim varValue As Variant '엑셀에 입력되는 값
Application.ScreenUpdating = False
With Application.FileDialog(msoFileDialogFilePicker)
.Show '파일피커 열기
If .SelectedItems.Count = 0 Then '아무것도 선택되지 않았을 경우 프로시저 종료
Exit Sub
Else
strFileName = .SelectedItems(1) '파일 이름 지정
End If
End With
If Len(strFileName) > 0 Then '파일 이름의 길이가 0보다 크면 (파일이 존재하면)
Set objText = CreateObject("Scripting.FileSystemObject").OpenTextFile(strFileName, _
IOMode:=1, Create:=False, Format:=-2) '지정된 이름의 파일을 열기
For i = 1 To loadt '지정한 행 수 만큼 반복하기
If Not objText.AtEndOfStream Then '텍스트파일의 끝이 아니라면
If i < loadf Then
objText.skipline
Else
varValue = Split(objText.ReadLine, vbTab) '텍스트파일의 한 행의 값을 탭으로 분리해서 가져온다
Cells(Rows.Count, 1).End(3)(2).Resize(, 1) = varValue(1) '셀에 텍스트파일 값을 넣기 '
End If
End If
Next i
End If
objText.Close
'(a2:a99999) 값은 평균 낼 데이터를 포함하는 범위
Cells(2, 5).value = "=average(a2:a99999)" 'average 함수식을 셀(E5)에 집어넣는다
Cells(2, 5).value = Cells(2, 5).value '출력된 함수값을 일반 형식의 값으로 바꾼다.
Range("a2:a99999").Value = "" '텍스트파일에서 추출한 데이터를 삭제한다
Set objText = Nothing
End Sub
초록색으로 표시된 부분이 추가된 부분입니다.
특정 셀에 함수식을 넣는 것만으로 함수식의 결과값을 얻어낼 수 있습니다.
average 함수 뿐만 아니라 엑셀의 모든 함수를 사용할 수 있습니다.
하지만 편법이다 보니 광범위하게 사용하기는 힘듭니다.
이런 방법도 있구나.. 하고 알아두시면 됩니다.
Worksheetfunction에 대한 내용은 추후에 엑셀 기초강의에서 다루도록 하겠습니다.
실전예제5.xlsm
실전예제1.txt