개발하는 디자이너의 DIY 일상

오늘 포스트에서는 지난 엑셀 자동화 툴의 뎃글로 문의 주신 헤더에 이미지를 VBA 를 이용하여 자동으로 삽입하는 내용을 소개해 드릴까 합니다.

네 당연히 가능하고 약간 시간이 걸리긴 했지만 유용할 듯 하여 VBA 로 작성하여 보았습니다.

나중에 시간내서 제가 만든 자동화 툴에도 구현 되도록 해보겠습니다.

오늘 내용은 어렵지 않은 내용이므로 차근차근 확인하시며 따라 오시면 될 것 같습니다.

먼저 다음의 객체로 VBA 에서 접근하여 설정을 진행하는 것을 기억해 두시기 바랍니다.

Worksheet.PageSetup.LeftHeaderPicture.Filename = "이미지 파일 경로"

Worksheet.PageSetup.LeftHeader = "헤더에 입력할 텍스트"

위와 같이 worksheet 내에 PageSetup 이라는 개체로 진입한 뒤 PageSetup 개체 내에 각종 헤더 위치에 따라 필요한 설정을 진행할 수 있습니다. 

Worksheet.PageSetup.붙일위치/타입

머릿말 및 꼬릿말의 위치 및 타입은 아래와 같은 대상을 정하여 설정을 진행할 수 있습니다. 

그림을 넣을 경우

글자를 넣을 경우

그 중 한군데에 그림을 적용하는 코드 샘플을 보여드리면 아래와 같습니다.

Sub headerInsertTest()

    Dim aSht As Worksheet    
    Set aSht = ActiveSheet ' 현재 활성화된 시트'
    
    With aSht.PageSetup   ' 반복되는 문장으로 코드가 길어지지 않도록 with 사용'
        With .LeftHeaderPicture
            .Filename = "D:\myTestImage_25.png" ' 이미지 경로'
            .Height = 25    ' 이미지의 크기를 입력'
            .Width = 25     ' 이미지의 크기를 입력'
            .ColorType = msoPictureAutomatic
        End With        
        .LeftHeader = "&G" ' 헤더에 그림을 표시함'
        .Zoom = 100        
    End With

End Sub

역시 동일한 방식으로 글자를 넣게 되면 약간만 수정해 주면 됩니다.

Sub headerInsertTest()

    Dim aSht As Worksheet    
    Set aSht = ActiveSheet ' 현재 활성화된 시트'
    
    With aSht.PageSetup   ' 반복되는 문장으로 코드가 길어지지 않도록 with 사용'
        .LeftHeader = "This Header is writen by VBA"                    
    End With

End Sub

위의 방식으로 하면 VBA 를 이용하여 헤더에 그림이나 글씨를 적용하는 것이 가능합니다.

폴더 내 모든 엑셀 파일에 동일한 머릿글 이미지 적용하려면?!?!

먼저 다른 포스트에서 요청 주신바와 같이 특정 폴더에 엑셀 시트가 수백개가 있다고 하셨는데요. 해당 폴더 내 모든 엑셀 파일에 동일한 헤더 이미지를 적용한다고 하면 약간 코드가 복잡해 지겠지요.

아마 아래와 같은 과정이 필요할 것 같습니다.

• 폴더를 지정하면 폴더내의 엑셀 파일의 리스트를 가져온다.
• 가져온 엑셀 파일에 대하여 하나씩 열어서 다음의 과정을 처리한다
  • 엑셀에 있는 모든 시트에 대하여 각각 작업이 진행되도록 한다.
  • 이미지의 경로를 이용하여 헤더에 그림을 삽입한다.
• 처리가 완료된 엑셀 파일은 저장한다.

어떤가요? 감이 오시나요?

제가 넣기 위하여 준비한 이미지는 아래와 같습니다.

테스트용 엑셀 파일은 아래와 같이 만들어 두었습니다. 하위 폴더가 있는 경우 하위 폴더의 엑셀 파일에도 적용이 되어야 하므로 하위 폴더도 만들어 준비를 했습니다. 

한번 작성된 코드를 보시죠.

Sub insertHeadertoAllXLSfiles()
    Dim fso As Object
    Dim fsoFolder As Object
    Dim rootPath As String
    Dim imagePath As String
    
    rootPath = "D:\test\files"
    imagePath = "D:\test\files\diy_dev_design_01.png"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fsoFolder = fso.GetFolder(rootPath)
    
    getDataRecursive fsoFolder, imagePath
