[엑셀/VBA] 다른 엑셀 파일에서 원하는 시트 가져오기

 

  이전 포스팅에서는 원하는 시트를 다른 엑셀 파일로 복사시키는 VBA에 대해 포스팅을 진행했다. 이번 편은 반대로 다른 엑셀파일에서 원하는 시트를 복사해서 가져오는 매크로를 작성해보려고 한다.

 

  프로그램을 테스트하기 위해 아래 가상의 예시파일을 만들었다. 1.xlsx, 2.xlsx, 3.xlsx는 시트를 가져올 엑셀파일들이고 BrigntSheet.xlsm은 매크로 동작파일이다.

 

파일 목록

 

  1.xlsx, 2.xlsx, 3.xlsx의 시트 구성은 숫자-A,B,C와 같은 구성으로 되어있다. 아래 이미지는 1번 파일의 시트명이다. 2번 3번 엑셀파일도 동일한 규칙으로 시트가 구성되어있다.

 

1.xlsx 시트 구성

 

  이번 포스팅의 목적은 내가 원하는 엑셀파일의 원하는 시트를 가져오는 VBA 코드를 작성해보는 것이다. 사실 이전편과 비교하여 Copy, Move 메서드를 사용하는 것은 동일하므로, 사용법에 대해서 참고링크로 대체한다.

(이전 포스팅에서 정리했으므로 생략)

 

참고링크 : 2021.07.09 - [코딩/엑셀,VBA] - [엑셀/VBA] 원하는 시트를 다른 엑셀 파일로 복사하기

 

 

 

1. 기본 코드 및 설명

 

  먼저 전체 코드 및 실행 결과를 확인해보자.

 

 

<전체 코드>

Sub 시트가져오기()

    Dim fileName As String
    Dim filePath As String
    Dim shtName As String
    
    '시트를 가져올 파일명
    file = ActiveSheet.Cells(2, 1).Value
    
    '가져올 시트명
    shtName = ActiveSheet.Cells(2, 2).Value
    
    '가져올 엑셀파일의 경로
    filePath = ThisWorkbook.Path + "\" + file
    
    '해당 파일 열기
    Workbooks.Open fileName:=filePath
    
    '열린 파일에서 원하는 시트 가져 오기
    ActiveWorkbook.Sheets(shtName).Copy Before:=ThisWorkbook.Sheets(1)
    
    '열린 파일 닫기
    Workbooks(file).Close
    
    

End Sub

 

<VBA 코드 작성>

 

 

 

<결과>

1.xlsx 파일의 "1-B"' 시트 가져오기

 

 

코드에 대한 설명은 기능적으로 구분하여 3가지로 설명해보도록 하겠다.

 

 

1) 읽어올 파일명 및 시트명 읽어오기

 

 

<코드>

'시트를 가져올 파일명
file = ActiveSheet.Cells(2, 1).Value
    
'가져올 시트명
shtName = ActiveSheet.Cells(2, 2).Value

 

 

  위 코드는 엑셀의 활성화 시트("가져오기" 시트)의 값을 문자열로 읽어오는 부분이다.

 

  ActiveSheet란 현재 실행 중인 엑셀파일의 활성화 된 시트를 의미한다. 위 이미지에서 보면 "가져오기" 시트이다. 이 부분을 Sheet1을 나타내는 Sheets(1)이나 Sheets("가져오기")라고 사용해도 동일하다. 코드가 복잡해지는 경우 ActiveSheet보다는 시트 번호나 시트명을 지정해주는게 버그를 방지하는데 좋을 것 같다.

 

  Cells(2,1)은 Range("A2")와 동일하다. Cell과 Range에 대해서는 아래 포스팅에서 정리했으니 링크를 참고하면 된다.

(참고링크 : 2021.06.08 - [코딩/엑셀,VBA] - [엑셀/VBA] 엑셀 시트 마지막 행값 구하기)

 

  

 

2) 가져올 시트의 엑셀파일을 열기

 

 

<코드>

