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

안녕하세요. 이번 강좌에서는 엑셀 시트에 있는 이미지 파일 경로를 이용하여 셀에 이미지를 붙여넣는 스크립트를 한번 알아 보겠습니다. 엑셀에 이미지를 몇장 붙여 넣는 거야 그림 삽입하기로 손쉽게 넣으면 되지만 붙여넣어야 할 그림이 몇백개 이상이 되면 답이 나오지 않는 상황이 옵니다. 절망 적이죠. 바로 야근 각입니다.

하지만 스크립트가 출동한다면 ?

칼퇴근 쌉가능

게다가 셀의 크기에 맞게 가지런히 딱 붙여줄수 있다면 말할 필요도 없이 선배님에게 쓰담쓰담 각 입니다.

어렵지 않으니 천천히 따라와 주시면 됩니다.

*** 개발에 관심 1g도 없고 그림만 자동으로 붙여 넣고 싶으시다면 아래 포스트를 참고해주세요

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

엑셀에 이미지를 붙여 넣는 방법중에 대표적으로 아래 두가지 방법이 있을 수 있겠습니다.

• Worksheet.Pictures.Insert(pathName & bmpName)
• Worksheet.Pictures.Insert.Shapes.AddPicture(Filename:=(pathName & bmpName), linktofile:=msoFalse, savewithdocument:=msoCTrue, Left:=imgPosX, Top:=imgPosY, width:=imgWidth, Height:=imgHeight)

요렇게 두가지 인데요. 

저는 아래의 방법을 선호 합니다. 위의 방법은 코드가 짧고 단순하여 작성이 편리하기는 하지만 엑셀 2007 이하의 버전에서 삽입한 이미지가 2010 버전에서 간혹 누락이 되는 경우가 있습니다. 정확히 원인을 알수는 없지만 두번째 방법은 그런 문제가 없어 현재 두번째로만 사용중입니다.

자 그럼 코드를 작성해 볼까요?

먼저 이미지 이름 (경로 포함 또는 경로는 별도로 설정) 이 있는 셀이 연속으로 있어야 겠습니다.

요런식으로 말이죠.

그럼 코딩을 시작해 보겠습니다. 아래 코드를 봐주세요 전에 작성했던 코드에 비하면 조금 길지만 어렵지 않습니다.

Sub insertPicture()

    Dim aSht As Worksheet
    Dim rngA As Range
    Dim rng8 As Range
    
    Dim pathName As String  '이미지가 들어있는 경로'
    
    Dim imgPosX As Integer
    Dim imgPosY As Integer
    Dim imgWidth As Integer
    Dim imgHeight As Integer
    
    '이미지 정보 확인을 위한 개체를 만들겁니다.'
    Dim wia         As Object
    
    Dim m_Width As Integer
    Dim m_Height As Integer
    
    Dim pic As Shape
    
    Set aSht = ActiveSheet  ' 현재 선택된 시트에서 진행하겠습니다.'
    
    Set rngA = aSht.Range("B2")
    Set rngA = aSht.Range(rngA, rngA.End(xlDown)) ' 요렇게 하면 B2 부터 아래로 맨 끝까지가 설정이 되는거 아시죠?'
    
    Set wia = CreateObject("WIA.ImageFile")     ' 이미지 정보 확인을 위한 오브젝트를 설정함'
     
    pathName = "F:\test\unimog\" '이미지가 들어있는 경로'

    For Each aRng In rngA
    
        Set rng8 = aRng.Offset(0, 1) 'rng8 이라는 변수'
        bmpName = aRng.Value
                
        bmpName = bmpName
        
        ' 이미지가 경로상에 실제 존재하는지 파악하기 위하여 Dir 함수 사용'
        isBmpExist = Dir(pathName & bmpName)
        
        ' 이미지가 없으면 "" 가 리턴됨'
        If (isBmpExist <> "") Then
            
            ' 이미지를 열어서 사이즈를 확인함'
            wia.LoadFile (pathName & bmpName)
            
            m_Width = wia.Width
            m_Height = wia.Height
            
            '이미지의 세로크기가 셀 최대 높이 값을 넘는 경우 최대 높이값으로 설정함
            If (m_Height > 540) Then
                Dim scaleRatio As Double
                scaleRatio = 540 / m_Height
                m_Height = 540
                m_Width = CInt(m_Width * scaleRatio)
            End If
            
            '만약 이미지의 세로 크기가 현재 설정되어있는 셀보다 작다면 셀의 세로 길이를 늘여줌'
            If rng8.RowHeight < (m_Height * 0.75 + 4) Then
                rng8.RowHeight = m_Height * 0.75 + 4
            End If
            
            '이미지가 셀의 중앙에 위치하도록 위치와 크기를 계산하여줌.'
            '엑셀에서의 수치 단위가 px 이 아닌 point 이기 때문에 px 로 계산된 값에 각각 0.75 를 곱해주었다.
            imgPosX = rng8.Left + rng8.Width / 2 - (m_Width * 0.75) / 2
            imgPosY = rng8.Top + (rng8.RowHeight * 0.5 - (m_Height * 0.75 * 0.5))
            imgWidth = m_Width * 0.75
            imgHeight = m_Height * 0.75
            
            '최종 이미지를 위에서 설정한 값으로 삽입하는 과정'
            '복잡하게 계산할거 없이 다 동일한 크기에 동일한 위치라면 imgPosX, imgWidth, imgHeight 는 고정값을 적용해 주어도 된다.'
            Set pic = aSht.Shapes.AddPicture(Filename:=(pathName & bmpName), linktofile:=msoFalse, savewithdocument:=msoCTrue, Left:=imgPosX, Top:=imgPosY, Width:=imgWidth, Height:=imgHeight)
            
            
        End If
   
   Next
   
   Set wia = Nothing
   
