Autohotkey #25 , word파일에서 excel로 자료 이동(회원가입신청서 내용을 회원명부 엑셀로 옮기기)

youtu.be/3IjENQBvSlw

 

일반적으로 회원가입 신청서를 취합하는 경우, 통상적으로는 회원 관리하는 엑셀로 옮겨야 하는데 일일이 내용을 붙여넣는 것이 여간 곤욕이 아니다. 그래서 회원 가입 신청서를 엑셀로 옮겨 주는 것을 만들고자 한다.

 

일반적으로 회원 가입신청서는 대부분 이름, 주소, 전화번호, 이메일, 회사명, 회사주소, 회사번호 ... 등을 적게 되는데

항목이야 그만큼 늘리거나 줄이면 되는 것이고, 어떤 형식으로 word에서 엑셀로 옮길것인지 검토해 봐야 한다.

 

1) 사람의 동작을 따라 하는 방법

  먼저 사람이 수동으로 작업하는 경우에는 신청서의 이름을 복사해서 엑셀로 이동하여 붙여 넣거나, 직접 입력하는 방법이 있고, 이후에 순차적으로 주소를 복사해서 엑셀에 가서 붙여 넣기를 해야 한다.  입력할 항목이 늘어날 수록 일의 양은 증가하는데 이런 일이 그리 생산성이 높을리도 없고, 하는 사람도 신이 나지 않는다. 그래서 이 방법대로 그대로 자동화를 하면, word에서 복사하고 엑셀에서 붙여 넣고 다시 word에서 복사, 엑셀에서 붙여넣기를 반복해야 하는데, 자동화를 하더라도 그리 훌륭하게 느껴지지 않는다. 게다가 word와 엑셀간 왔다 갔다하는데도 일정 부분 시간 지연을 주는게 안전하다. 따라서 보다 효율적인 방법이 필요하다.

 

2) word에서 저장할 모든 항목을 변수로 저장하고 엑셀에서 순차적으로 넣는 방법

  처음의 방법은 저장 공간이 하나라는 전제가 있는 것인데, 컴퓨터의 메모리가 충분하므로 이를 활용해서, 모든 항목을 각각의 변수에 저장했다가 엑셀에서 차례로 붙여 넣는 방법이 있다. 만약 모든 항목이 별다른 차이가 없다면 배열등으로 잡아서 Loop문을 돌리는 방법도 가능하겠고, 내용별로 일부 손을 봐서 저장해야 한다면 개별적인 변수로 저장하는 방법이 더 좋다고 할 수 있다. 우리는 두번째 방법을 택하겠다.

  즉, 이름항목을 선택해서 name이라는 변수에 넣고, 주소항목을 선택해서 address등의 변수로 저장하는 것이다. 그렇게 하려면 어떻게 해야 할까? word문서를 직접 읽어서 진행하는 방법도 가능하고, 사람이 읽듯이 해당 항목으로 이동해서 그 내용을 그대로 변수로 저장하는 방법이 있다. word문서를 읽는 방법은 별도의 강좌에서 진행하는 것으로 하고, 이번에는 그냥 사람이 특정 항목을 읽는 것처럼 해당 항목으로 이동해서 복사하는 것으로 하겠다.  

  먼저, 해당 항목으로 이동하기 위해서는 문서의 처음으로 이동해야 한다. 모든 사람들이 문서를 저장할 때, 특정 위치를 지정하지 않고 아무데서나 저장한다. 따라서 무조건 문서의 처음으로 이동해야 한다. word에서는 Ctrl + PageUp을 누르면 문서의 처음으로 이동한다. 따라서  Autohotkey를 이용하는 우리는 다음과 같이 Ctrl + PageUp을 내보내면 된다. 

 

SendInput, {Ctrl Down}{PgUp}{Ctrl Up}

Sleep, 200

 

물론 이 명령을 내보내기전에 word문서를 실행하던지 열려진 word문서로 이동하는 동작이 필요하다. Sleep, 200은 200ms를 컴퓨터가 이전 명령을 완료할 시간적인 여유를 주는 것이다.

 문서의 처음으로 간 후에는 원하는 항목으로 이동해야 한다. 예로 든 문서의 맨 처음은 신청서 첫번째 칸이므로 해당칸에서는 특별히 할 일이 없고 이름을 가져오기 위해 이동해야 한다. 어떻게 이동하면 될까? 정답은 Tab키이다. 한번 누르면 이름칸으로 이동하고 해당 내용이 선택되어 있다. 어차피 선택해야 하는데 tab한번으로 이동과 선택까지 되었다. 그렇다면 우리가 할 일은 한번 더 tab을 누르는 것이다. 그러면 실제 이름칸으로 이동하고 선택까지 되었다. 따라서 해당 항목을 복사해야 한다. 그러려면 복사하기 동작은 바로 Ctrl+ C를 누르는 것이다. 그러면 해당 내용이 Clipboard에 저장이 된다. 이것을 변수에 저장하려면 변수명 := Clipboard 로 주면 된다. 하나의 동작으로 나열하면 다음과 같다.

 

