파일 내의 모든 시트 내보내기

Step 1: 예제 시트 만들기

일단 예제 데이터를 만들어야 겠군요. 수십 개나 되는 시트를 일일이 손으로 만들려면 귀찮고 힘이 드니까 이것도 자동으로 만들어 보도록 하지요. 아래 버튼을 누르세요.

순식간에 20개의 시트가 만들어 졌지요? 그냥 시트만 만들어 진 것이 아니라 지점별/분기별 실적 데이터도 들어가 있을 것입니다. 물론 가상의 데이터이긴 하지만… 수작업으로 만들려면 이것도 힘이 들겠지요?

예제 시트를 만드는 코드를 간단히 보면…

Sub MakeSheets()
    Dim shtSheet As Worksheet
    Dim varQuarter As Variant
    Dim varBranch As Variant
    Dim i As Integer
    Dim intNum As Integer
    varQuarter = Array("1/4분기", "2/4분기", "3/4분기", "4/4분기")
    varBranch = Array("강동", "강서", "강남", "강북")
    intNum = 20
    '''변수를 선언하고 값을 대입(Assign)하는 부분은 여느 때와 같습니다.
    '''다만 varQuarter와 varBranch 변수에는 배열값을 담아 둔 것이 조금 다르군요.

    For i = 1 To intNum
        Worksheets.Add after:=Sheets(1)
        [B1:E1] = varQuarter
        [A2:A5] = Application.WorksheetFunction.Transpose(varBranch)
        '''시트를 삽입하고… 그 때마다 표의 타이틀 부분을 입력합니다.
        '''여기서 한 가지 주의하실 점! 가로 방향으로 배열을 입력할 때는 그냥 사용을
        '''하면 되지만 세로 방향으로 배열값을 넣어 주기 위해서 Transpose 함수를
        '''이용해 준 점을 눈여겨 보시기 바랍니다.

        With [B2:E5]
            .FormulaR1C1 = "=INT(RAND()*1000)"
            .Copy
            .PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
            .Cells(1).Select
        End With
    Next i
    MsgBox intNum & "개의 시트를 만들었습니다", , "시트 생성//By Exceller"
End Sub

변수를 선언하고 값을 대입(Assign)하는 부분은 여느 때와 같습니다. 다만 varQuarter와 varBranch 변수에는 배열값을 담아 둔 것이 조금 다르군요. 시트를 삽입하고… 그 때마다 표의 타이틀 부분을 입력합니다. 여기서 한 가지 주의하실 점! 가로 방향으로 배열을 입력할 때는 그냥 사용을 하면 되지만 세로 방향으로 배열값을 넣어 주기 위해서 Transpose 함수를 이용해 준 점을 눈여겨 보시기 바랍니다.

Step 2: 예제 시트 지우기

음지가 있으면 양지가 있듯이 만드는 코드가 있다면 지우는 코드도 필요하겠지요? 버튼을 눌러 보세요.

역시 마찬가지로 순식간에 예제 시트가 샥~ 지워졌지요? 이래도 VBA를 안 배운다고 버팅기는 분이 계실려나? Exceller가 자꾸 권할 때 못 이기는 척 하고 빨리 따라 오시기 바랍니다.

지우는 코드는 더 간단하군요. 별로 설명이 필요없을 듯…

Sub DelSheets()
    Dim shtSheet As Worksheet
    Dim strSheetName As String
    Dim i As Integer
    Dim j As Integer

    For j = ThisWorkbook.Sheets.Count To 1 Step -1
        Set shtSheet = ThisWorkbook.Sheets(j)
        strSheetName = Left(shtSheet.Name, 5)
        If strSheetName = "Sheet" Then
            Application.DisplayAlerts = False
            shtSheet.Delete
            Application.DisplayAlerts = True
            i = i + 1
        End If
    Next j
    MsgBox i & "개의 시트를 삭제하였습니다", , "시트 삭제//By Exceller"
End Sub

Step 3: 시트를 텍스트 파일로 내보내기

이제 예제 시트를 별도의 텍스트 파일로 만들어 보도록 하지요. 아래의 버튼을 눌러 보세요.

Sub ExportSheets()
    Dim rngOut As Range
    Dim lngFirstRow As Long, lngLastRow As Long
    Dim intFirstCol As Integer, intLastCol As Integer
    Dim i As Integer
    Dim r As Long
    Dim c As Integer
    Dim varData As Variant
    Dim Msg As String
    Dim shtSheet As Worksheet
    Dim shtTarget As Worksheet
    Dim intSheetNum As Integer
    On Error GoTo ET
    intSheetNum = Sheets.Count
    If intSheetNum = 1 Then
        MakeSheets
        intSheetNum = Sheets.Count
    End If

    For i = 2 To intSheetNum
        Set shtTarget = Sheets(i)
        Application.Goto shtTarget.[A1], True
        Set rngOut = ActiveCell.CurrentRegion

        lngFirstRow = rngOut.Rows(1).Row
        lngLastRow = lngFirstRow + rngOut.Rows.Count - 1
        intFirstCol = rngOut.Columns(1).Column
        intLastCol = intFirstCol + rngOut.Columns.Count - 1
        Open ThisWorkbook.Path & Application.PathSeparator & "Text" & i & ".txt" For Output As #1

        For r = lngFirstRow To lngLastRow
            For c = intFirstCol To intLastCol
                varData = Cells(r, c).Value
                If IsNumeric(varData) Then varData = Val(varData)
                If c <> intLastCol Then
                    Write #1, varData;
                Else
                    Write #1, varData
                End If
            Next c
        Next r
        Close #1
    Next i
    MsgBox "Exporting 작업을 완료하였습니다", , "Exporting 완료//By Exceller"
ET:
End Sub

코드에 대한 해설은 다음 강좌로 미루어야 할 것 같습니다. 사실은 Text 파일을 들여오기/내보내기 하는 것은 예전 강좌에서 이미 다루었던 내용이므로 복습한다 생각하고 한번 찾아보시기 바랍니다.

다시 생각해 보니… 코드에 대한 설명은 질문이 들어오면 하도록 하는 것이 좋을 것 같군요. ^^

정리 — 시트 내보내기 핵심

자주 묻는 질문 (FAQ)

마치며

시트가 수십 개여도 VBA를 이용하면 한 번의 실행으로 텍스트 파일로 모두 내보낼 수 있습니다.