[VBA] 다른 엑셀파일 내용 가져오기

외부 엑셀파일에서 자료를 가져오는 방법이다.

서식까지 그대로 가져오는 방법과 SQL 을 이용하여 가져오는 방법이다.

A1 셀에서 값을 a, b 로 다르게 적어주면 가져오는 파일의 내용이 달라진다.

Excel_Get.zip

Sub ExcelFile_Get()
    Dim getFile As Object
    Dim FilePath As String          '// 파일 경로 변수
    Dim FileName As String         '// 가져올 엑셀 파일명 변수
   
    On Error Resume Next
    FilePath = ThisWorkbook.Path + "\"  '// 현재 파일 경로
   
    If [A1].Text = "a" Then
        FileName = "Asample.xlsx"      '// 다른 엑셀파일
    ElseIf [A1].Text = "b" Then
        FileName = "Bsample.xlsx"      '// 다른 엑셀파일
    Else
        MsgBox " 가져올 파일을 선택하지 않았습니다"
        Exit Sub
    End If
   
    Set getFile = GetObject(FilePath & FileName)
    With ActiveWorkbook.ActiveSheet.Range("A5")    '// A5열부터 데이터 출력
        .CurrentRegion.Clear    '// 현재 존재하는 값을 전부 삭제
        getFile.Sheets([A2].Text).UsedRange.Copy [A5]
        getFile.Close False
    End With
    Set getFile = Nothing
End Sub

또다른 방법은 데이터베이스처럼 가져오는 방법이다.

이 경우에는 서식유지까지는 안된다.

Sub ExcelFileData_Get()
'// This sub will pull data from an external .xlsx file.
'// The ADO 6.0 object library reference must be loaded.
    Dim conn As Object     '// 연결변수 선언
    Dim RS As Object
    Dim strSQL As String              '// SQL 문을 위한 변수
    Dim FilePath As String          '// 파일 경로 변수
    Dim FileName As String         '// 가져올 엑셀 파일명 변수
    Dim i As Long
   
    FilePath = ThisWorkbook.Path + "\"  '// 현재 파일 경로
    If [A1].Text = "a" Then
        FileName = "Asample.xlsx"      '// 다른 엑셀파일
    ElseIf [A1].Text = "b" Then
        FileName = "Bsample.xlsx"      '// 다른 엑셀파일
    Else
'        FileName = ActiveWorkbook.Name      '// 같은 엑셀파일(현재 엑셀화면에 활성화된 파일)
        MsgBox " 가져올 데이터를 선택하지 않았습니다"
        Exit Sub
    End If
  
    Set RS = CreateObject("ADODB.Recordset")
    Set conn = CreateObject("ADODB.Connection")
   
    With conn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & FilePath & FileName & ";" & _
            "Extended Properties=Excel 12.0;"
        .Open
    End With
   
    strSQL = "SELECT * FROM [Data$] "           '// 엑셀시트이면 뒤에 $ 를 붙인다. Data Sheet 가 존재해야 한다.
   
    Set RS = conn.Execute(strSQL)
   
    With ActiveWorkbook.ActiveSheet.Range("A5")    '// A5열부터 데이터 출력
        .CurrentRegion.Clear    '// 현재 존재하는 값을 전부 삭제
       
        For i = 0 To RS.Fields.Count - 1    '// 레코드셋 제목 전부 가져오기
            .Offset(0, i).Value = RS.Fields(i).Name
        Next i
       
        .Offset(1, 0).CopyFromRecordset RS
       
    End With
   
    RS.Close
    conn.Close
    Set RS = Nothing
    Set conn = Nothing
   
End Sub