End Sub

  좀 특별한 개체로 createObject 라는 함수가 호출이 되었고 그 개체에서 이미지 정보를 확인하는 부분이 들어있는데요. 이는 엑셀에서 COM(Component Object Model) 이라고 하는 오브젝트를 생성하여 기능을 수행하는 과정을 보여줍니다. vba 나 .net 기반의 개발 환경에서 아주 빠르고 손쉽게 여러가지 기능을 사용할 수 있도록 하기 위한 라이브러리 같은 개념이라고 보시면 됩니다. 다만 플랫폼이나 사용자별 환경에 따라 상당히 제약적인 부분이 있으니 남발하는 것은 좋지 않겠습니다.

F5 키를 눌러 실행을 하면 아래와 같이 이미지가 붙게 됩니다.

참고로 엑셀 시트의 확대 배율을 반드시 100% 로 해놓고 실행하셔야 정확하게 셀에 붙습니다. 축소된 상태면 이미지가 밀려 내려가는 경우가 발생할 수 있습니다.

약 31개의 이미지가 붙었는데요. 1초만에 모두 삽입 되었습니다.

제가 작성한 코드를 복사해 가신 후 아래 내용에 경로만 수정해 주시면 바로 사용이 가능합니다.

pathName = "F:\test\unimog\" <-- 이미지가 들어있는 폴더 경로, 끝에 \ 를 붙여줘야 합니다.

물론 이미지 이름이 붙어 있는 range 의 시작 셀의 이름도 설정을 해주어야 겠지요.

Set rngA = aSht.Range("B2") <-- B2 부분에 시작 셀의 이름을 적어주면 됩니다. C3, F1 처럼 말이죠.

어디 메모장이나 노트에 붙여 넣어 두셨다가 필요하실 때 사용하시면 됩니다. 

이상으로 이미지를 한번에 붙여 넣는 자동스크립트를 알아 보았습니다.

2021/01/03 - [DEV/Adobe Script] - [포토샵스크립트] 코딩을 통하여 '모동숲 카드' 만들기

2020/09/04 - [DEV/VBA] - [vba] 엑셀 이미지 리스트로 일괄 다운로드 받기

2020/01/08 - [DEV/c#] - Excel Automate, 엑셀 자동화 프로그램 (그림 자동으로 붙여주는 프로그램)

2020/05/21 - [DEV/VBA] - [Excel] LCD 인치 정보로 가로 세로 길이 알아내는 방법

셀의 크기, 테두리 등을 VBA 로 지정하는 방법

2020/01/30 - [분류 전체보기] - [vba] 셀 속성 조정하기 (넓이, 높이, 숨기기, 테두리 등)

만약 사진을 잘못 붙여넣어서 한번에 다 지워야 한다면?

https://diy-dev-design.tistory.com/30

2020/08/27 - [DEV/VBA] - 재택근무 필수 엑셀 "자리안비움" - 윈도우 꺼짐 방지