Access VBA에서 쉽게 Excel 자료 다루기 - DoCmd.TransferSpreadsheet 메서드

작업을 하다 보면 엑셀 파일의 자료를 다뤄야 할 경우가 많이 있습니다.

특히 엑셀로 다운로드 받은 자료를 Access의 테이블에 insert 해야 하는 경우가 꽤 있습니다.

 

그럴 때 대부분 반복문으로 엑셀의 각 셀 데이터를 읽어 쿼리를 생성하고 쿼리문을 실행해서 데이터를 insert 하는 경우가 대부분일 겁니다.

오늘은 그럴때 유용한 방법을 소개해보겠습니다.

 

바로 DoCmd.TransferSpreadsheet 메서드입니다. 이 메서드는 엑셀의 자료를 바로 Access의 테이블로 만들어 줍니다.(테이블이 없으면 만들고, 있으면 자료를 추가합니다.)

 

 

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Test_table", "C:\Temp\Test.xlsx", True, "A1:Z100"

 

 

위 문장은 "C:\Temp\Test.xlsx" 파일의 "A1:Z100"데이터를 "Test_table"이라는 테이블로 만들어 줍니다.

참고 5번째 인자를 위의 예제와 같이 True로 주면 엑셀 파일의 1번째 행을 필드명으로 사용합니다.

 

이렇게 갖고 온 데이터를 쿼리를 이용하여 해당 테이블에 insert 하거나 update 할 수 있게 되는데,

특히 update를 하려면 프로그램에서 일일이 해당 값을 찾아 값을 수정하거나 각각의 update문을 생성하지 않고,

타깃 테이블과 만들어진 테이블과 join 하여 update 하면 쉽게 처리가 됩니다.

 

아래 예제를 참고하시기 바랍니다.

Private Sub cmdGetDelivery_Click()
        ''배송정보 수집
        Dim daoWK As DAO.Workspace
        Dim daoDB As DAO.Database
    
            
        Dim xlApp As Object
        Dim fd As Object     'File Dialog : 참조 >> Microsoft Office 16.0 Object Library
        Dim selectedItem As Variant
        
        Dim strQuery As String
        
        Set xlApp = CreateObject("Excel.Application")
        xlApp.Visible = False
        
        '1.파일선택창에서 파일 xlsx 파일 선택
        Set fd = xlApp.Application.FileDialog(msoFileDialogFilePicker)
        ' Clear out the current filters, and add our own.
        fd.Filters.Clear
        fd.Filters.Add "Excel", "*.xlsx"
        
        
        If fd.Show = -1 Then
        
            Set daoWK = DBEngine(0)
            Set daoDB = CurrentDb
            
            For Each selectedItem In fd.SelectedItems
                'Debug.Print selectedItem
                
                '2.선택된 엑셀 데이터를 Temp_Delivery 테이블로 만들기
                DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Temp_Delivery", selectedItem, True, "A1:BZ300"
                
                On Error GoTo trans_Err
            
                'Begin the transaction
                daoWK.BeginTrans
                
                '3.Temp_Delivery에 있는 데이터로 update하기
                strQuery = "UPDATE Order_main o"
                strQuery = strQuery & " INNER JOIN Temp_Delivery t"
                strQuery = strQuery & " ON o.상품주문번호=t.상품주문번호 "
                strQuery = strQuery & " SET o.배송완료일 = t.배송완료일"
                strQuery = strQuery & " , o.주문상태 = t.주문상태"
                strQuery = strQuery & " , o.발송처리일 = t.발송처리일"
                strQuery = strQuery & " , o.택배사 = t.택배사"
                strQuery = strQuery & " , o.송장번호 = t.송장번호"
                daoDB.Execute (strQuery)
                                
                'Commit the transaction
                daoWK.CommitTrans dbForceOSFlush
                
            Next
                
            Set fd = Nothing
            xlApp.Quit
            Set xlApp = Nothing
            
            MsgBox "성공적으로 작업이 완료 되었습니다."
        
        Else
            MsgBox "선택된 파일이 없습니다."
            Exit Sub
        End If
        
        
trans_Exit:
       
    'Clean up
    daoWK.Close
    Set daoDB = Nothing
    Set daoWK = Nothing
    
    Exit Sub

trans_Err:
    
    'Roll back the transaction
    daoWK.Rollback
    MsgBox "오류로 인해 작업이 완료 되지 않았습니다.", vbCritical
    
    Resume trans_Exit
    
End Sub

 

또한 반대로 테이블의 자료를 엑셀 파일로 만들어 줄 때도 쉽고 유용합니다.

 

1번째 매개변수를 acExport로 바꿔주고, 테이블명, 생성할 엑셀 파일명을 다음과 같이 적어주면 됩니다.

 

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "TempTableDef", "발주서.xlsx", True

 

 

오늘은 Access VBA에서 DoCmd.TransferSpreadsheet 메서드에 대해 살펴보았습니다.

엑셀 데이터를 읽어오면서 다른 작업을 함께 해야 한다면, 반복문을 사용하여 작업들을 코딩하여야 하겠지만, 단순히 가져오고 내보내는 작업이라면 아주 편하고 유용하게 쓰이는 기능으로 생각됩니다.

 

앞으로도 제 작업을 하다가 유용한 기능이 있으면 소개하겠습니다. 감사합니다.

 

 

 

참고!

예제를 실행하려면 Access 폼에 버튼(예제 에서는 버튼명이 cmdGetDelivery)을 추가한 후 VBA에 코드를 붙여넣어야 합니다. 

만약 모른다면 다음 예제를 참고 하세요.

 

파이썬 + MS Access : Access 에서 VBA 로 Python 실행하기