'가져올 엑셀파일의 경로
filePath = ThisWorkbook.Path + "\" + file
    
'해당 파일 열기
Workbooks.Open fileName:=filePath

 

  Thisworkbook은 Macro가 동작하는 파일을 의미하는 개체이다. 즉 ThisWorkbook.path는 매크로가 동작하는 "BringSheet.xlsm" 파일의 경로이다. file은 1)에서 받아온 파일명 문자열이다.

 

  ThisWorkbook이랑 비슷한 것으로 ActiveWorkbook이 있는데 차이점은 아래를 참고해보자.

 

개체	설명
ThisWorkbook	-. VBA 코드(매크로)가 동작하는 파일 개체
ActiveWorkbook	-. 현재 활성화 되어있는 엑셀 파일 개체

 

  액셀파일이 1개만 실행되어있는 경우 ThisWorkbook = ActiveWorkbook이지만, 다수 엑셀파일이 실행 된 경우는 다를 수 있다.

 Workbooks.Open fileName:=filePath은 filePath로 지정한 경로 엑셀파일을 여는 메서드이다.

 

 

 

3) 실행 한 엑셀파일에서 원하는 시트 가져오기

 

 

<코드>

 '열린 파일에서 원하는 시트 가져 오기
 ActiveWorkbook.Sheets(shtName).Copy Before:=ThisWorkbook.Sheets(1)
    
 '열린 파일 닫기
 Workbooks(file).Close

 

 

ActiveWorkbook.sheets(shtName).Copy Before:=ThisWorkbook.Sheets(1)

: ActiveWorkbook과 ThisWorkbook의 차이는 위에서 정리했으니 설명은 생략한다. Copy 메서드는 이전 포스팅에서 정리했던 부분이다. 위 코드대로 설명하면 활성화된 Workbook에서 정해진 Sht를 복사해서 VBA가붙여 실행 중인 본 엑셀파일에 붙여넣는다라는 의미이다.

 

 

 

2. 응용 : 여러 엑셀 파일의 원하는 시트를 한번에 가져오기

 

 

  아래처럼 여러 엑셀파일과 시트명으로 구성 된 리스트가 있다고 하자. 이번엔 위 코드를 조금 수정하여 아래 엑셀시트를 한번에 가져오는 코드를 작성해볼 것이다.

 

<가져올 시트목록>

 

 

<전체 코드>

Sub 시트가져오기()

    Dim fileName As String
    Dim filePath As String
    Dim shtName As String
    Dim LastRow As Integer
    
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To LastRow
        '시트를 가져올 파일명
        file = Sheets("가져오기").Cells(i, 1).Value
        
        '가져올 시트명
        shtName = Sheets("가져오기").Cells(i, 2).Value
        
        '가져올 엑셀파일의 경로
        filePath = ThisWorkbook.Path + "\" + file
        
        '해당 파일 열기
        Workbooks.Open fileName:=filePath
        
        '열린 파일에서 원하는 시트 가져 오기
        ActiveWorkbook.Sheets(shtName).Copy Before:=ThisWorkbook.Sheets(1)
        
        '열린 파일 닫기
        Workbooks(file).Close
    Next i

End Sub

 

 

  이전 코드랑 차이점은 For문이 추가되고 파일명 변수 file고 Sheet명 변수 ShtName의 Cells 행값을 i로 치환했다는 것이다. LastRow는 해당 시트의 마지막 행값을 구하는 변수로 아래 포스팅에서 정리한바가 있어 참고링크를 기재한다.

(참고링크 : 2021.06.08 - [코딩/엑셀,VBA] - [엑셀/VBA] 엑셀 시트 마지막 행값 구하기)

 

  실행결과는 아래와 같다.

 

<결과>

원하는 시트 가져오기 실행

 

프로그램 실행 후 아래 시트목록에 원하는 시트들이 추가된 것을 확인할 수 있다.

---

참고링크

https://steemit.com/kr-newbie/@gbgg/excel-2

[Excel] 2. 능력있는 직장인이 되는 법. 다른 파일 불러오기! — Steemit