[엑셀 VBA] 자동화 - 1. 데이터 입력, 2. 양식에 자동으로 대입 3. 이메일 자동 발송

일반 회사 업무에서 가장 많은 케이스 일 것입니다.

첫번째, 데이터 입력은 필드명부터 데이터가 들어가 있는 값들이 어느정도 정규화 되어 있어야 합니다.

중구난방의 필드명이나, 구별 또는 관리가 되어 있지 않은 데이터는 쓸모가 없습니다. 체계적으로 관리된 데이터는 활용도가 매우 높습니다. 저같은 경우는 엑셀로 DB파일(그냥 ".xlsx" 파일입니다.)을 하나 만들어 데이터만 따로 모아두고, 실제 업무를 보는 엑셀 파일이 따로 있습니다. 저장해 놓은 DB에서 데이터를 가져오고 수정하고 저장하고 합니다. 사실 엑세스를 주로 쓰는데, 제가 만든  프로그램들을 사용하시는 분들이 엑세스를 전혀 하지 못하기 때문에 궁여 지책으로 그렇게 합니다.

이 부분은 오빠두님의 강좌를 필히 보셔야 합니다.

관계형 데이터베이스를 조금만 이해하신다면 내가 이 데이트들을 어떻게 조합해서 쓸 수 있겠다는 것을 생각해서 각각의 "필드"들을 구성하고 이름을 명명하는 것이 좋습니다. 특히 참조할 ID를 만들어 두는 것은 매우 중요합니다. 원하는 데이터를 가지고 올 때, 그 ID를 참조해여 그 행을 가져오기 때문입니다. SQL을 몰라도 For~Next문이나, Range().Find() 같은 것을 이용하여 쉽게 찾아 올 수 있습니다. 고급 필터를 쓰면 CriteriaRange 부분에 ID를 기준으로 찾아오게 할 수도 있습니다. 관계형 데이터베이스 구축하는 법은 맡은 프로젝트 하나 끝내고 올려보도록 하겠습니다.

데이터를 이렇게 관리해 하는 이유는 가령 높으신 분이, "김대리, 우리회사에 30-35 사이의 서울사는 미혼 남성 이력서 좀 뽑아와봐."라고 했을 때, 수동으로 필터, 필터, 필터, 필터 이렇게 뽑은 데이터가 엑셀 시트에 있다고 해봅니다. 데이터 열들은 있는데 이를 양식에 옮기는 게 문제겠지요. 하니씩 입력하고, 출력하고 이를 반복하기는 쉽지 않은 문제일 것입니다. 게다가 일반 필터링은 열을 숨기는 방식으로 보이는 데이터만 가져오기(오빠두님 함수가 있습니다.) 쉽지 않을 것입니다. 그냥 DB파엘에서 Query를 던지거나(?), AdvanceFilter쓰는 것이 더 빠르고 효율적입니다.

위와 표를 보면 "EID"라는 필드명이 있습니다. 매우 중요한 역할을 하는 녀석입니다. 만역 2번 행을 가지고 오고 싶으면,

"A" 칼럼(EID필드)을 검색하여 해당 아이디를 찾으면 행번호를 알아 낼 수 있습니다. 여기서 2번 행이라 하겠습니다.

2번 행을 읽어 오는 방법은 여러가지가 있습니다만

첫번째 Range객체에 통째로 담아두기. 배열로 변환이 가능합니다. 횡으로 배열되어 있는 데이터를 종으로 바꾸어 붙이려면, Application.WorksheetFunction.Transpose를 쓰면 간단히 해결됩니다.

Dim rng As Range
Set rng = Range("A2:AA2")

두번째, 열만큼 루핑을 돌면서 값을 가져오기. 이때는 Dictionary를 써서 필드명까지 같이 담아 두면 양식에 대입하기 수월해 집니다.

Dim dict As New Scripting.Dictionary
Dim i As Long
Dim key As String
Dim val
 
For i = 1 To 칼럼의 수 만큼
   key = Cells(1, i).Value
   val = Cells(찾은열, i).Value
   dict.Add key, val
Next i

여기서 Key값을 잘 활용하면 양식에 대입이 쉬워집니다. Key값이 바로 필드명이기 때문입니다.

이제는 이러한 양식에 가져온 값을 집어 넣는 것이 일이겠지요. 자세히 보시면 각각의 필드명이 보일것입니다.

양식의 각각의 이름을 위의 Key값과 가급적 매치 시키도록 하는 것이 중요합니다.

그리고 나서 얻어온 값을 양식 시트에 뿌리는 것입니다. 저는 주로 각각의 들어갈 셀의 주소를 아래 처럼 배열로 만들어 놓고 루프로 돌려 버립니다. 수정이 필요치 않은 경우라면 셀참조 방식으로 데이트를 뿌리는 방법도 있습니다.

Dim arrCells
arrCells = Array("C2", "D3", "C4", ....)

그럼 해당 자료가 들어간 양식을 인쇄범위로 지정해 놓고 인쇄를 하던가 엑셀이든 PDF파일로 저장한다음 이메일로 뿌려주는 함수를 호술할 것입니다. 이렇게 반복해야 할일이 1만건이라 하면

1. 데이터 검색해서 해당 자료 DB에서 가져옴 (1만건)
2. 아래처럼 루프를 돌면서 
   2.1 행을 읽어들이고
   2.2. 양식에 뿌리고
   3.3 인쇄하거나 메일로 보낸다.
 
For i = 1 To 10000
   1. 행을 읽어 들인다.
   2. 양식에 뿌린다.
   3. 인쇄하거나 메일로 보낸다.
Next i

이렇게 하면 알아서 1만번을 돌면서 같은 일을 나 대신 컴퓨터가 해 줄 것입니다. 커피한잔하고 오면 끝나 겠네요.

로직은 이렇습니다. 그런데 문제는 이를 어떻게 구현해야 할지 모르니까 문제인 것이지요. 함수만 가지고 놀면 자동화는 요원입니다. 그냥 시트에 있는 셀들만 가공해 줄 뿐이니까여. VBA의 공부를 어드 정도 하셔야 합니다.

회사에 이런식으로 처리 해야할 일이 많다면 반드시 해두셔요 몸과 마음이 편해 집니다. ^^