[Excel] VBA로 데이터 자동화하기
- 인프런 '데이터 자동화(with VBA)' 배영자 님의 강의를 수강했습니다.
엑셀 자동화 코드를 만들어줄 수 있냐는 의뢰를 받았다. 지금 작업 방식은 년도별로 달라지는 데이터를 업데이트하기 위해 각 년도별 파일을 모두 하나하나 열어서, 해당하는 값을 찾고, 데이터 셀을 복사해서 가지고와 붙여넣는다고 하셨다. 분명 자동화하면 도움이 될 것이다. 나는 컴활 1급이 있지만 장롱 컴활이기에 따로 공부를 하면서 만들어보기로 했다.
지금 해야하는 일은 파일 간 데이터 이동이다. 하나의 시트에, 해당하는 년도별 데이터를 종합해 모아놓아야 나중에 업데이트할 때도 편리하다. 지금까지 알고 있는 지식으로는 파일을 모두 열어놓으면 데이터 공유가 가능하다고 알고 있었다. 근데 매번 2013~2023까지의 파일을 모두 열어놓고 작업을 하면 부하가 너무 심할 것 같았다. 찾은 방법은 두 가지가 있다.
1. 파워커리를 이용하면 닫혀있는 파일이라도 값을 가져올 수 있다고 한다. 대신 엑셀 파일이 동일한 폴더에 들어있어야 한다. 그리고 '폴더에서 데이터 불러오기'는 맥 엑셀에서는 지원하지 않기 때문에 윈도우 운영체제를 이용해야 한다.
2. VBA를 이용한다. 대신 이건 파일이 모두 열려있어야 하고 직접 코딩해야 하는 ... 삽질의 시간이 요구된다.
파워커리 vs VBA
지피티왈, 단순데이터 처리와 자동업데이트는 power Query, 엑셀의 까다로운 작업을 자동화나 액셀 내 작업 자동화는 VBA를 추천한단다.
맥으로 갈아탄지 약 6개월? 이 됐는데 파워커리를 이용해보고자.. 자고 있는 그램을 포맷하고 기다리고 있다. 그동안 VBA를 공부해봐야겠다.
VBA 코드
폴더에 엑셀 파일을 모아놓고, 파일 내에서 엑셀 파일을 돌면서 값을 복사해오는 코드다.
Sub Getdata_infolder()
dim strPath as string, fname as string
dim wb as workbook
dim rng as range
dim cntrows as long
application.screenupdating = false
sheet1.usedrange.offset(1).clear
strPath = environ("userprofile") & "\Desktop\folder_name\"
fname = dir(strPath & "*.xlsx")
if fname = "" then
magbox = "폴더 내 엑셀 파일이 존재하지 않습니다."
exit sub
end if
do while fname <> ""
set wb = workbooks.open(filename := strPath & fname, updatelinks := 0)
set rng = wb.sheets(14).usedrange
set rng = rng.offset(1).resize(rng.rows.count - 1)
cntrows = rng.rows.count
`rng.copy sheet1.Cells(rows.count, "a").end(3)(2)
sheet1.cells(rows.count, "a").end(3)(2).resize(cntrows, 4) = rng.value
wb.close
fname = dir
loop
application.screenupdating = true
end sub- application.screenupdating = false : 속도 향상을 위한 코드(true가 기본값)
- usedrange : 엑셀에서 사용된 영역을 감지해 범위를 자동 설정해주는 기능
- offset(1) : 폴더 내 엑셀 파일 데이터를 복붙해오는 VBA 코드다. 현재 코드에서 첫 행(해당 열의 항목명 - 이름, 나이 등)을 제외한다.
- strPath : 엑셀 파일이 들어있는 폴더를 가져올건데 각 개인의 컴퓨터명이 달라서 environ("userprofile")을 이용한다. 아니면 직접실행(ctrl + G)를 눌러서 '? thisworkbook.path' 를 입력하여 경로를 받아올 수 있다.
- fname, 즉 파일이 있는 동안 파일에서 원하는 값을 복사해온다.
- wb에서 updatelinks := 0 는 외부 링크 업데이트를 하지 않는다는 것으로 셀에 걸린 함수식(외부 참조) 업데이트하면서 생길 오류를 방지하고자 함이다.
- 값을 받아올 때는, 첫 행을 받아오면 중복되니까 set rng = sheet().usedrange 후에 첫 행을 빼주고 offset(1) 밑 행만으로 지정한다.
복사는 copy 자체를 이용한 방법보다 rng.value에 값을 지정해주는 것이 더 빠르다.
- Cells(rows.count, "a") -> 시트1의 셀 중에서 A열의 마지막 행을 찾는다. (데이터 유무와 관계없이)
- end(3)은 데이터가 있는 값으로 이동을 말한다. 3은 x1Up의 의미로 위로 한 칸 가라는 뜻이다.
- end(3)(2)는 현재 위치에서 한 행 아래로 이동, 즉 데이터가 있는 값 바로 아래 빈 셀을 가리키라는 말이다.
- resize(cntrows, cols) 여기서 열의 개수는 고정이니까 4를 넣어준거다.
- fname = Dir 은 이전 호출에서 찾은 다음 파일을 반환한다. == 다음 파일을 불러온다.
- loop로 다음 파일까지 반복