[엑셀/VBA] 엑셀 다른 파일에서 데이터 가져오기 : Getobject 함수 사용하기

 

  이번 내용은 다른 폴더(또는 같은 폴더)에 있는 다른 엑셀 파일에서 필요한 데이터를 가져오려면 어떻게 해야하되는지에 대해 정리한 내용이다.  

 

  이전에는 '같은 엑셀 파일에서 다른 시트의 데이터를 가져오려면 어떻게 해야하는가'?를 정리했었다. 이번 포스팅을 잘 사용하면 여러 엑셀파일과 데이터를 연동하여 VBA 프로그램을 만들 수 있다.

(이전 포스팅 : 2021.08.16 - [코딩/엑셀,VBA] - [엑셀/VBA] 엑셀 시트별 모든 데이터 통합하기(합치기))

 

  엑셀 VBA에서 다른 엑셀 파일에 접근하기 위한 방법은 GetObject라는 함수를 사용하는 것이다. 엑셀 VBA를 공부해본 사람이라면 알겠지만 엑셀에는 Workbook이라는 개체가 존재한다. Workbook이라고 하면 "ActiveWorkbook'이나 "ThisWorkbook"이라는 개체를 예로 들 수 있다. "ActiveWorkbook"은 현재 활성화되어있는 엑셀파일, "ThisWorkbook"은 현재 매크로(VBA)가 실행되고 있는 파일을 의미하는 개체명이다. 이와 동급의 개체로 엑셀에는 GetObject라는 함수가 있는 것이다. 아래 내용을 보며 이해해보도록 하자.

 

  먼저 예시를 들기 위해 "Sample_Data.xlsx"라는 엑셀 파일을 만들었다. 이 엑셀파일은 VBA가 동작하는 파일은 아니다. VBA가 동작하는 파일에서 "Sample_Data.xlsx"라는 파일에 접근해서 데이터를 가져오는 컨셉의 코드를 작성할 것이다.

 

 

Concept1. 데이터를 가져올 파일명 : Sample_Data.xlsx

 

 

 

 

 

 

"Sample.Data.xlsx" 파일의 내부 데이터는 아래와 같다. "데이터"라는 이름의 시트에 지출내역부가 작성되어 있다.

 

"Sample_Data.xlsx"의 "데이터" Sheet

 

Concept2. 매크로(VBA)를 동작시킬 파일명 : "test.xlsm:"

 

VBA 코드 작성 파일 : "test.xlsm"

 

 

  "test.xlsm"에서 위 Concept1에서 설명했던 Sample 폴더 내부 파일 "Sample_Data.xlsx"의 데이터를 가져오는 코드를 작성해볼 것이다.

 

  일단 전체코드 및 결과를 확인해보도록 하자. 

 

 

<전체코드>

Option Explicit '명시적 선언

Sub 데이터가져오기()
  
  Dim Fileobj As Object '엑셀파일 Object를 생성하기 위한 개체 변수
  Dim Path As String '불러올 파일 경로를 저장할 String 변수
  Dim FileName As String '파일명을 저장할 String 변수
  Dim sht As Worksheet 'Worksheet 설정을 위한 변수
  
    
  '"Sample_Data.xlsx"의 파일경로 및 파일명 변수에 저장
  Path = "C:\Users\Desktop\VBA CODE\Project8 - 다른 파일에서 데이터 가져오는 방법\Sample\"
  FileName = "Sample_Data.xlsx"
  
  'GetObject 함수를 통해 엑셀파일 개체 생성   
  Set Fileobj = GetObject(Path + FileName)
  
  '위 엑셀파일 개체의 활성화된 시트를 sht 변수에 저장
  Set sht = Fileobj.ActiveSheet
  
  'sht에 접근하기
  With sht
  
    .UsedRange.Copy '각 DATA 시트의 데이터를 모두 복사
    Sheets(1).Cells(1, 1).PasteSpecial xlPasteValues '현재 매크로 실행되는 시트에 붙여넣기
    
  End With

    
End Sub

 

<결과>

"Sample_Data.xlsx"에서 데이터 불러오기

 

 

  사실 이전 포스팅에서 설명했던 코드와 크게 다른 것이 없다. GetObject 라는 함수를 사용했다는 내용이 큰 차이점이다. 위 결과 이미지는 다른 파일에서 데이터를 불러온 결과를 출력해본 것이다.

 

  GetObject 함수를 사용할 때는 아래 2줄 코드만 기억하면 된다.

 

 

<코드>

Dim FileObj as Object #반드시 Object 타입으로 개체를 선언해야 함.

Set FileObj = GetObject("파일경로+파일명") #반드시 Object 설정시 Set 붙일 것

 

 

  첫번째는 GetObject 함수를 통해 개체를 설정할 변수를 Object 타입으로 설정하자. 위 예시에서 디버깅을 통해 FileObject 가 어떤 데이터 타입을 가지고 있는지 확인해보면 아래 이미지와 같이 Object 타입으로 확인된다.

 

 

<FileObj 변수의 타입>

 

 

  두번째는, FileObj에 GetObject 함수를 통해 개체 설정을 할 때, Set이라는 구문을 설정해야 한다. 엑셀 VBA에는 Range나 개체 설정시 반드시 Set 이라는 구문을 사용해야 한다. 만약 Set을 빼고 실행하면 '91' 런타임 오류가 발생한다. 자세한 내용을 보면 개체 변수가 설정되지 않았다는 내용이다. 즉, 개체 변수를 설정하기 위해선 반드시 Set을 변수 설정문 앞에 입력하도록 하자.

 

 

Set을 뺄 경우 발생하는 에러

 

 

  그럼 위 코드를 응용해서 "Sample_Data.xlsx" 파일의 아래 영역(A5:C7)의 데이터만 가져오려면 어떻게 해야될까?? 어려운 것이 없다. 위 코드에서 with 문 안에 UsedRange를 Range("A5:C7")이라고 수정하면 된다.

 

  아래 영역의 코드를 수정해서 다시 실행해보도록 하겠다.

 

<변경 전 코드>

 #sht에 접근하기
  With sht
  
    .UsedRange.Copy #각 DATA 시트의 데이터를 모두 복사
    Sheets(1).Cells(1, 1).PasteSpecial xlPasteValues #현재 매크로 실행되는 시트에 붙여넣기
    
  End With

 

<수정 후>

 #sht에 접근하기
  With sht
  
    .Range("A5:C7").Copy #각 DATA 시트의 데이터를 모두 복사
    Sheets(1).Cells(1, 1).PasteSpecial xlPasteValues #현재 매크로 실행되는 시트에 붙여넣기
    
  End With

 

<결과>

A5:C7 영역 데이터만 가져오기