엑셀에서 정리한 데이터를 Access DB에 쌓아야 할 때, 매번 Access를 열고 외부 데이터 가져오기 마법사를 돌리는 건 번거롭습니다. 엑셀 쪽 버튼 하나로 끝내고 싶다면 엑셀 VBA에서 Access를 자동화 개체로 띄워서 Access의 가져오기 명령을 대신 실행시키면 됩니다.

Sub program1472()
    Dim acc As Object
    Set acc = CreateObject("Access.Application")
    acc.OpenCurrentDatabase ThisWorkbook.Path & "\DB.accdb"
    acc.DoCmd.TransferSpreadsheet _
            TransferType:=0, _
            SpreadSheetType:=acSpreadsheetTypeExcel12Xml, _
            TableName:="program1472", _
            Filename:=Application.ActiveWorkbook.FullName, _
            HasFieldNames:=True, _
            Range:="tem$A1:B10"
    acc.CloseCurrentDatabase
    acc.Quit
    Set acc = Nothing
End Sub

코드가 하는 일

CreateObject("Access.Application") 으로 보이지 않는 Access를 하나 띄우고, 엑셀 파일과 같은 폴더에 있는 DB.accdb 를 엽니다. 그다음 Access의 DoCmd.TransferSpreadsheet 를 호출하는데, 이게 Access 메뉴의 "엑셀 가져오기"와 같은 기능입니다.

광고

인수를 하나씩 보면, TransferType:=0 은 가져오기(acImport)입니다. 엑셀 입장에서는 내보내기지만 명령을 실행하는 주체가 Access라서 "가져오기"가 됩니다. TableName 은 데이터를 넣을 Access 테이블 이름이고, 테이블이 이미 있으면 그 뒤에 행이 추가됩니다. Filename 에는 지금 열려 있는 통합 문서의 전체 경로를 넘겼고, HasFieldNames:=True 는 범위의 첫 행을 필드 이름으로 쓰겠다는 뜻입니다. Range 로 시트 tem 의 A1:B10만 지정해서 그 영역만 넘어갑니다. 작업이 끝나면 DB를 닫고 Access를 종료합니다.

늦은 바인딩에서 상수가 비는 문제

이 코드는 Access 참조를 추가하지 않고 As Object 로 만든 늦은 바인딩 방식입니다. 이 경우 엑셀 VBA는 acSpreadsheetTypeExcel12Xml 이라는 상수를 모릅니다. 모듈 위에 Option Explicit 가 있으면 "변수가 정의되지 않았습니다" 오류로 멈추고, 없으면 빈 값(0)으로 넘어가서 의도와 다른 엑셀 형식으로 처리됩니다.

해결 방법은 둘 중 하나입니다. VBA 편집기의 도구 → 참조에서 Microsoft Access Object Library를 체크하거나, 상수 대신 숫자를 직접 넣습니다. acSpreadsheetTypeExcel12Xml(.xlsx 형식)의 값은 10이니 SpreadSheetType:=10 으로 쓰면 참조 없이도 정확히 동작합니다. 배포할 파일이라면 PC마다 Office 버전이 달라 참조가 깨질 수 있으니 숫자로 쓰는 쪽이 낫습니다.

돌리기 전에 확인할 것

Access는 디스크에 저장된 파일을 읽습니다. 엑셀에서 방금 고친 내용이 저장되지 않았다면 그 변경분은 넘어가지 않으니, 실행 전에 ThisWorkbook.Save 를 한 줄 넣어 두는 게 안전합니다. 또 Access가 설치되어 있지 않은 PC에서는 CreateObject 에서 오류가 나고, DB.accdb 가 다른 사람에 의해 열려 있으면 잠금 때문에 실패할 수 있습니다.

같은 명령에서 TransferType 을 1(acExport)로 바꾸면 반대로 Access 테이블을 엑셀 파일로 내보낼 수 있습니다. 범위를 고정값 대신 "tem$A1:B" & 마지막행 처럼 만들어 넘기면 데이터 양이 바뀌어도 대응할 수 있고요.