3. 일찍 퇴근하기 (VBA 기초 + 모든 시트 병합)

안녕하세요, 오늘은 엑셀에 내장된 VBA 코드를 이용하는 방법과  

여러 엑셀로 받은 일정한 양식의 파일들을 하나의 엑셀 시트로 불러오는 작업을 해보겠습니다.

 

VBA를 한번도 이용해보지 않으신 분들을 위해 VBA 코드 작성 방법 부터 천천히 다룰 예정이니

코드만 필요하신 분들은 가장 하단의 코드만 복사해 이용하시기 바랍니다.

 

 

1. 샘플 자료 준비하기

     1_1. 한 폴더에 엑셀 담기(예시 파일 생성)

 

먼저 샘플로 이용할 n개의 '예시*.xlsx' 파일을 생성하여 한 폴더에 담아두었습니다.

 

 

 

    1_2. VBA 작업 할 *.xlsm 파일 생성

 

VBA 코드가 포함된 상태로 저장을 하기 위해선 *.xlsm 형식으로 저장을 해야 합니다.

그렇지 않을 경우, 엑셀 종료 후 다시 켰을때 코드가 저장되지 않아 일회성이 되므로 주의합니다!

 

2. VBA 기초

     2_1 VBA 프로젝트 생성

엑셀 화면에서 'Alt + F11' 키를 입력하면 아래 화면이 나타납니다.

 

 

이 상태에서 작업하기 편하게 아래 화면에서 밑줄 친 부분들을 클릭합니다.

 

직적실행창, 지역 창, 프로젝트 탐색기를 모두 클릭하셨다면 아래 화면과 같을거예요

 

그런 다음 V 마크된 공간에 우클릭 후 모듈을 생성해 줍니다.

 

     2_2. 간단한 코드 연습(셀 값 가져오기, 값 입력하기)

          2_2_1. 셀 값 가져오기

먼저 아래와 같이 샘플용 값을 입력해두었습니다.

 

이 상태에서 코드를 작성하자면, 먼저 불러온 값이 담길 변수 이름(ex 셀A1, 셀A2) 을 적고 담길 값(값이 있는 위치) 를 지정해주면 됩니다.

Sub 셀_값_가져오기()


셀A1 = Sheets("sheet1").Cells(1, 1).Value 'A1 셀 값을 "셀A1" 라는 변수에 담는 코드

셀A2 = Sheets("sheet1").Cells(2, 1).Value 'A2 셀 값을 "셀A2" 라는 변수에 담는 코드


셀C1 = Sheets("sheet1").Cells(1, 3).Value 'C1 셀 값을 "셀C1" 라는 변수에 담는 코드
셀C2 = Sheets("sheet1").Cells(2, 3).Value 'C2 셀 값을 "셀C2" 라는 변수에 담는 코드



End Sub

 

 

그 후 'F8' 버튼을 이용해 코드를 한 줄씩 실행시키며 End Sub 전까지 실행하면

각 셀 값을 가져올 수 있습니다.

 

          2_2_2. 셀 값 입력하기

 

셀에 값을 입력하는 방법은 2_2_1과 반대로 하면 되겠죠?

 

먼저 입력하고 싶은 셀의 위치를 정한 다음, 입력하고 싶은 값을 적어줍니다.

 

Sub 셀_값_입력하기()



Sheets("sheet1").Cells(1, 2).Value = "B1" 'B1 셀에 "B1" 값을 입력하는 코드

Sheets("sheet1").Cells(2, 2).Value = "B2" 'B2 셀에 "B2" 값을 입력하는 코드

Sheets("sheet1").Cells(3, 2).Value = "B3" 'B3 셀에 "B3" 값을 입력하는 코드

Sheets("sheet1").Cells(4, 2).Value = "B4" 'B1 셀에 "B4" 값을 입력하는 코드

End Sub

 

