들어가며
안녕하세요? 전 스티브 잡스가 줄기차게 강조했던 "Connecting the dots"를 업무에서 항상 실현하려고 애쓰고 있답니다. 이번 포스팅에서는 Autohotkey, Excel, SafeArray 그리고 Regex를 합쳐서 반복적인 엑셀 내용을 클릭 한 번에 쉽게 고치는 방법을 알아볼 거예요. 전 프로그래밍 초심자라 한번에 꿀떡 삼키면 100 프로 체하니까 코드를 분쇄해서 한 구절씩 풀어서 쓰겠습니다.
아무리 좋은 자동차를 몰아도 목적지가 틀리면 아무 소용이 없지요. 프로그래밍에서도 그 목적지를 머리속에서 짜는 게 제일 먼저 해야 할 작업입니다. "그래서 이번 스크립트로 구현하고자 하는 건 뭐야?"라고 스스로에게 되묻는 것이죠. 제가 생각한 구조는 대략 이렇습니다.
사용자에게 원하는 엑셀 파일을 선택할 수 있는 창을 띄우기 → 사용자가 선택한 파일을 변수로 저장 → 이때 데이터를 2개로 나누기. 그대로 복사할 데이터 vs 수정할 데이터 → 수정할 데이터는 SafeArray에 모셔두기 → SafeArray에 있는 내용을 Regex로 수정 → 수정한 SafeArray를 새로운 엑셀 파일에 붙여넣고 저장
어때요, 참 쉽죠?
세부내용
1. 사용자가 바꾸고 싶은 엑셀 파일을 선택
fileselectfile,path
SplitPath,path,name
SplitPath,path,,dir
filename:=name
filename:=StrReplace(filename, " .xlsx")
oDownloaded:=ComObjGet(path)
oNewBook:=ComObjCreate("Excel.Application")
oNewBook.Workbooks.Add자, 명령어가 여럿 나왔네요. 하나씩 보죠. fileselectfile은 파일을 선택할 수 있는 창을 띄우는 명령어로 콤마 뒤에 넣은 변수에 파일의 전체 경로를 저장합니다. 이때 파일 이름도 함께 저장해요. 그럼 여기서 문제가 생겨요. 제가 생각한 것은 "Original.xlsx" 파일이 있으면 같은 폴더에 수정한 엑셀을 "Original_trimmed.xlsx" 이렇게 이름을 바꿔 넣는 것이거든요. 이 두 가지를 이뤄내려면 path 변수로 지정한 전체 경로를 나눠줘야 해요. 이때 등장하는 것이 Splitpath 명령어죠. Splitpath가 받는 첫 번째는 path 변수이고요, 다음 콤마에는 파일 이름을 저장할 변수를, 그 다음 콤마에는 파일 경로를 저장할 변수를 넣어줍니다. 그리고 가공하기 쉽게 파일 이름 변수에서 확장자인 .xlsx를 지워줍니다. 마무리로 ComObj를 활용하여 사용자가 선택한 엑셀 파일과 새롭게 생성할 엑셀 파일을 모두 변수로 지정합니다.
2. 엑셀 데이터 가공의 기본: 마지막 행과 열 파악
lastColumnDownloaded:=oDownloaded.Sheets(1).UsedRange.Columns.Count+4
lastRowDownloaded:=oDownloaded.Sheets(1).UsedRange.Rows.Count
lastColumnDownloadedLetter:=number_into_Letters(lastColumnDownloaded)엑셀 시트 내부의 데이터를 본격적으로 파악할 때 가장 먼저 해야 하는 게 있어요. 데이터가 들어있는 마직막 행과 열을 파악하는 것이죠. 참, 여기서 나오는데 시트 내부의 데이터로 접근할 때도 순서가 있습니다. 아무리 급하다고 해도 친구 집을 방문할 때 창문으로 들어갈 수는 없잖아요? 아파트로 치면 현관 → 거실 → 침실 순서인 것이죠. 이걸 엑셀에 대입해보면 "Application 레벨 (Workbooks) → Sheets → Range (Cells)"이랍니다. 엑셀 내부의 자료를 ComObj로 접근할 때에는 언제나 이것을 지켜야 해요. 그리고 마지막 행과 열을 파악할 때 사용하는 명령어는 UsedRange입니다. UsedRange는 위의 엑셀 순서에서 Sheets 뒤에 넣어야 해요. 그리고 나서 Columns 또는 Rows를 붙이고 Count까지 적어주죠.
마지막 줄이 특이한데 number_into_Letters라는 함수가 보입니다. 여기서 왜 함수를 넣었을까요? 정답은 ComObj가 Range를 선언하는 방식에 있습니다. Range 내부에 변수(Variable)를 넣을 수는 있으나, 이때 숫자가 아니라 알파벳으로 엑셀 행을 적어줘야 하거든요. UsedRange 명령어는 결과값으로 정수(Integer)를 뱉어내므로 숫자를 알파벳으로 바꾸는 작업이 필요해요. 즉, 1 → A, 2 → B, 3 → C로 치환해줘야 한다는 거죠. 이때 쓰는 함수는 아래와 같습니다. 스크립트 제일 밑에 붙여넣기 해주세요.
numbers_into_letters(in) {
Loop % ((in-1) // 26) + 1 {
out .= Chr(Mod(in-1, 26) + asc("a"))
}
}마지막 행과 열을 파악했으면 전체 내용을 새로운 엑셀 시트로 복붙합니다. 특이한 것이 새로운 엑셀 시트에 A1이 아니라 E1부터 붙여넣는데요, 제 경우는 새로운 엑셀 시트의 A부터 D행에 SafeArray로 수정한 내용을 집어넣어야 하기 때문이에요.
oNewBook.Sheets(1).Range("E1" ":" lastColumnDownloadedLetter lastRowDownloaded).value2:=oDownloaded.Sheets(1).Range("A1" ":" lastColumnDownloadedLetter lastRowDownloaded).value2SafeArray. 세탁(?)하려는 데이터를 모두 넣자
SafeArray라고 말하긴 했지만 그냥 Array(어레이)라고 보면 됩니다. 일반적인 Array와 엑셀의 Array가 다른 점은 바로 다차원(Multi-Dimensional)이란 점인데요, 전 괜히 골치아프게 만들기 싫어서 1차원으로만 Array를 짰습니다. Array를 선언할 때 행을 하나만 잡으면 1차원이 되고 2개 이상 잡으면 다차원이 된답니다.
context1:=oDownloaded.Sheets(1).Range("A3" ":" "A" lastRowDownloaded).value2
context2:=oDownloaded.Sheets(1).Range("B3" ":" "B" lastRowDownloaded).value2
source:=oDownloaded.Sheets(1).Range("B3" ":" "B" lastRowDownloaded).value2SafeArray 내부의 데이터를 입맛대로 수정
Loop, % context1[MaxIndex, 1]
varContext1:=context1[A_Index, 1]
varContext1:=RegExReplace(varContext1, "^.*\.ste_", "")
StringLower, varContext1, varContext1
context1[A_Index, 1]:=varContext1
varContext2:=context2[A_Index, 1]
varContext2:=RegExReplace(varContext2, "\^\d", "")
varContext2:=RegExReplace(varContext2, ":.*", "")
StringLower, varContext2, varContext2
varSource:=source[A_Index, 1]
varSource:=RegExReplace(varSource, ".*\^\d","")
varSource=%varSource%
source[A_Index, 1]:=varSource이번 포스트의 핵심입니다. Array에서 바꾸고자 하는 내용을 변수로 가져와서 머리를 자르고, 꼬리를 떼고, 양념을 쳐서 다시 Array에 집어넣는 과정이죠. 이때 사용하는 게 Regex(정규 표현식)입니다. Regex는 Regular Expression의 줄임말인데 마치 인간의 뇌가 사고하는 것처럼 텍스트를 찾고 수정할 수 있어요. 그래서 텍스트를 고칠 때 Regex를 쓰냐 안 쓰냐는 걸어서 가는 것과 비행기를 타고 가는 것만큼 차이가 크답니다. Autohotkey에서 Regex로 텍스트를 고칠 때 쓰는 명령어가 RegExReplace예요. 여기에 Loop를 끼얹어서 Array의 첫 번째 데이터부터 마지막 데이터까지 반복하며 텍스트를 수정합니다.
Loop문에서 특이한 것을 살펴볼까요? 먼저 context[MaxIndex, 1]입니다. Loop를 선언할 때 컴퓨터에게 몇 번 반복할지 말해줘야겠죠. 따라서 "Loop, % 3" 이렇게 반복 횟수를 넣습니다. 숫자 부분에는 변수를 넣을 수도 있어요. 우리는 Array의 1번 데이터부터 마지막 데이터까지 반복할 것이므로 Array 안의 데이터 개수가 필요합니다. 이때 데이터 개수를 나타내는 것이 context[MaxIndex, 1]입니다. 다음은 Array에서 특정 데이터를 불러와야겠죠. 현재 반복 회차가 1번이면 Array의 1번 데이터를 불러오고 2번이면 2번 데이터를 가져와야 합니다. 이때는 A_Index라는 것을 씁니다. 즉, context1[A_Index, 1]이라고 표현합니다.
마지막으로 RegExReplace는 간단합니다. 네 가지를 지정해줘야 하는데 1. 바꾸고 싶은 텍스트, 2. String 변수, 3. Regex 표현식, 4. 바꿀 내용이에요. 4번은 공백으로 바꿀 거면 생략해도 좋고요. RegEx 문법은 여기에 포스팅하기엔 너무 기니까 생략하겠습니다.
수정한 SafeArray를 새로운 엑셀 파일에 붙여넣기
이제 다 끝났습니다. 새로운 엑셀 파일의 원하는 부분에 SafeArray를 넣고 파일을 저장하기만 하면 돼요. 저는 SafeArray가 들어갈 곳으로 행 4개를 위에서 비워두웠습니다.
oNewBook.Sheets(1).Range("A3" ":" "A" lastRowDownloaded).value2:=context2
oNewBook.Sheets(1).Range("B3" ":" "B" lastRowDownloaded).value2:=context1
oNewBook.Sheets(1).Range("C3" ":" "C" lastRowDownloaded).value2:=source
oNewBook.ActiveWorkbook.SaveAs(dir "\" filename "_trimmed.xlsx")
oNewBook.Quit마지막 2줄이 파일을 저장 완료하는 명령어입니다. 이때 제일 처음에 나눠놓은 dir과 filename 변수를 가져와서 씁니다.
마치며
사실 엑셀은 Regex를 기본적으로 지원하지 않습니다. 엑셀에서 Regex를 사용하려면 VBA를 쓰거나 Autohotkey처럼 서드 파티 언어를 이용하여 우회 접근해야 하죠. 그럼에도 Regex를 써서 얻을 수 있는 이점은 그야말로 무궁무진하니 어떻게든 도입해서 활용하는 게 좋습니다. Find & Replace 100번 해야 할 것이 Regex 1번으로 끝날 수 있거든요. 스마트하게 사는 게 좋은 거 아니겠습니까!
