[엑셀vba] 셀메이트 발송 확인 프로그램

오랜기간 셀메이트를 이용한 발송 업무를 해왔다. 그리고 발송 작업을 하다보면 발송 완료처리가 제대로 되지 않는 경우가 발생한다. 이런일이 발생하면 생길 문제는 다음과 같다.

1. 회사의 상품 재고가 맞지 않게된다. 완료처리를 하면 재고도 차감되는 방식이기에 완료처리가 되지 않는다면 상품 재고에 영향을 주게 된다.
2.  상품이 제대로 나갔는지 고객이 받기 전엔 확인하기 어렵다. 셀메이트에는 완료처리된 시간이 기록된다. 만약 발송 작업에 문제가 있어서 고객에게 잘못 전달되었다면, 완료처리 시간을 보고 발송 작업자의 cctv를 확인하게된다. 하지만 완료처리가 되지 않았다면 언제 어떻게 잘못나가게 됬는지 cctv를 볼 수가 없다. (발송 수량이 적으면 일일히 보면서 노가다로 가능하긴 할 듯..)
3. 고객상담 부서와의 친밀도 하락
4. 업무 정확도 하락
5. 이것저것 다 하락
6. 주가 하락
7. 일단 완료처리가 되지않으면 좋은 일은 없다.

하나의 잘못이 아주 많은 문제를 발생시킨다. 이 문제를 해결하는 방법은 없을까하다가 생각해냈다. 아직은 조금 미완성인 부분도 있지만 써보면서 발전 시켜나갈 예정이다.

 

---

간단 목차

 

1. 발송 작업
2. 해결 방안
3. 발송 확인 프로그램
4. DB 구조
5. 배치 파일 & 작업 스케줄러 설정

---

1. 발송 작업

기본적으로 셀메이트에서 송장 발송 작업 순서는 아래와 같다.

1. 셀메이트 상품발송 기능 입장
2. 송장 바코드를 스캔한다.
3. 상품 바코드를 스캔한다.
4. 모든 상품이 찍히면 완료처리가 된다.
5. 상품 포장을 하고 다음 송장 바코드를 스캔하며 1~ 4 작업을 반복한다. 

발송 작업 중 2번에서 바로 5번으로 넘어가는 경우 본문의 상단에 명시한 문제가 발생하게 된다. 어떻게 상품 바코드를 안찍을 수 도 있지? 라고 생각할 수도 있는데 발송해야할 수가 많고 아주 바쁘게 움직여야 할 때(택배사 마감시간)는 그럴 수 있다고 생각한다. 게다가 송장에 상품이 한 개만 있다면 더욱이 이런일이 발생하기 쉽다. 

---

2. 해결 방안

방안: 마지막으로 송장 바코드를 스캔한 계정이 누군인지 기록을 남긴다.

 

생각해낸 방안을 셀메이트에 전달하였으나 웹 브라우저 상에 나타내기는 힘들다라는 답변을 받았다. 대신 발송 작업 프로그램을 사용하면 발송 작업에 관련된 모든 기록이 로컬PC에 남는다며 한 번 써보길 추천하였다.

 

시험삼아 PC 한 대에 설치하여 이틀간 경과를 지켜봤다. 일반 웹에서 발송 작업하는 것과 다른 것은 없고 오로지 발송 작업만을 위한 프로그램이란 것을 잘 알게되었다. (다른 기능 일체없고 발송 작업만 가능)

로그도 살펴보니 송장이나 상품 바코드를 스캔한 내역을 물론이고 발송 작업 중 일어나는 상황들이 다 남는 것을 확인하였다. 다만 어떤 ID로 작업했는지는 나타나지 않았다. 이 부분은 추후 추가해보겠다 했다.

 

지금은 발송 작업하는 모든 PC에서 발송 프로그램을 이용해 작업을 진행하고 있다. 그리고 각 PC마다 배치파일과 작업 스케줄러(구. 예약 작업)을 사용하여 공유 폴더에 실시간 파일 복사를 1분 단위로 수행하고 있다.

 

마지막으로 그 날 발송 작업이 마무리되면 공유 폴더에 있는 발송 기록 파일은 Access에 필요한 데이터만 가공하여 저장하도록 했다. 

 

다음날 우리가 완료처리한 송장번호와 택배사에 가져간 송장번호를 비교하여 완료처리 되지 않은 송장을 색출한다. 그리고 발송 완료 확인 프로그램을 이용해 송장이 찍힌 시간과 계정을 찾아낸다. 해당 직원을 엄벌하도록 한다.

---

3. 발송 확인 프로그램

이걸 프로그램이라 해도 될진 모르지만, 그냥 프로그램이 하자.

 

송장 스캔 폼

 

날짜 선택 기능

주황색 버튼을 누르면 송장번호 검색 폼이 열린다. 그리고 원하는 날짜를 선택할 수 있도록 기능을 만들었다.

날짜 위에 마우스가 올라가면 볼드 처리되도록 하였고, 좌우 화살표를 클릭하면 다음, 이전 달로 넘어갈 수 있다.

 

검색결과없음

 

검색결과 있을 때