SendInput, {Tab 2} ;이름
Sleep, 200
Sendinput, {CTRL Down}c{CTRL Up}
Sleep, 200
name := Clipboard
Clipboard:=

마지막에 Clipboard := 를 왜 실행했을까? 바로 안전하게 다음 항목을 복사하기 위해서이다. 예를 들어 다음 항목으로 이동했는데, 빈칸일 경우, Clipboard에 복사되는 내용이 없으므로 그전에 복사된 내용이 다음 변수로 저장되는 일이 생긴다. 그러면 내용이 중복되고, 잘못된 내용이므로 지워져야 하므로 그런 일을 방지하기 위해 사전에 Clipboard를 지워주는 것이다. 이런 방식으로 모든 항목에 대해서 순차적으로 변수에 저장했다면 다음에는 엑셀로 항목들을 이동해야 한다. 우선 엑셀은 미리 열려 있다고 가정하고 진행하도록 하자. 물론 프로그램 실행하면서 열수도 있지만, 컴퓨터마다 파일이 열리는 속도도 다르고 해서 일률적으로 맞추기가 쉽지 않으므로 쉽게 가자.

  엑셀로 이동하기 위해서는 다음과 같이 이동할 수 있다.

  winactivate, ahk_exe excel.exe

  sleep, 300

  xl := ComObjActive("Excel.Application")

  여기서 xl은 Excel을 다루기 위해 ComObjActive()를 이용해서 변수로 저장한것이다. 이후 엑셀 동작은 모두 xl을 기주으로 이뤄진다. 

  엑셀파일로 이동했다면 할일을 나열해 보자.

  먼저 기존 회원명부의 맨 마지막에 넣어야 하는데 이것은 어떻께 알 수 있을까? 엑셀의 마지막행 찾기를 autohotkey에서 진행해야 하는데 바로 다음과 같이 알 수 있다.

  row :=  xl.ActiveWorkbook.Sheets("Sheet1").UsedRange.Rows.Count

 그런데 이 마지막행은 우리가 넣으려고 하는 행의 바로 전행이므로 여기에 1을 더해주면 우리가 넣을 행을 찾은것이다.

첨부한 엑셀의 첫번째 항목은 순번인데 맨 첫줄이 제목에 해당하므로 마지막행의 수와 같다. 따라서 순번은 마지막행을 넣어 주고, 그 다음 셀에 이름, 주소 등을 순차적으로 넣어 주면 된다. 

  xl.Cells(row,col).value := (row -1)

  Sleep, 200  

  col += 1

  xl.Cells(row,col).value := name

  Sleep, 200  

 첫줄의 row-1만해도 될텐데 왜 ()로 쌌을까요? 특정 case에 정상적인 수식이 동작하지 않고 오류가 날때가 있는데 이 경우에 일부 헷갈릴 소지가 있는 부분을 ()로 감싸주면 autohotkey가 헷갈리지 않고 잘 수행됩니다. 이후에 name변수를 저장하는 과정이며, 이후의 동작은 다 동일합니다. 다만, 일부 변수로 저장된 내용의 경우 약간의 조작이 필요한 경우가 있습니다. 바로 (회사명), (주소)등으로 이미 내용에 사용자에게 알림용으로 넣어둔 내용들은 지워야 합니다. 해당 내용을 지우기 위해서는 StrReplace()를 이용하면 좋습니다. 다음의 경우는 회사명에 대한 일부 처리가 들어간 부분입니다. 

buso := StrReplace(buso,"(회사명)")
buso := StrReplace(buso, "`r") 
buso := StrReplace(buso, "`n")

buso := Trim(buso)

먼저 "(회사명)"을 없애고, \r, \n도 없앤 후, 앞뒤공백을 지워줍니다. 그러면 순수하게 주소만 남게 됩니다.

이런 방식으로 회사 주소나 마지막 가입일쪽을 처리해 주면 됩니다. 특히나 가입일의 경우 앞뒤로 내용을 지우고, 중간에 항목만 처리해야 합니다. 이러한 내용의 일들을 순서적으로 나열하면 다음과 같습니다.

  먼저 gui요소들을 구성하고, 버튼별로 해야할 일을 나열합니다. 그리고 Esc, Gui Close동작에 대한 처리를 추가합니다.

 다음은 모든 항목을 구현한 ahk파일, 실행파일, 그리고 가입신청서 예제 word파일 및 결과 excel파일입니다. 

memberExcel.ahk

0.00MB

memberExcel.exe

1.06MB

member.docx

0.02MB

member.xlsx

0.01MB