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

안녕하세요! @gbgg 입니다. 능력있는 직장인이 되는법! 엑셀 자동화 강의 두번째입니다~~ 와~~

엑셀로 일 편하게 하는 방법을 여러 차례에 걸쳐 포스팅해보고자 합니다.

이번 포스팅에서는 다른 파일에 있는 시트를 불러오도록 해보겠습니다!

일을 더 편하게 하기위해선 이런 기능도 필요하겠죠?

파일 불러올 때 파일명을 코드가 아닌 셀의 내용을 참조하여 불러오기
(같은 폴더에 있는 파일)
확장자 선택하기
가져오고 싶은 시트명 고르기

자 이 그림처럼 파일명과 확장자, 시트명을 적으면 해당 파일 시트를 불러오는

작업을 버튼 하나로만 할 수 있게 해보겠습니다!

어렵지 않으니 천천히 따라오세요! 아 참 이렇게 매크로가 저장된 파일을 사용하려면 저장할 때

확장자를 다음과 같이 바꿔야 하는데요. 간단하게 설명드릴게요!

엑셀에서 매크로나 비주얼베이직을 사용하려고 하면 위 그림과 같이 확장자를 바꿔주셔야 합니다.

Excel 매크로 사용 통합문서 (.xlsm)
Excel 바이너리 통합 문서 (.xlsb)

기본적으로 사용하시려면 1번 .xlsm을 선택하시면 되는데 아무래도 업무용으로 사용하다보면 데이터가 쌓이고 쌓여서 느려지는 현상이 발생합니다. 이럴때는 바이너리 통합문서로 저장하시면 용량도 대폭 감소하고 그만큼 처리 속도도 많이 증가하게 되니 참고하세요!

기본 셋팅

다음과 같이 엑셀파일 A를 새로만든 뒤 각 셀을 아래처럼 꾸밉니다.

자료가 들어있는 엑셀파일 B를 새로 생성하겠습니다. 다음과 같이 내용을 쓰고 시트명을 1번시트 로 변경합니다.

엑셀파일B의 파일명은 스팀잇 불러오기 테스트 로 하겠습니다.

버튼 및 코드생성

아래와 같이 버튼을 생성합니다.

다음과 같이 모듈을 생성해줍니다.

Sub 시트불러오기()

    Dim shtName, shtEx As String

    Dim strPath As String
    Dim strFile As String
    Dim sh As Worksheet
    Dim shExist As Boolean
    
    shtName = Sheets("sheet1").Cells(2, 2).Value + Sheets("sheet1").Cells(2, 3).Value
    shtEx = Sheets("sheet1").Cells(2, 4).Value
    
    strPath = ThisWorkbook.Path + "\"
    strFile = strPath & shtName
    
    Workbooks.Open Filename:=strFile
    
    shExist = False
    For Each sh In ThisWorkbook.Worksheets
        If (sh.Name = shtEx) Then
            shExist = True
            Exit For
        End If
    Next
    
    '시트가 있으면
    If (shExist = True) Then
        ThisWorkbook.Sheets(shtEx).Delete
    End If
    Workbooks(shtName).Worksheets(shtEx).Copy Before:=ThisWorkbook.Sheets(1)
    Workbooks(shtName).Close

End Sub

흐아... 갑자기 코드가 길어졌네요.. 난이도 조절에 실패했나봅니다... ㅠㅠ

자 이 코드를 다음 그림처럼 붙여넣으시면 됩니다.

버튼에 능력 부여하기

코드를 다 입력하셨으면 다음과 같이 버튼에 능력을 부여해줍시다!

결과

짜잔! 기존에 있던 sheet1 앞에 새로운 시트가 생성되었네요!

스팀잇 불러오기 테스트.xlsx 파일에 있는 1번시트 가 통채로 복사되어왔습니다!

다른 파일을 직접 열어 시트를 복사하지 않아도 이렇게 간단하게 버튼만으로 옮겨올 수 있습니다.

계속해서 바뀌는 파일명, 시트명도 손쉽게 변경하여 불러올 수 있고

지금은 한 파일의 시트만 불러왔지만 조금만 응용하면 수십개의 파일에 있는
각 시트들을 원하는 대로 불러올 수 있다는 사실!

지난시간에 배운 다른 시트의 셀 값을 복사해오는 방법과 같이 융합하면

특정 셀의 값만 또 빼낼 수 있습니다.

코드분석

shtName = Sheets("sheet1").Cells(2, 2).Value + Sheets("sheet1").Cells(2, 3).Value
shtEx = Sheets("sheet1").Cells(2, 4).Value

셀에 작성한 파일명과 확장자, 시트명을 참조하기 위해
shtName 변수에는 파일명 + 확장자명의 셀 값을 저장하였고
shtEx 변수에는 시트명의 셀 값을 저장하였습니다.

'시트가 있으면
    If (shExist = True) Then
        ThisWorkbook.Sheets(shtEx).Delete
    End If
    Workbooks(shtName).Worksheets(shtEx).Copy Before:=ThisWorkbook.Sheets(1)

ThisWorkbook.Sheets(shtEx).Delete 이 부분은 만약 기존에 불러온 시트가 남아있을 경우 그 시트를 닫는 역할을 수행합니다. 이 작업을 수행하지 않으면 새로 불러온 시트와 기존 시트가 충돌하게 됩니다!

Copy Before:=ThisWorkbook.Sheets(1) 이 코드에 있는 숫자 1은 불러온 시트를 현재 있는 시트에서 어떤 위치에 넣을 것인지 작성해 주시면 됩니다.
단, 이런상황에서 맨 오른쪽에 시트를 생성하고 싶으면

3을 쓰면 에러가 납니다. 반드시 생성될 시트의 위치는 각 시트의 사이 값이 되어야 합니다.

이렇게 임의로 시트를 생성해두고 3을 쓰면 에러가 안나겠죠?