마찬가지로 'F8' 키를 이용해서 한 줄씩 실행하면, 아래 이미지와 같이 값들이 입력되는 것을 확인할 수 있습니다.

 

 

3. 여러 엑셀 시트 병합

아래와 같은 한 폴더 내에 있는 엑셀들을 하나씩 불러와 한 시트에 담아보겠습니다.

코드를 작성할 때에는 컴퓨터에게 어떤 작업들을 명령할지 상상한 후 시작하면 좋아요.

 

1. 컴퓨터가 작업을 수행하기 위해 알고 있어야 할 내용들을 알려준다.

2. 내가 수행하고 싶은 작업 내용을 입력한다.

 

 

우리가 진행할 내용을 정리하자면 아래와 같겠죠??

 

1. 컴퓨터가 알고있어야 할 변수들

     a. 병합할 엑셀들의 경로와 이름

     b. 각 엑셀에 가져올 자료가 담겨있는 시트명

     c. 각 시트마다 가져올  자료의 범위

     d. 가져온 자료가 담길 위치

 

2. 작업내용

    a. 가져올 엑셀을 연다

    b. 자료가 담겨있는 시트로 이동한다

    c. 가져올 자료 범위를 복사한다

    d. 자료가 담길 위치에 붙여넣는다

    f. a~d 까지의 작업을 순차적으로 반복한다

 

Sub 엑셀별_시트_병합하기()

메인파일 = "메인엑셀.xlsm"

'엑셀들이 담겨있는 폴더명 가져오기
폴더명 = ThisWorkbook.Path + "\"

'폴더 내에 ".xlsx" 형식의 첫번째 엑셀 이름 가져오기
파일명 = Dir(폴더명 & "*.xlsx")

i = 1
'Do while ~ Loop 문을 이용하여 폴더 내의 전체 "*.xlsx" 파일들을 탐색하면서
'하나의 "*.xlsx"  파일을 불러올 때마다 복사 기능과 붙여넣기 기능들을 넣고 반복문을 추가하였습니다.

Do While 파일명 <> ""
    
    '가져온 파일명에 폴더명을 추가하여 전체 경로를 불러오게 합니다.
    전체경로 = 폴더명 & 파일명
    
    '잘 가져오고 있는지 직접 실행창에 프린트 해서 검토합니다.
    Debug.Print (전체경로)
    
    Set 엑셀파일 = Workbooks.Open(전체경로)
    '전체경로 엑셀 실행
    
    '모든 엑셀에서 첫 번째 시트에 있으므로 이용하지 않았음
    시트명 = "Sheet1"
    
    '복사할 위치의 우측 끝과 아래끝 위치를 미리 찾아둠
    우측끝번호 = Range("A1").End(xlToRight).Column
    아래끝번호 = Range("A1").End(xlDown).Row
    
    '미리 찾아둔 우측 끝과 아래끝 좌표를 이용하여 복사할 범위를 지정
    Set 범위 = Range(Cells(2, 1), Cells(아래끝번호, 우측끝번호))
    
    '메인 엑셀 파일 활성화
    Windows(메인파일).Activate
    
    If i = 1 Then
        '지정된 범위를 복사 후 붙여넣기 할 장소 선택 (cells(1,1)은 타이틀이 들어가야 하는데,
        '타이틀은 직접 붙여넣었음
        범위.Copy Destination:=Cells(2, 1)
        
    Else
        
        범위.Copy Destination:=Cells(Range("A1").End(xlDown).Row + 1, 1)
        
    End If
    
    ' 엑셀파일 닫기 (저장하지 않음)
    엑셀파일.Close SaveChanges:=False
    
    'Do while 문의 끝 지점을 정하기 위해, 다음 파일명이 존재하는지 확인합니다.
    파일명 = Dir
    
    i = i + 1
    
Loop

End Sub

 

다음 포스팅에서는 위 코드를 응용하여, 여러 엑셀 자료들을 각 시트에 붙여넣도록 해볼게요~