위 그림은 송장번호 68로 검색했을 때 나타나는 검색 결과이다. 로그에는 작업자 ID가 나오지 않는다고 했지만 우리는 자리마다 정해진 계정을 사용하므로 특별한 일이 없다면 99.99% 동일한 계정으로 발송 작업을 처리하게 된다. 그리고 처리 IP는 작업 PC의 내부 IP주소를 나타낸다.

 

※구현하지 않은 기능

1. 송장번호 입력 값 숫자만 받도록 하기
2. 설정 기능

---

4. DB구조

 

 

테이블은 날짜로 구분하고 필드는 시간, 송장번호, 처리ip, 처리id, 기타 다섯가지 존재한다. 그리고 레코드는 모두 짧은 텍스트 형식으로 저장한다.

테이블은 2036년 12월(15년치) 만들어둔 상태이며 언제까지 사용 할 수 있을지는 모르겠다. 흐흑 

 

발송 확인 프로그램과 별개로 DB에 데이터를 넣는 VBA코드는 따로 만들어서 사용하고 있다.

 

※ DB에 데이터 넣는 프로그램 동작 순서

1. 프로그램 실행
2. 로그 파일이 저장된 공유폴더에 새로운 파일 있는지 체크 (TXT 파일)
3. 새로운 파일 시트에 불러와서 필요한 데이터로 가공
4. 가공된 데이터를 해당 날짜의 테이블에 삽입
5. 작업된 TXT파일 이름에 '(완료)' 문자 붙임
6. 폴더마다 1 ~ 5 작업 진행
7. 모든 폴더의 작업이 끝나면 프로그램 종료

---

5. 배치파일 & 작업 스케줄러

배치파일은 간단하게 로그 파일을 공유폴더로 복사하는 copy 명령어만 사용했다. 그리고 주기적으로 배치파일을 실행하기 위해 작업 스케줄러를 이용하였다.

---

※ CMD copy 명령어 사용법

 

copy 복사대상경로 복사할 경로

경로에 띄워쓰기가 있다면 경로를 쌍따옴표로 감싸줘야 한다.

ex.

copy "C:\Program Files (x86)\Sellmate\셀메이트 발송 프로그램\Logs" c:\test

위 예시는 Logs 폴더에 있는 파일들을 test  폴더로 복사하는 명령어다.

---

배치파일 내용

별 내용없는 배치파일 내용이다. 그래도 동기화 프로그램이나 백업 소프트웨어를 설치하지 않더라도 간단히 파일 복사 할 수 있다는 장점이 있다. 이걸 주기적으로 반복하는 작업 스케줄러만 설정하면 이번에 만든 발송 확인 프로그램을 완벽히 사용 할 수 있는 환경이 만들어진다.

 

윈도우 10에서는 작업 스케줄러, 이하 버전에서는 예약 작업으로 찾을 수 있다. 버전마다 이름은 다르지만 인터페이스는 동일하다.

 

새 작업 만들기

이름(필수)과 설명(선택)을 적고 '가장 높은 수준의 권한으로 실행' 체크하고 트리거 탭으로 넘어간다. 

새 트리거 만들기

트리거는 언제 실행할건지 조건을 정하는 곳이다. 본인은 매일 1분에 한 번씩 실행하고 싶으니 그에 맞게 설정을 하겠다.

 

근데 5분이 가장 짧은 시간인가??

작업 반복 간격을 눌러보면 5분이 가장 짧은 시간처럼 보인다. 하지만 5분을 클릭하고 5를 1로 바꿔주기만 하면 1분마다 실행 할 수 있다.

1분마다 실행하도록 조건 설정

초 단위로 설정해봤지만 오류메세지를 띄우며 시간을 늘리라고 나온다. 트리거 설정 후 동작 탭으로 이동한다.

동작 설정

조건이 맞을 때 원하는 프로그램이 시작할 수 있게 위 그림처럼 설정한다. 그리고 설정 탭으로 이동한다.

 

조건 탭

조건 탭에선 필요한 내용이 있으면 사용하면 될 듯함.

지금 나는 여기서 쓸 내용 없으므로 그대로 두고 설정 탭으로 간다.

조건 탭

여기도 필요하면 설정하면 되는 곳이다. 나는 여기서 두 가지 옵션에 체크를 하였다. 

 

1.예약된 시작 시간을 놓친 경우 가능한 대로 빨리 작업 시작(S)

2.작업이 실패하는 경우 다시 시작 간격(T)

 

요기까지하고 확인 누르면 작업 스케줄러 설정은 끝이다.

 

 

그런데 작업 스케줄러 설정하고 문제점 하나를 발견했다.

1분마다 배치파일 창이 자꾸 켰다 꺼졌다하니까 거슬린다...

 

구글링을 해보니 vbs 파일로 배치파일을 실행하면 된다는 해결책을 찾았다.

메모장을 열고 다음 코드를 복사해서 붙여넣었다.

Set WinScriptHost = CreateObject( "WScript.shell" )
WinScriptHost.Run Chr(34) & "배치파일경로" & Chr(34), 0
Set WinScriptHost = Nothing

코드의 배치파일경로만 수정해서 저장할때 .vbs로하면 vbs파일로 만들어진다.

vbs 파일로 저장

 

생성 완료

아까 만들어둔 작업 스케줄러에서 동작 부분의 프로그램을 teset.vbs로 바꾸면 1분마다 CMD 창이 보이는 문제가 해결될 것이다.

 

마지막 설정

---