End Sub

Sub getDataRecursive(ByVal baseFolder As Object, ByVal imgName As String)
    
    Dim fso As Object
    Dim tmpSubFolders As Object
    Dim tmpFiles As Object
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set tmpSubFolders = baseFolder.subFolders
    Set tmpFiles = baseFolder.Files
    
    For Each c In tmpFiles
        '여기서 각 엑셀에 실행해야 할 코드를 수행함'

        If LCase(Right(c.Name, 3)) = "xls" Or LCase(Right(c.Name, 4)) = "xlsx" Then ' xls, 또는 xlsx 인 경우'
            '엑셀 파일 열기'
            Dim aBook As Workbook
            Set aBook = Workbooks.Open(c)
            
            '모든시트에 헤더를 넣는 함수 실행'
            For Each tmpsht In aBook.Worksheets
                headerInsertTest tmpsht, imgName
            Next tmpsht
            
            ' 저장하고 닫기'
            aBook.Save
            aBook.Close
        End If
    Next c
    
    For Each d In tmpSubFolders
        
        Dim tmpSub As Object
        Set tmpSubs = fso.GetFolder(d)
        
        ' 하위폴더가 있는경우 하위폴더까지 계속 탐색함 (재귀함수)'
        getDataRecursive tmpSubs, imgName
    Next d
End Sub

Sub headerInsertTest(ByVal sht As Worksheet, ByVal imgPath As String)

    Dim wia         As Object       '이미지 크기 확인을 위한 개체'
    
    Set wia = CreateObject("WIA.ImageFile")
    wia.LoadFile (imgPath)
    
    With sht.PageSetup
        
        With .CenterHeaderPicture       ' 상단 중앙 머릿글에 이미지 넣기'
            .Filename = imgPath
            .Height = wia.Height        ' 이미지의 세로 크기를 입력'
            .Width = wia.Width          ' 이미지의 가로 크기를 입력'
            .ColorType = msoPictureAutomatic
        End With
        .CenterHeader = "&G" ' 헤더에 그림을 표시함'
        .Zoom = 100
    End With
End Sub

좀 길죠? 

여기에는 총 3개의 함수가 들어있습니다.

1. 실제 코드를 실행 시키는 함수 
2. 하위 폴더를 탐색하며 엑셀 파일을 만나면 열어서 시트별로 3번 함수를 실행
3. 워크시트의 머릿글에 이미지를 넣는 함수

이런 순서로 들어있고요. 

1번 함수에서 원하시는 엑셀 파일이 들어있는 경로와 머릿글에 들어갈 이미지의 경로를 지정해 준 뒤 실행하면 됩니다. 제가 실행해 보니 실행 속도가 다소 느리기는 하지만 정상적으로 동작이 잘 되는 군요.

첫번째 함수에서 

    rootPath = "D:\test\xlss"
    imagePath = "D:\test\xlss\diy_header.png"

부분에 이미지의 경로와 엑셀 파일이 들어있는 경로를 바꾸어 주신뒤 동작 시키면 모든 엑셀파일(xls, xlsx)에 아래와 같이 헤더 위치에 원하시는 이미지가 붙게 됩니다. 

참고로 위 스크립트는 머릿글을 붙여야할 문서에 작성하는 것이 아닙니다. 별도 엑셀 파일에 작성한 뒤 동작을 시켜야만 지정한 폴더에 있는 엑셀 파일들을 열어서 자동화 작업이 가능하겠죠.

스크립트가 길어 직접 작성이 어려우실 수 있을 것 같아 작성된 엑셀 파일을 첨부합니다.

해보시고 어려운 부분이 있으면 뎃글 부탁드립니다.

뎃글과 공감은 블로그 작성자에게 큰 힘이 됩니다. 

감사합니다.

2019/06/01 - [DEV/VBA] - 엑셀 VBA 시작하기

2019/08/12 - [DEV/VBA] - [VBA] 문자열 가지고 놀기

2019/12/06 - [DEV/VBA] - [vba] 하위폴더 내의 모든 파일 정보 가져오기

2020/01/08 - [DEV/c#] - Excel Automate, 엑셀 자동화 프로그램