엑셀(Excel)의 VBA로 엑세스(Access)의 *.mdb 파일 불러오기

컴퓨터/VBA(Visual Basic for Application)

엑셀(Excel)의 VBA로 엑세스(Access)의 *.mdb 파일 불러오기

아우쿠스 2012. 7. 10. 09:10

아우쿠스입니다.

최근 회사 업무를 하던 중, 사용할 일이 있어서, 만들어본 코드입니다.

물론, 엑셀 자체 내에서도 불러오는 기능은 가능합니다만, 저는 편의상 VBA를 사용하여 작성 하였습니다.

물론, 고수분들이야 저보다 깔끔하고 간편하게 만드시겠지만, 저는 실력이 미천하여,

그렇질 못하니 감안하고 봐주시길..

임의의 UserForm을 만들었습니다.

참, 썰렁하기 그지 없군요...;ㅁ;

폴더 선택기는 FileDialog 명령을 사용하여 구현 하였습니다.

Private Sub FolderSet_Click()

    strDir2 = strDir
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Show
        If .SelectedItems.Count = 0 Then
            Else
            strDir = .SelectedItems(1)
        End If
    End With


    If (strDir <> "") Then
        ChDrive (Left(strDir, 1))
        ChDir (strDir)
        Else
            strDir = strDir2
    End If

    If (Len(strDir) > 42) Then
        Fold1.Text = Left(strDir, 10) & "...." & Right(strDir, 30)
        Else
            Fold1.Text = strDir
    End If

End Sub

요런, 현태가 되는데...중점 코드는 붉은색 부분입니다.

그 아래에 if (strDir <> "") Then 으로 시작하는 구분은 폴더선택기에서 선택한 폴더를 엑셀에 적용하는 코드(파랑).

보라색으로 표시된 부분은 TextBox에 뿌려주는 전체 경로가 특정 글자 수 이상일 경우, 중간 부분을 생략해서,

보여주게끔 하는 부분입니다. (없어도 무관, 그냥 보기 좋으라고......)

요녀석이 폴더를 선택하기 위한 창

"취소 / 종료" 버튼은 말 그대로 UserForm의 종료 시에 사용됩니다.

Private Sub BtnCancel_Click()

Unload AccesstoExcel

End Sub

위의 아주 단순한 코드가 "취소 / 종료" 버튼의 실제 명령입니다.

그런데, 저렇게만 해두면, UserForm이 그냥 닫혀 버립니다. 실수로 눌렀다고 해도 짤없이 닫아 줍니다.

그래서, 확인 창을 띄워보고자 아래와 같은 코드를 추가합니다.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    Dim Msg As String
    Dim Answer As Integer

    If CloseMode = 0 Then
        Msg = "정말 종료 하시겠습니까?"
        Answer = MsgBox(Msg, vbYesNo)
        Else
            Msg = "정말 종료 하시겠습니까?"
            Answer = MsgBox(Msg, vbYesNo)
    End If

    If (Answer = vbYes) Then
        Unload AccesstoExcel
        Else
            Cancel = True
            Exit Sub
    End If
End Sub

위 코드는 "취소 / 종료" 버튼을 눌러서, Unload UserForm이 실행될 경우의 조건적 실행이 되는 함수입니다.

Unload 명령이 실행되면, 종료되기 전에 중간에 끼어들어서 "너 정말 종료 할거야?" 라고 물어보는 기능이지요.

자, 그러면 이제 "시작" 버튼으로 가봅시다.

Private Sub BtnOK_Click()

    Dim wrkBook As Workbook
    Dim TempFile As String
    Dim ArrangeFile As String

    Dim File(30000) As String
    Dim OCV(40) As Single
    Dim ChNo As Integer
    Dim i As Integer
    Dim P As Single
    Dim Fcnt As Integer

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error Resume Next

    MDNo = 1

    For i = 1 To 20
        OCV(i) = 0
    Next i

    Workbooks.Add

    strFile = Dir("")
    FileNo = 0
    Do While strFile <> ""
        FileNo = FileNo + 1
        strFile = Dir()
    Loop
    Fcnt = FileNo
    FileNo = 0
    strFile = Dir("")

    Do While strFile <> ""
        FileNo = FileNo + 1
        File(FileNo) = strFile

        If (UCase(Right(File(FileNo), 4)) = "XLSX" Or UCase(Right(File(FileNo), 4)) = ".XLS") Then GoTo bb

        Workbooks.Add
        TempFile = strFie

        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
            "ODBC;DSN=MS Access Database;DBQ=" & strDir & "\" & strFile & ";DefaultDir=StrDir;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTim" _
            ), Array("eout=5;")), Destination:=Range("$A$1")).QueryTable
            .CommandText = Array( _
            "SELECT Channel_Normal_Table.Test_ID, Channel_Normal_Table.Data_Point, Channel_Normal_Table.Voltage" & Chr(13) & "" & Chr(10) & "FROM `" & strDir & "\" & strFile & "`.Channel_Normal_Table Channel_Normal_Table" _
            )
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = "표_MS_Access_Database_Query"
            .Refresh BackgroundQuery:=False
        End With
    Loop
End Sub

코드를 보실 때, 불필요한 함수 지정 및 불필요한 코드들은 그냥 무시하시기 바랍니다.

사실 이 코드의 제작 의도는, 회사에서 mdb파일을 오픈하여, 해당 파일내에서 원하는 데이터들을 뽑아내고,

그걸, 별도의 엑셀 파일에 취합, 정리하고자 하는 것이 목적이었기 때문에, 일부 그에 따른 코드들이 남아있으므로,

붉은색 표시 부분만 참고하시기 바랍니다.

붉은색에서도 살짝 보기 어려우니, 구분을 지어보겠습니다.

자, 위의 코드 중 붉은색 표시 부분은 각각 strDir은 경로명, strFile은 파일명을 담고 있는 변수 입니다.

파랑색 표시 부분은 데이터 필드명입니다. (자료의 종류에 따라 달라지는 부분이겠죠.)

사용 방법에 대해서 참고하시기 바라고, 파란색으로 표시된 부분은, 불러오고자 하는 엑세스 파일 내의

데이터 필드입니다.

여기까지, 혹시나 궁금하신 점이 있으시면 댓글로...

추신: 본, 내용은 본인의 기억력 한계로 인한 정보 백업 및 정보 공유를 위한 글 이므로, 무한히 허접할 수 있으므로,

참고 바랍니다. 조언도 감사히 받습니다 :)

저작자표시 비영리