(엑셀/PPT연동) VBA 자동화 모듈 만들기

VBA란 ? ( Visual Basic for Application ) 

VBA로고

 

vba란 Visual Basic for Application의 약자로 Microsoft Office에 내장되어 있는 프로그래밍 언어로

대부분 엑셀에서 사용하기 때문에 엑셀에서만 되는 줄 아는 사람이 많지만 사실 액세스, 워드, 파워포인트 등의 응용프로그램에서 사용이 가능합니다. 

 

굳이 vba를 사용하지 않아도 대부분의 일처리를 할 수 있는데

왜 vba를 사용하느냐?

 

1. 자동화가 된다.

 

아무리 단순한 ctrl+c, ctrl+v 작업이라도 그것만 계속한다면 굉장히 피로해지고 오래 걸리죠

하지만 vba를 사용한다면 1시간 걸릴 작업을 10분으로 단축할 수 있습니다.

 

2. 수정이 쉽다.

말 그대로입니다. 작업을 하고 있을 때 수정사항이 생기면 일일이 바꿀 필요 없이 코딩하던 것에서 한두 개 정도만 만져줘도 문서 전체를 수정할 수 있습니다.

 

3. 연동이 쉽다.

앞서 말해드렸듯이 vba는 엑셀뿐만 아니라 다른 office에서도 사용할 수 있는데요 이를 이용해서 엑셀에 있는 데이터를 다른 파워포인트 같은 office에 연동할 수 있는 것입니다.

 

 

사용방법

 

1. 엑셀창을 킨다.

2. 파일 → 옵션 → 리본사용자 지정 → 리본메뉴사용자 지정 → 개발도구 체크박스 체크!

진행화면

3. 개발도구 위에 리본에 생성된곳 확인 가능 → VisualBasic 클릭  → 창 오픈

4. 우리는 ppt와 엑셀 라이브러리를 사용해야함 고로 다음과 같은 세팅을 반드시 해줘야함

 

도구 → 참조 → Microsoft Office 16.0 (버전 말하는 것) Object Library 체크 / Microsoft Excel 16.0 (버전 말하는 것) Object Library 체크 / Microsoft PowerPoint 16.0 (버전 말하는 것) Object Library 체크

 

5. 모듈 만들어서 코드 기입

Sub SearchTextInPPT()
    Dim xlApp As Object
    Dim xlWorkbook As Object
    Dim xlWorksheet As Object
    Dim pptApp As Object
    Dim pptPresentation As Object
    Dim pptSlide As Object
    Dim pptShape As Object
    Dim cellVal As String
    Dim searchCol As Long
    Dim outputCol As Long
    Dim lastRow As Long
    
    ' 적절한 경로 및 파일 이름으로 바꿔주세요
    Dim examplePath As String: examplePath = "D:\example.xlsx"
    Dim pptPath As String: pptPath = "D:\example.pptx"
    
    ' 엑셀 파일 열기
    Set xlApp = CreateObject("Excel.Application")
    Set xlWorkbook = xlApp.Workbooks.Open(examplePath)
    Set xlWorksheet = xlWorkbook.Worksheets(1)
    
    searchCol = 1 ' 찾을 열 설정
    outputCol = searchCol + 1 ' 결과 열 설정
    lastRow = xlWorksheet.Cells(xlWorksheet.Rows.Count, searchCol).End(xlUp).Row
    
    ' 파워포인트 파일 열기
    Set pptApp = CreateObject("PowerPoint.Application")
    pptApp.Visible = True
    Set pptPresentation = pptApp.Presentations.Open(pptPath)
    
    ' 엑셀 파일의 각 검색 값에 대한 반복문
    For i = 1 To lastRow
        cellVal = xlWorksheet.Cells(i, searchCol).Value
        Dim strArray() As String: strArray = Split(cellVal, ",")
        Dim searchResult As String

        ' 각 검색 값에 대한 반복문
        For n = LBound(strArray) To UBound(strArray)
            Dim foundInPPT As Boolean: foundInPPT = False
            Dim searchText As String: searchText = Trim(strArray(n))
            
            ' 파워포인트 프레젠테이션의 각 슬라이드에 대한 반복문
            For Each pptSlide In pptPresentation.Slides
                ' 슬라이드 내 각 도형에 대한 반복문
                For Each pptShape In pptSlide.Shapes
                    If pptShape.HasTextFrame Then
                        If InStr(pptShape.TextFrame.TextRange.Text, searchText) > 0 Then
                            foundInPPT = True
                            Exit For
                        End If
                    End If
                Next pptShape
                
                If foundInPPT Then Exit For
            Next pptSlide

            ' 결과 문자열 생성
            If foundInPPT Then
                If searchResult = "" Then
                    searchResult = searchText & " = 존재"
                Else
                    searchResult = searchResult & "; " & searchText & " = 존재"
                End If
            Else
                If searchResult = "" Then
                    searchResult = searchText & " = 존재하지 않음"
                Else
                    searchResult = searchResult & "; " & searchText & " = 존재하지 않음"
                End If
            End If
        Next n
        
        ' 결과값을 엑셀 파일에 입력
        xlWorksheet.Cells(i, outputCol).Value = searchResult
    Next i
    
    ' 결과값을 새 엑셀 파일에 저장
    Dim newWorkbook As Object
    Dim newWorksheet As Object
    Set newWorkbook = xlApp.Workbooks.Add
    Set newWorksheet = newWorkbook.Worksheets(1)
    xlWorksheet.Cells.Copy newWorksheet.Cells
    
    ' 새로운 엑셀 파일의 경로와 이름을 필요에 따라 변경하십시오
    newWorkbook.SaveAs "D:\example_results.xlsx"
    
    ' 파워포인트와 엑셀 파일 닫기
    xlWorkbook.Close False
    pptPresentation.Close
    pptApp.Quit
    newWorkbook.Close
    xlApp.Quit
End Sub

실행 !

 

코드설명