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

엑셀 파일을 이용하여 정말 다양한 작업이 가능합니다. 계산이며 표만들기며 각종 정보를 분석하거나 데이터 화 시키는 작업 등 정말 할 수 있는 일이 무궁무진 하죠. 그런데 가끔 엑셀을 이용하여 특정 정보를 별도로 텍스트 등으로 저장해야 하는 경우가 있습니다. 개발 쪽에서 어떤 데이터 리스트를 csv 형태로 요구하였는데 그러기 위하여는 굳이 정보를 재 가공 해야 하는 경우라던가 자료의 특성상 데이터를 가공하는 위험 부담이 있는 경우에는 좀 부담스럽죠.

필요한 필드만 또는 특정 필드의 값들을 특정 형태로 가공해서 저장하고 싶을 때 스크립트를 이용하여 파일 쓰기를 해 줄 수 있다면 큰 도움이 될 겁니다.

아니면 HTML 이나 CSS 처럼 엑셀에 있는 정보들을 특정한 포멧에 맞게 뽑고 싶은데 엑셀의 저장 기능에는 그런것은 없기 때문에 코딩을 할 수 있게 되면 활용 범위가 더욱 커지고 불필요한 노동을 줄일 수 있습니다.

오늘은 엑셀에서 텍스트 파일 형태로 파일을 추출하는 간단한 방법을 알아보도록 하겠습니다.

코드는 대충 아래와 같습니다.

Open FILE_NAME For Output As #1
Print #1, YOUR_STRING
Close #1

아주 간단하죠? 간단해서 제가 자주 사용하는 코드 입니다.

FILE_NAME 부분에 실제 저장되어야 할 파일 명을 경로를 포함하여 적어주시고요 저장하고 싶은 텍스트를 YOUR_STRING 부분에 넣으면 해당 파일을 기록하고 닫는 코드 입니다.

손쉽게 사용하기 위하여 함수 형태로 만들면 아래와 같이 되겠습니다. 물론 너무 짧아서 굳이 함수로 만들 필요도 없기는 하지만요.

Sub writeMyFile (byVal strPath as string, byVal currentText as string)
	Open strPath For Output As #1
	Print #1, currentText
	Close #1
End Sub

'사용할때 '

sub test()
	dim myString as string
	myString = "hello" & vbNewLine
	myString = myString & "DIY" & vbNewLine
	myString = myString & "DEV" & vbNewLine
	myString = myString & "DESIGN" & vbNewLine
	
    writeMyFile "d:\test.ini", myString
end sub

아래 test 라는 함수에서 실제 텍스트를 써주는 부분을 호출하고 아래와 같은 파일이 만들어 지게 됩니다.

보시는 것처럼 vbNewLine 과 같은 줄바꿈 기호 등을 줄의 맨뒤에 추가하여 여러줄을 동시에 기록하는 것도 가능합니다.

제가 만든 test 부분에서 기록하고 싶은 여러 내용을 포멧에 맞게 변수에 저장을 한 뒤 마지막에 파일을 써주는 함수에 보내면 원하는 형식으로 텍스트 파일이 만들어 지는 것이죠.

자 손쉽게 엑셀의 자료를 텍스트 파일로 기록해 주는 함수를 만들어 보았습니다. 스트링 형식으로 저장된 내용을 그냥 기록만 해주면 되기 때문에 해당 기능을 이용하여 자바스크립트, CSS, HTML 등의 형식으로 손쉽게 저장하 실 수 있습니다.

참고로 저는 이미지를 붙여 넣는 기능과 함께 해당 이미지를 웹 브라우저를 통해 미리 볼 수 있는 html 을 뽑는 스크립트를 자주 사용합니다. 웹 브라우저에서 이미지를 보게 되면 파일이 많아도 화면 이동 속도가 거의 느려지지 않고 이미지와 함께 전달하게 되면 사용자가 간단하게 이미지 리스트를 확인 할 수 있기 때문입니다. 또한 이미지 수정 시 별도로 엑셀등을 수정하지 않고 저장된 이미지만 교체하면 바로 수정된 결과를 볼수 있는 장점도 있기 때문입니다.

사용하시는 분마다 용도는 다르겠지만 텍스트를 추출하는 기능만으로도 아주 커다란 효과를 낼 수 있을지 모릅니다.

자 그럼 오늘은 이만~

2019/07/12 - [DEV/VBA] - [vba] 자동화를 위하여 엑셀의 영역 설정하는 방법

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

2019/07/12 - [DEV/VBA] - [vba] For 구문 이용하기와 offset 사용 해 보기

2019/09/20 - [DEV/VBA] - [VBA] 엑셀에 그림 자동으로 넣기

정말 지긋지긋한 마이크로 소프트 입니다. 저는 VBA 를 하면서 이번 포스트 내용만큼 어의가 없는 경우는 없었던거 같습니다. 왜 때문이냐고요? 바로 아주 단순한 셀 넓이를 VBA 를 이용하여 지정하는 방법 때문입니다.

자 제가 왜 이런 반응을 보이는가 하면요.

엑셀 VBA 에서 셀의 넓이 즉 range.columnWidth 를 지정할때 정말 독특한 단위와 방법을 사용하기 때문입니다.

일단 셀의 넓이는 다음의 두가지 방법으로 구할 수 있습니다. 그런데 그 두가지가 반환하는 값의 정체를 보면 정말 당황스럽기 짝이 없죠.

Range.Width [읽기]
 : 셀의 넓이를 point 로 반환한다. 

Range.ColumnWidth [읽기/쓰기]
 : (나름데로 해석한 결과)셀의 넓이를 현재 지정된 폰트의 "Normal" 인 상태를 기준으로 하는 글자 하나의 크기를 1로 하고 마진값을 추가한 값을 설정 또는 반환한다.

????????????

저게 뭔가요.. 

물론 글자가 짤리 않도록 셀의 폭을 지정하는 기능 아주 좋습니다. 글자수만 알면 되는거죠. 훌륭합니다. 네.

그런데 왜 Width 는 읽기 만 가능하며 point 나 pixel 같은 값으로는 설정이 안되는 것인가요? 저와 같은 그래픽 디자이너는 이미지를 붙이든 뭘해도 pixel 을 기준으로 값을 정의하는게 일상인데요. 뜬금없이 왠 글자의 크기를 이용하여 margin 까지 덧붙여 크기가 지정되는 걸까요? 게다가 현재 지정된 폰트의 Normal 상태 일때 라니... 만약 글자 크기가 달라지거나 스타일이 달라지면 같은 값을 지정해도 폭이 달라질 수 있다는 이야기가 됩니다.

헐

해외 사이트들을 구글링 해보았으나 마땅한 답이 없더군요. 

그래서 그냥 코드를 짜 보았습니다.

VBA function : Pixel to ColumnWidth

Sub setColumnWidth(ByVal mRng As Range, ByVal targetWidth_pixel As Integer)

    'point = pixel * 0.75'
    'pixel = point / 0.75'
    'columnWidth = char Width + margin(fixed)'
    
    Dim cWidth_A As Double
    Dim cWidth_B As Double
    Dim cWidth_margin As Double
    Dim columnWidthRatio As Double
    Dim testColumnWidth As Double
    
    Dim cWidth As Double
    
    testColumnWidth = 10
    
    
    ' 가로 크기 지정시 발생되는 margin 을 알아보기 위하여 1배, 2배 크기일때 유지되는 margin 을 구해보자'
    mRng.ColumnWidth = testColumnWidth
    cWidth_A = mRng.Width   '(point)'
    
    mRng.ColumnWidth = testColumnWidth * 2
    cWidth_B = mRng.Width   '(point)'
    
    ' 항상 고정으로 유지되는 margin 크기 확인 (point)'
    cWidth_margin = cWidth_B - ((cWidth_B - cWidth_A) * 2)
        
    'point 값이 columnWidth 로 변환되기 위한 ratio 를 구한다. 이때 전체 넓이에서 위에서 구한 margin 을 생략하고 계산한다. '
    'columnWidthRatio : point --> columnWidth'
    columnWidthRatio = mRng.ColumnWidth / (cWidth_B - cWidth_margin)
    
    '최종 크기 지정'
    mRng.ColumnWidth = (targetWidth_pixel - (cWidth_margin / 0.75)) * 0.75 * columnWidthRatio
        
        
End Sub

약간 복잡해 보이지만 함수 형태로 만들어 놓았으므로 코드를 가져다가 그냥 사용하시면 됩니다. 인자로 크기를 조정해야 하는 셀과 pixel 기준의 크기를 입력해 주시면 되고요. 아래와 같이 사용하시면 됩니다.

Sub test()
    Dim rngA As Range    
    Set rngA = [d3]
    
    setColumnWidth rngA, 256
End Sub

이렇게 하면 D3 에 해당하는 column 의 폭이 256 pixel 로 설정이 됩니다.

코드를 좀 보시는 분은 금방 아시겠지만 원리는 다음과 같습니다.

columnWidth 는 고정된 단위가 아니므로 point 라는 단위를 이용하여 columnWidth 로 환산하기 위한 ratio 를 구해야 합니다. columnWidth / Width 하면 되는거죠. 그런데 여기에 함정이 있습니다. 

columnWidth 라는 값이 반환하는 값이 글자의 폭 + margin 이기 때문에 위와 같이 단순 계산을 하면 정확한 값으로 설정을 할 수가 없습니다. margin 만큼씩 계속 오차가 있는 것이지요. 그래서 margin 을 구하는 과정이 선행되었던 것입니다.

제가 몇번 테스트 해본 결과 글자을 속성을 중간에 바꾸지 않는다면 margin 의 값은 항상 일정한 것을 확인하였기 때문에 columnWidth 를 10 , 20 으로 두배 차이나게 각각 적용했을때의 Width 를 이용하여 변하지 않는 크기를 찾아내 해당 크기를 margin 으로 지정하였습니다.

결과적으로는 현재 구해진 Width(point) - margin(point) 로 ColumnWidth 를 나누어 주게 되면 point 값을 columnWidth로 환산 하기 위한 비율이 구해지게 되는 것이죠.

끝으로 입력된 픽셀에서 margin 에 해당되는 픽셀 크기를 빼 준뒤 point 로 전환한 다음 위에서 구한 비율을 곱해주게 되면 정확하게 pixel 에 대응되는 columnWidth 를 구할 수 있게 됩니다.

정말 저는 미국인들이 inch 를 사용하는 이유도 모르겠고 microSoft 사에서 위와 같이 독특한 단위만 입력하도록 개발해놓은 이유도 알 수가 없습니다. 

이번 기회로 저역시 좋은 공부가 되었기에 포스트로 남깁니다.

도움이 되셨거나 본 포스트가 괜찮으셨으면 공감 부탁드립니다~

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

2019/09/20 - [DEV/VBA] - [VBA] 엑셀에 그림 자동으로 넣기

2019/08/30 - [DEV/VBA] - [VBA]RGB 색상 값이 들어있는 셀에 셀 색상 지정하기

2022.12.02 - [DEV/VBA] - [excel vba] 16진수를 10진수로, 10진수를 16진수로 변경

일을 하다 보면 가끔 업무를 진행하던 경로 하위에 있는 데이들의 리스트를 만들어야 하는 경우가 있습니다. 하나의 폴더라면 어떻게 해보겠는데 그 폴더가 하위 뎁스가 연속해서 있고 저장되어 있는 파일이 불특정으로 다수 있는 경우 이를 리스트로 만드는 일은 정말 끔찍한 일이 아닐 수 없습니다.

이런 경우 손쉽게 리스트로 작성하는 스크립트를 만들어 보았습니다.

폴더 및 하위 폴더의 파일 명칭은 물론 저장된 경로, 파일의 만든날짜, 수정한 날짜 등의 속성을 표시할 수 있도록 하고 파일의 용량과 타입까지 자동으로 리스트로 만들어 보려 합니다.

이러한 코드를 작성하기 위하여 개발을 할 내용을 준비해봅니다.

1. 엑셀 vba 에서 사용 가능한 File 및 Directory 관련 개체를 알아보자.
2. 지정한 폴더내에 파일이 있는 경우 파일의 이름과 속성등을 가져와 기록해주자
3. 파일이 바뀔때마다 줄을 바꾸어 주자
4. 폴더를 만나게 되면 해당 폴더 하위의 파일을 찾아주자.
5. 2 ~ 4 번은 하위 폴더를 만날때 마다 계속해서 반복해주어야 하므로 재귀 함수로 동작하도록 구성하자.
   * 재귀함수란 자신이 자신을 호출하는 함수를 말합니다.
6. 연속해서 위치를 이동시켜야 하므로 range.offset(y,x) 를 이용하되 x, y 는 전역 변수 (public)로 설정해주자

요 정도면 원하던 기능의 구현이 가능할 것 같습니다.

제가 작성한 코드를 아래에 올려보겠습니다.

보시면서 위에 설명드린 동작이 어떤 과정으로 일어나는지 확인해 보세요.

Public fso As Object
Public offsetY As Integer
Public offsetX As Integer
Public stRng As Range

Sub getSubFileList()

    Dim fsoFolder As Object
    Dim aSht As Worksheet
    Dim rootpath As String
    
    rootpath = "C:\Users\mariine\Pictures\excel_test\"
    
    offsetX = 0
    offsetY = 0
    
    Set aSht = ActiveSheet
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fsoFolder = fso.GetFolder(rootpath)

    Set stRng = aSht.Range("B2")

    getDataRecursive fsoFolder
    
End Sub

Sub getDataRecursive(ByVal baseFolder As Object)

    Dim tmpSubFolders As Object
    Dim tmpFiles As Object
    Dim tmpRng As Range
    
    offsetX = offsetX + 1
    
    Set tmpSubFolders = baseFolder.subFolders
    
    Set tmpFiles = baseFolder.Files
    
    For Each c In tmpFiles
        
        stRng.Offset(offsetY, offsetX).Value = c.Name                       ''파일명
        stRng.Offset(offsetY, offsetX - 2).Value = offsetY + 1              ''인덱스
        stRng.Offset(offsetY, offsetX - 1).Value = c.ParentFolder.Path      ''경로명
        stRng.Offset(offsetY, offsetX + 1).Value = c.Size / 1000# & "kb"    ''파일용량
        stRng.Offset(offsetY, offsetX + 2).Value = c.DateCreated            ''만든날짜
        stRng.Offset(offsetY, offsetX + 3).Value = c.DateLastModified       ''수정한날짜
        stRng.Offset(offsetY, offsetX + 4).Value = c.Type                   ''파일타입
        
        offsetY = offsetY + 1
    Next c
    
    offsetX = offsetX - 1
    
    For Each d In tmpSubFolders
        
        Dim tmpSub As Object
        Set tmpSubs = fso.GetFolder(d)

        getDataRecursive tmpSubs
        
    Next d     
    
End Sub

제일 상단의 4줄은 이번 코드에서 사용할 전역 변수를 설정해주는 모습입니다.

전역변수를 설정해주면 하위의 어떤 함수에서건 해당 값을 읽고 쓸 수 있게 됩니다. A 함수에서 변경한 전역 변수의 값을 B 함수에서도 그대로 사용이 가능하게 됩니다.

그 다음 만나는 Sub getSubFileList() 함수가 동작을 설정하는 함수가 되겠고요. FileSystemObject 라는 파일 및 디렉토리 작업이 가능한 COM 오브젝트를 선언하여 서브폴더나 폴더, 파일 등에 접근이 가능하도록 하고 있습니다.

그리고 그뒤로 이어지는 재귀 함수인 Sub getDataRecursive(ByVal baseFolder As Object) 를 호출하는 것으로 동작이 시작됩니다. 

그렇게 시작된 재귀함수는 입력받은 폴더내에 파일이 있으면 파일의 이름 및 기타 속성들을 줄을 바꾸어 가며 기록을 진행하고 모든 파일에 대하여 기록이 끝나면 그아래 해당 폴더에 하위 폴더가 있는지 검사한뒤 하위 폴더가 있는 경우 또다시 자신 (폴더내의 파일을 찾아 기록하는 함수) 을 호출하여 주게 되는 겁니다.

이렇게 해주면 하나의 재귀함수로 아무리 깊고 많은 뎁스의 폴더안의 파일들도 모두 탐색이 가능해 지게 되겠습니다.

이렇게 만들어진 리스트는 아래와 같습니다.

만약 특정 확장자만 검색하고 싶으시면 실제 파일 속성을 기록하는 For 구문의 바로 첫 행에 IF 문을 이용하여 파일 이름내에 필요한 확장자가 있는지 검사를 진행하면 되겠습니다.

예를 들어 xlsx 파일만 리스트로 만들고 싶다면 해당 For 구문을 아래와 같이 변경하면 되겠습니다.

    For Each c In tmpFiles
        
        If InStr(c.Name, ".xlsx") Then
            stRng.Offset(offsetY, offsetX).Value = c.Name                       '파일명
            stRng.Offset(offsetY, offsetX - 2).Value = offsetY + 1              '인덱스
            stRng.Offset(offsetY, offsetX - 1).Value = c.ParentFolder.Path      '경로명
            stRng.Offset(offsetY, offsetX + 1).Value = c.Size / 1000# & "kb"    '파일용량
            stRng.Offset(offsetY, offsetX + 2).Value = c.DateCreated            '만든날짜
            stRng.Offset(offsetY, offsetX + 3).Value = c.DateLastModified       '수정한날짜
            stRng.Offset(offsetY, offsetX + 4).Value = c.Type                   '파일타입
            offsetY = offsetY + 1
        End If
        
    Next c

어렵지 않죠?

만약 있어서는 안되는 확장자가 들어있다면 눈에 띄게 표시를 하는것도 어렵지 않습니다.

예를 들어 폴더내에 hwp 파일만 있어야 한다고 했을때 해당 폴더에 .doc 파일처럼 또 다른 있는 경우 빨간색으로 셀을 표시할 수 있습니다.

For Each c In tmpFiles
        
        stRng.Offset(offsetY, offsetX).Value = c.Name                       '파일명
        stRng.Offset(offsetY, offsetX - 2).Value = offsetY + 1              '인덱스
        stRng.Offset(offsetY, offsetX - 1).Value = c.ParentFolder.Path      '경로명
        stRng.Offset(offsetY, offsetX + 1).Value = c.Size / 1000# & "kb"    '파일용량
        stRng.Offset(offsetY, offsetX + 2).Value = c.DateCreated            '만든날짜
        stRng.Offset(offsetY, offsetX + 3).Value = c.DateLastModified       '수정한날짜
        stRng.Offset(offsetY, offsetX + 4).Value = c.Type                   '파일타입
        
        If Not InStr(c.Name, ".hwp") Then
            stRng.Offset(offsetY, offsetX).Interior.Color = RGB(255, 0, 0)
        End If
        
        offsetY = offsetY + 1
        
    Next c

참 쉽죠?

이렇게 해서 폴더내의 모든 파일을 하위폴더를 포함하여 가져오는 스크립트를 알아보았습니다. 이런식의 작업은 엑셀의 워크시트 함수만을 이용하여 작업하기는 어렵기 때문에 vba를 좀 할줄 알면 아주 많은 시간을 절약하는 것이 가능합니다. 

오늘은 여기까지 하도록 하겠습니다.

관련하여 사전 지식이 부족하신 분은 아래 관련 포스트를 링크해 드리니 참고하시면 되겠습니다.

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

2019/08/30 - [DEV/VBA] - [VBA]RGB 색상 값이 들어있는 셀에 셀 색상 지정하기

2019/07/12 - [DEV/VBA] - [vba] For 구문 이용하기와 offset 사용 해 보기

2019/07/12 - [DEV/VBA] - [vba] 자동화를 위하여 엑셀의 영역 설정하는 방법

감사합니다.

공감은 블로그 포스팅을 진행하는 블로거에게 큰 힘이 됩니다~

어떤 엑셀 페이지에 늘 사용하는 스크립트를 작성하였으나 매번 실행하기 위하여 vba 창을 눌러 F5를 누르는 것은 사실 본인이 직접 스크립트 코드를 개발하는 입장이라면 조금도 번거로울 것이 없는 것이지만 누군가에게 스크립트를 작성하여 엑셀 파일을 건내 주는 경우라면, 실제 사용하는 사람이 개발과 거리가 있는 사람이라면 더더욱 이와 같은 방법으로 스크립트를 실행시키는 것은 불편한 방법이 됩니다.

게다가 스크립트가 딱 하나가 아니고 어떤 스크립트는 정렬을 해주고, 어떤 스크립트는 불필요한 값을 숨겨주고, 또 어떤 스크립트는 데이터를 새로 읽어 들여 전체 시트를 리프레쉬 하는 등의 여러개의 기능을 개발한 상태라면 vba 창에서 실제 해당 스크립트 위치에 마우스 커서를 가져다 놓는 등의 추가 행위가 필요합니다.

이럴때 버튼을 이용하여 특정 스크립트 (엑셀에서는 매크로라고 합니다) 를 연결하게 되면 지정된 버튼을 누를때 마다 필요한 스크립트가 동작하게 됩니다. 

말하자면 간단한 툴이 만들어 지는 것이죠. 

사실 엑셀의 각 셀은 함수나 계산식을 포함할 수 있으므로 왠만한 복잡한 것들은 워크시트의 함수와 계산식으로 처리가 가능하지만 이미지를 불러온다거나 어떤 데이터의 속성을 알아내거나 약간은 복잡한 관계의 셀 값들을 이용하여 결과값을 얻는다든가 하는 등의 일은 워크시트 함수로는 조금 어려운 부분이 있습니다. 그래서 매크로 스크립트를 작성하는 것이죠. 이런 기능기능이 모여 마치 어떤 툴처럼 제작이 가능합니다.

영업사원들에게는 견적서 프로그램을 만드는 것이 가능할 것이고 세무, 회계부서에서는 각종 세금 관계 계산을 위한 처리가 가능할것입니다. 

오늘은 그렇게 만들어진 스크립트 들을 버튼을 이용하여 동작하도록 하는 방법을 소개해 드릴까 합니다.

먼저 제가 초등학교 저학년 학생들을 위한 간단한 덧셈 연산을 자동으로 생성하는 엑셀파일을 만든적이 있습니다. 

https://diy-dev-design.tistory.com/41?category=791049

해당 파일을 보면 아래와 같은 화면을 볼 수 있는데요. 우측의 버튼을 누를 때 마다 버튼의 텍스트에 해당하는 기능으로 좌측 값이 자동으로 생성이 됩니다. 위 링크된 페이지에 가면 실제 엑셀 파일을 받으실 수 있으니 해당 파일을 받으셔서 참고해보셔도 좋을 것 같습니다.

우선 단추(버튼)르르 이용한 동작을 확인할 간단한 스크립트를 작성해 보겠습니다.

• 첫번째 스크립트로 셀 A1~C1 번에 숫자 1, 2, 3 을 넣어 보겠습니다.
  • 만약 이미 숫자가 들어있다면 현재 들어있는 숫자에 각각 1, 2, 3을 더하도록 해보죠.
• 두번째 스크립트는 A2 ~ C2 에 A1 + A1, B1 + B1, C1 + C1 까지의 값을 넣는 동작을
• 세번째 스크립트는 A3 ~ C3 에 A1 * A2, B1 * B2, C1 * C2 의 값을 넣는 동작을
• 네번째 스크립트는 A4 ~ C4 에 C1 ~ C3 의 값을 이용하여 셀의 색상을 채워 넣어 보겠습니다.
  • 컬러를 지정하기 위한 숫자는 RGB 로 각각 0~255만 가능하므로 255 보다 크면 255로 설정 하겠습니다.
  • 그리고 255보다 값이 커진다면 첫번째 행의 값을 낮은 값으로 바꾸어 보겠습니다.

스크립트를 작성해 보겠습니다.

먼저 엑셀을 열고 VBA 창 (alt + F11) 을 실행한 뒤 아래와 같이 입력해 보겠습니다.

Sub function_a()
    
    'cells(rowIndex, columnIndex) 와 같은 형식으로 셀을 지정하는 방법'
    If Len(Cells(1, 1)) Then
        Cells(1, 1).Value = Cells(1, 1).Value + 1
    Else
        Cells(1, 1).Value = 1
    End If
    
    If Len(Cells(1, 2)) Then
        Cells(1, 2).Value = Cells(1, 2).Value + 2
    Else
        Cells(1, 2).Value = 2
    End If
    
    If Len(Cells(1, 3)) Then
        Cells(1, 3).Value = Cells(1, 3).Value + 3
    Else
        Cells(1, 3).Value = 3
    End If

End Sub

Sub function_b()
    
    ' Range(name) 의 형태로 셀을 지정하는 방법'
    
    Range("A2").Value = Range("A1").Value * 2
    Range("B2").Value = Range("B1").Value * 2
    Range("C2").Value = Range("C1").Value * 2
    
End Sub

Sub function_c()
    
    ' range 라는 변수를 설정하여 정의하는 방법'
    
    Dim a3, b3, c3
    
    Set a3 = Range("A3")
    Set b3 = Range("B3")
    Set c3 = Range("C3")
    
    a3.Value = a3.Offset(-2, 0).Value * a3.Offset(-1, 0).Value
    b3.Value = b3.Offset(-2, 0).Value * b3.Offset(-1, 0).Value
    c3.Value = c3.Offset(-2, 0).Value * c3.Offset(-1, 0).Value
    
End Sub

Sub function_d()

    Dim a3, b3, c3, rngAll
    
    Set a3 = Range("A3")
    Set b3 = Range("B3")
    Set c3 = Range("C3")
    
    Set rngAll = Range("A4", "C4")
    
    If a3.Value > 255 Then
        a3.Value = 255
        a3.Offset(-2, 0).Value = 1
    End If
    
    If b3.Value > 255 Then
        b3.Value = 255
        b3.Offset(-2, 0).Value = 2
    End If
    
    If c3.Value > 255 Then
        c3.Value = 255
        c3.Offset(-2, 0).Value = 3
    End If
    
    rngAll.Interior.Color = RGB(a3.Value, b3.Value, c3.Value)
    
End Sub

Sub ~ End Sub 까지가 하나의 매크로 스크립트가 되겠습니다. 총 4개의 스크립트가 작성이 되었는데요. 내용은 위에서 기술한 내용데로 작성하였으며 각각의 스크립트에는 셀을 지정하는 방법을 달리하여 작성해 보았습니다. 여러분들도 직접 타이핑 해보시면서 어떤방법으로 셀을 지정하는 것이 편리한지 경험해보시는 것이 도움이 되실 것 같습니다.

끝으로 rngAll 이라는 Range 개체를 만들었는요 현재 시트의 4번째 행의 A~C 열을 한꺼번에 설정해주었습니다. 그리고 한꺼번에 해당 셀의 색상을 변경을 하는 코드를 넣어 봤습니다.

자 스크립트를 모두 작성하였으면 이제 단추를 만들어 보아야 겠죠? 

그런데 엑셀의 기본 기능 중에는 단추가 보이지 않습니다. 리본 메뉴가 만들어 지면서 사용하지 않는 기능들은 꺼내져 있지 않기 때문인데요. 사용하기 위하여 꺼내 보겠습니다. 처음 한번만 하시면 됩니다.

파일 메뉴로 가셔서 아래와 같은 창이 나오면 제일 아래 옵션을 선택합니다.

그럼 옵션창이 팝업으로 뜨게 됩니다.

팝업으로 뜬 창에서 '리본 사용자 지정' 텝으로 이동해보면 아래와 같이 아주 많은 기능이 나열된 것을 볼 수 있습니다.

위 그림에서 좌측은 엑셀이 가지고 있는 모든 기능을 보여주고 있고 우측은 현재 사용자의 리본메뉴에 추가되어 있는 기능들이라고 생각하시면 되겠습니다.

아래 1번의 드롭다운 메뉴에 리본에 없는 메뉴를 선택하신 뒤 리스트에서 2번 '단추(양식컨트롤)' 이 바로 우리가 필요한 단추를 넣는 기능입니다. 단추라고 하면 좀 이상한데요, 익숙한 용어로는 버튼입니다. 버튼.

우측은 해당 기능을 넣을 리본 메뉴라고 했는데요, 저는 삽입 이라는 탭에 넣을 계획 입니다. 이미 있는 그룹에 넣는 것은 불가능하고 새로운 그룹을 만들어서 넣어야 하기 때문에 3번 삽입 을 선택하여 메뉴를 확장 시킨 뒤 아래 '새 그룹' 이라는 버튼을 이용하여 새로운 그룹을 추가해 줍니다.

새로 추가한 그룹의 이름을 바꾸어 볼 차례 입니다. 저는 제 블로그 이름인 "DIY_DEV_DESIGN" 라고 변경하였습니다. 아래 이름 바꾸기라는 버튼을 이용하여 변경하시면 됩니다.

이제 단추를 선택한뒤 가운데에 있는 "추가>>" 를 이용하여 방금 만든 그룹에 추가를 해 보겠습니다.

자 이제 단추를 추가하는 기능은 완료가 되었습니다. 확인 버튼을 눌러 옵션 창을 닫고 엑셀의 리본메뉴를 살펴보면 아래와 같이 단추가 추가되어 있을 것입니다.

이제 해당 버튼을 누르게 되면 엑셀 시트에 단추를 만들수 있게 됩니다.

만드는 방법은 아래와 같습니다. 

1. 단추 버튼을 누른다
2. 원하는 위치에 마우스를 클릭한뒤 드래그 하여 필요한 크기가 되면 마우스 버튼을 뗀다
3. 팝업으로 나오는 매크로 선택창에서 아까 만들어둔 스크립트를 선택해 준다.
4. 이미 만들어진 단추의 크기나 위치, 레이블을 수정하기 위하여는 마우스 우클릭으로 선택해주면 됩니다.

총 4개의 스크립트를 연결할 것이므로 4개의 스크립트를 차례로 연결해 보겠습니다

이제 위의 버튼부터 차례로 눌러 볼까요?

네 잘 동작되네요.

만약 스크립트를 수정해야 하면 해당 버튼을 마우스 우클릭한 뒤 "매크로 지정" 기능으로 이동하여 설정되어 있는 매크로를 다른 메뉴로 변경하거나 선택되어 있는 매크로에서 "편집" 버튼을 눌러주게 되면 해당 매크로 스크립트를 바로 편집할 수 있는 비주얼 베이직에디터가 나타나게 됩니다.

자 이렇게 해서 엑셀에 버튼을 추가하고 스크립트를 연결하는 방법을 알아 보았습니다.

설정창에서 단추 기능을 꺼내는 단계부터 처음하시는 분은 조금 복잡하다고 생각하실 수도 있겠는데요. 막상 해보시면 정말 간단한 작업이고 어렵지 않습니다.

또한 이렇게 단추로 만들어진 스크립트는 매우 실행하기 편리하고 직관적이죠. 

자 여러분도 이제 엑셀을 이용하여 툴을 만들어 보시는 겁니다.

도전해 보시지요.

이번 강좌에서는 좀 재미있는 코드를 소개해 드릴까 합니다.

바로 엑셀 VBA 를 이용하여 포토샵 자동화 하기! 

오~~ 이게 되냐구요? 네 ! 됩니다.

사실 스크립트나 자동화 작업을 통하여 어떤 일을 할 것인가를 찾아내는 것이 실제 코드를 작성하는 것보다 훨씬 어려운 일이랍니다. 막상 어느 정도 개발을 할 수 있게 되면 생각보다 많은 것을 할 수 있다는 것을 알게되죠.

자 오늘은 그런 의미에서 새로운 문을 한번 열어 보겠습니다.

포토샵은 크게 세가지 스크립트 언어를 지원합니다. 바로 JavaScript, Apple Script (맥에서 만 가능), Visual Basic (윈도우에서만 가능) 요렇게 세가지 입니다. 엑셀은 기본적으로 비주얼 베이직 에디터를 포함하고 있기 때문에 포토샵 스크립트인 비주얼 베이직 버전의 개발이 가능한 것이죠. 

비주얼 베이직 버전의 포토샵 스크립트도 많은 기능을 지원합니다. 자바스크립트 버전과 거의 동일합니다. 오히려 코딩 관점에서는 더 간단하기도 합니다.

 재미없는 이야기는 집어 치우고 바로 코드로 실습을 해보시죠.

엑셀을 켜고 Alt + F11을 눌러 비주얼 베이직 에디터 창을 열어줍니다. 그리고 Sheet1 페이지를 더블클릭해서 코딩을 위한 창을 하나 만든다음 아래의 내용을 입력해주세요.

Sub usingPhotoshop()

    Dim psApp As Object
    
    Set psApp = CreateObject("Photoshop.Application")
    
    psApp.Documents.Add

End Sub

물론 컴퓨터에 당연히 포토샵이 설치가 된 상태여야 겠죠?

F5 키를 눌러보면 쨔짠~ 아래와 같이 포토샵이 자동으로 켜지고 곧이어 새창이 하나 만들어 집니다.

신기 하신가요?

제가 제 블로그에서 포토샵 스크립트도 틈틈히 올리고 있는데요. 해당 카테고리에는 대부분 자바스크립트 기준의 스크립트를 올리고 있습니다. 사실 해당 카테고리에 올린 모든 내용은 VBA 로도 작성이 가능하답니다.

코드를 조금더 올려볼까요?

Sub usingPhotoshop()

    Dim psApp As Object
    Dim psdDoc
    
    Set psApp = CreateObject("Photoshop.Application")
    
    psApp.Documents.Add
    
    Cells(1, 1).Value = "Document Name"
    Cells(1, 2).Value = "가로"
    Cells(1, 3).Value = "세로"
    
    Set psdDoc = psApp.activeDocument
    
    Cells(2, 1).Value = psdDoc.Name
    Cells(2, 2).Value = psdDoc.Width
    Cells(2, 3).Value = psdDoc.Height
        
End Sub

 조금 길어졌지만 뭐 한줄 한줄 읽어 보면 어려울 것이 없습니다. 처음 짜시려면 막막하시겠지만 막상 짜놓은 코드를 읽어보면 간단합니다. 자꾸 이렇게 만들어진 코드를 보는 것이 중요합니다. 자꾸 보면서 따라 코딩을 하다보면 눈에 익게 되고 그 다음엔 손에 익게되죠. 일단 익숙해 지면 쉽게 자신만의 코드 작성이 되기 시작합니다.

내용을 보면 이렇습니다.

• 포토샵 어플리케이션이 될 변수와 PSD 다큐먼트를 지정할 변수를 선언해준다.
• photoshop.application 이라는 오브젝트를 생성한다 --> 이때 포토샵이 실행됨
• 실행된 포토샵에 새로운 다큐먼트를 추가한다.
• 엑셀 시트의 멘위 세줄에 각 항목의 이름을 기입한다.
• 현재 포토샵에서 활성화된 창을 변수에 담아준다.
• 활성화된 창의 이름과 가로 길이, 세로 길이를 두번째 줄에 차례로 입력해준다.

요런식이 되겠습니다. 간단하죠?

자 그럼 반대로 엑셀에서 지정한 이름과 크기로 포토샵 도큐먼트를 세팅해 볼까요? 아마 실제로는 이게 더 유용하겠죠?

코드는 이렇게 작성해 보겠습니다.

• 포토샵으로 새로운 다큐먼트를 만들어 준다
• 가로세로 크기를 지정한 크기로 Resize 한다
• 지정한 이름의 PSD 파일로 저장한다.

간단하죠? 코드를 볼까요? 아까랑 별반 차이가 없습니다.

Sub usingPhotoshop()

    Dim psApp As Object
    Dim psdDoc
    
    Set psApp = CreateObject("Photoshop.Application")
    
    psApp.Documents.Add
    
    Cells(1, 1).Value = "Document Name"
    Cells(1, 2).Value = "가로"
    Cells(1, 3).Value = "세로"
    
    Set psdDoc = psApp.activeDocument
    
    psdDoc.ResizeImage CInt(Cells(2, 2).Value), CInt(Cells(2, 3).Value), 72, 4
      
    psdDoc.SaveAs ("d:\" & Cells(2, 1).Value & ".psd")
    
End Sub

윗쪽까지는 다 동일하고요. 아래 두줄이 다릅니다.

바로 ResizeImage 라는 명령이 들어갔고요. 그 아래에는 다큐먼트의 이름이 되는 PSD 파일을 저장해주는 코드 입니다.

VBA 에서는 문자열을 합칠때 + 기호가 아닌 & 를 사용하는 것 알고 계시죠??

요렇게 하면 아래와 같이 만들어 진답니다. 사이즈 확인을 위하여 이미지 리사이즈 창을 통해 정보를 띄워 놓았습니다.

사실 이런 간단한 코드도 처음 작성하는 분에게는 막막하기만 합니다.

VBA 는 익숙치 JavaScript 나 C, C++, Java 와는 다른 좀 특이한 문장 형태를 갖고 있기 때문에 더 어렵게 느껴질 수 있습니다. 하지만 VBA 의 장점이라 할 수 있는 실시간 오류 리포팅 기능이 있지 않겠습니까? 코드 작성 중 잘못된 문법은 허용이 되지 않죠. 바로 경고창으로 알려 줍니다. 경고 창이 뜨지 않도록 잘 맞춰주면 코드가 완성이 됩니다.

그리고 ResizeImaze 뒤에 Cells(2,1).value 는 알겠는데 그 뒤에 오는 72 와 4 라는 숫자는 뭘까요?? 각각의 값은 위 이미지에서 볼 수있는데요. 바로 리사이즈시 사용하는 Resolution 정보와 아래 resample 옵션인 Bicubic 에 해당하는 옵션입니다. 

이런건 어떻게 알아내냐구요? 바로 레퍼런스죠. 

먼저 다큐먼트 개체로 진입합니다. 그다음 리사이즈를 해야 하니 resize 라는 용어 근처를 찾아봐야겠죠? 

자 ResizeImage 라는 명령을 찾았습니다. 바로 위에는 ResizeCanvas 라는 명령도 보이네요. 어쨌든 ResizeImage 아래에 어떤 옵션들을 적어야 하는지 설명이 되어 있습니다.

• ResizeImage
  • Width (숫자, 더블형 - 소수)
  • Height (숫자, 더블형 - 소수)
  • Resoluton (숫자, 더블형 - 소수)
  • ResampleMethode (PsResampleMethod)

위에 3개의 옵션은 아시겠죠? 입력해야 하는 데이터의 자료형 입니다. 소수라고 정의 되어 있으니 12.5 이런 값으로 입력이 가능합니다. 그런데 맨 아래는 뭔지 잘 모르시겠죠? 파란색으로 링크 표시가 있으니 클릭해서 들어가 보겠습니다.

이미지를 인터폴레이션 즉, 보간하기 위한방법이라고 나와있군요. 각 숫자별로 보간 방법이 나와 있구요. 일반적으로 포토샵에서 기본으로 설정되어 있는 옵션은 Bicubic 이며 4번에 해당됩니다. 제가 사용한 코드에 4 로 입력하였으니 Bicubic 으로 보간이 된 셈입니다. 

막간을 이용하여 간단하에 각 옵션을 설명 드리자면, (이번 기회에 알아두셔도 좋겠네요.)

• psNoResampling - 보간하지 않음
• psNearestNeighbor - 픽셀경계면의 색상을 섞지 않음 (지글지글거리게 됩니다. 안티 앨리어싱이 안먹은 느낌?)
• psBilinear - 리니어하게 보간을 합니다. 선형보간이라 하는데 그냥 평범한 보간 법이라 보시면 됩니다.
• psBicubic - 선형 보간에서 하나의 축을 더하여 보간을 한 것으로 좀더 자연스러운 보간이 됩니다. (추천)
• psBicubicSharper - bicubic 을 기본으로 하며 좀더 선명한 이미지를 얻을 수 있습니다.(축소할 때 추천)
• psBicubicSmoother - bicubic 을 기본으로 하며 좀더 부드러운 이미지를 얻을 수 있습니다. (확대할 때 추천)

요렇게 필요한 옵션으로 리사이즈를 하시면 됩니다. 보통 Bicubic 으로 하지만 저는 필요에 따라 옵션을 바꾸며 사용합니다. psNearestNeighbor 같은 경우는 어떨때 사용할까요? 3D 텍스처용 맵을 만들어 놓고 리사이즈가 필요할때 해당 옵션을 이용하여 alpha 채널을 리사이즈하면 알파채널 경계 영역이 흐릿해지는 현상을 막을 수 있습니다.

강좌가 좀 딴길로 샜습니다. ^^

끝으로 조금더 컨트롤 하는 예제를 보여드리고 마무리 하겠습니다.

• 지정한 크기와 이름을 갖도록 설정하고 싶다.
• 엑셀에서 배경 색상을 지정하고 싶다.
• 텍스트 레이어를 만들어서 추가하고 싶다.

요정도만 해볼까요?

코드를 작성해보면 아래와 같습니다.

Sub usingPhotoshop()

    Dim psApp As Object
    Dim psdDoc
    Dim bgColor
    Dim fontColor
    Dim cellColor
    Dim txtLayer
    
    Set psApp = CreateObject("Photoshop.Application")
    
    psApp.Documents.Add
    
    Set psdDoc = psApp.ActiveDocument
    
    psdDoc.ResizeImage CInt(Cells(2, 2).Value), CInt(Cells(2, 3).Value), 72, 4
    
    '셀 색상을 HEX 값으로 받아오기'
    cellColor = Right("000000" & Hex(Cells(2, 4).Interior.Color), 6)
    
    'bgColor 라는 SolidColor 개체를 만들어서 Hex -> DEC -> Doble 로 변환하여 적용하기'
    Set bgColor = CreateObject("Photoshop.SolidColor")
    bgColor.RGB.HexValue = cellColor
    
    bgColor.RGB.red = CDbl(WorksheetFunction.Hex2Dec(Right(cellColor, 2)))
    bgColor.RGB.green = CDbl(WorksheetFunction.Hex2Dec(Mid(cellColor, 3, 2)))
    bgColor.RGB.blue = CDbl(WorksheetFunction.Hex2Dec(Left(cellColor, 2)))
    
    '화면 전체 선택한 뒤 색상 칠하기'
    psdDoc.Selection.SelectAll
    psdDoc.Selection.Fill bgColor
    
    '폰트 색상용 색상 개체 만들어 색상 설정하기'
    cellColor = Right("000000" & Hex(Cells(2, 5).Interior.Color), 6)
    
    Set fontColor = CreateObject("Photoshop.SolidColor")
    fontColor.RGB.red = CDbl(WorksheetFunction.Hex2Dec(Right(cellColor, 2)))
    fontColor.RGB.green = CDbl(WorksheetFunction.Hex2Dec(Mid(cellColor, 3, 2)))
    fontColor.RGB.blue = CDbl(WorksheetFunction.Hex2Dec(Left(cellColor, 2)))
    
    '텍스트 레이어 추가해주기'
    Set txtLayer = psdDoc.artlayers.Add
    txtLayer.kind = 2
    txtLayer.Name = "my Text"
    
    '만들어진 텍스트 레이어에 텍스트 추가하기'
    Set txtItem = psdDoc.artlayers("my Text").textitem
    txtItem.Font = "MalgunGothicBold"
    txtItem.Size = 36
    txtItem.Color = fontColor
    txtItem.contents = Cells(2, 6).Value
    
    'PSD 파일로 저장하기'
    psdDoc.SaveAs ("d:\" & Cells(2, 1).Value & ".psd")
    
End Sub

와우 엄청 길어졌네요. 순서대로 주석을 읽어 보시면 내용은 이해가 되실텐데요. 새로운 명령어 들이 많이 나왔죠? 한번 실습한다 생각하시고 따라서 코딩을 해보신 다음 찬찬히 생각해보시면 이해가 되실거에요. 좀 생소한 코드도 있기는 하지만 어렵지는 않습니다. 실행해보면 아래와 같이 됩니다.

샘플 코드 만드는데 생각했던 것보다 오려걸렸네요. 꼭 필요하신 분에게 도움이 되었으면 합니다.

위에 저는 단순히 한줄만 제가 필요한 내용을 엑셀에 기재하여 PSD 파일을 만들었지만 저런 조건들이 이제 수백개가 되어도 자동으로 모두 만들 수 있는 여건이 되었습니다. 반복 문을 조금 응용하면 되겠죠?

이미지를 엑셀로 붙여넣은 포스트 기억나시나요? 해당 이미지를 열어서 셀 크기로 저장한뒤 다시 붙여 넣는 것도 가능하겠고, 여러 이미지를 하나의 포토샵에 레이어로 추가하는 것도 가능하겠으며 이미지에 파일명을 텍스트로 추가하는 것도 역시 쉽게 가능할 것 같습니다.

어렵다고 생각 마시고 바로 도전해 보시기 바랍니다.

엑셀로 포토샵 자동화 가능?

네. 쌉가능입니다. ㅋ

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

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

칼퇴근 쌉가능

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

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

*** 개발에 관심 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] - 재택근무 필수 엑셀 "자리안비움" - 윈도우 꺼짐 방지

이번 포스트를 통해 꼬마아이들의 공공의적 이 될런지도 모릅니다.

바로 초등 저학년 연산문제를 끝도 없이 만들수 있는 파일을 준비했기 때문입니다. 지난번 포스트에서 연산문제를 만드는 VBA 를 소개해 드렸었는데요.

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

해당 포스트로 정말 문제를 내려면 VBA를 작성하셔야만 자동으로 문제를 내는것이 가능하죠.

그런데 사실 제 블로그를 보시는 분들이 다 실제 개발을 하지는 않지 않겠습니까? 그래서 만들어진 엑셀 파일을 그냥 공유하려고 합니다. 물론 업로드하는 엑셀 파일에 사용된 코드에 대한 설명도 곁들여 보겠습니다.

지난번에 올린 것에 조금 업데이트 된 내용은 아래와 같습니다.

• 버튼을 이용하여 문제를 바로 업데이트 할 수 있도록 할 것
• 더한 결과의 합이 10 이상인 문제도 낼 수 있을 것 (원하는 경우에만)
• 10의 자리와 1의 자리 덧셈을 낼 수 있을 것
• 문제의 난이도를 사용자가 설정할 수 있을 것

요정도의 기능을 수행할 수 있는 버전으로 만들어 보았습니다.

복잡한 코드에 관심이 없으신 분은 바로 첨부파일 다운로드 및 공감 부탁드립니다. ㅋ 

맘카페에 퍼날라 주셔도 감사하겠습니다. 비싼 학습지 신청하지 마시고 사랑하는 마음으로 직접 내주시길 바랍니다.

먼저 파일을 여시게 되면 아래와 같은 경고창이 나올 수 있습니다.

제가 만든 매크로가 동작해야 하므로 콘텐츠 사용을 클릭해 주세요. 위험하지 않으니 걱정하지 않으셔도 됩니다.

우측의 버튼은 엑셀이 업데이트 되면서 기본 기능에서는 빠진 기능을 이용해야 만들 수 있습니다. 기본적으로는 숨겨져 있기 때문에 설정 항목에서 단추를 만드는 기능을 꺼내야 합니다.

나중에 버튼(단추)을 만드는 예제는 따로 준비해서 올리겠습니다. 

VBA 에디터에 각 버튼에 해당되는 함수를 만든 뒤에 각 버튼에 만들어놓은 함수를 연결하는 방식으로 작업이 되었습니다. 코드를 보면 아래와 같습니다. 버튼별로 함수를 만들었기 때문에 조금 길이가 있습니다.

Sub makeMath_under10()
    
    Dim aSht As Worksheet
    Dim rngA As Range
    Dim numA As Integer
    Dim numB As Integer
    
    Set aSht = ActiveSheet
    Set rngA = [B3]
    
    Randomize
    
    
    For i = 0 To 8
        
        numA = Int(Rnd * 9) + 1

        Do
            numB = Int(Rnd * 9) + 1
        
        Loop While (numA + numB > 10)
        
        rngA.Offset(i * 2, 0).Value = numA
        rngA.Offset(i * 2, 1).Value = "+"
        rngA.Offset(i * 2, 2).Value = numB
        rngA.Offset(i * 2, 3).Value = "="
        
    Next i
    
    For i = 0 To 8
        
        numA = Int(Rnd * 9) + 1

        Do
            numB = Int(Rnd * 9) + 1
        
        Loop While (numA + numB > 10)
        
        rngA.Offset(i * 2, 6).Value = numA
        rngA.Offset(i * 2, 7).Value = "+"
        rngA.Offset(i * 2, 8).Value = numB
        rngA.Offset(i * 2, 9).Value = "="
        
    Next i
    
End Sub
Sub makeMath_over10()
    
    Dim aSht As Worksheet
    Dim rngA As Range
    Dim numA As Integer
    Dim numB As Integer
    
    Set aSht = ActiveSheet
    Set rngA = [B3]
    
    Randomize
    
    aSht.Cells(1, 12).Value = Date
    
    For i = 0 To 8
        
        numA = Int(Rnd * 9) + 1

        Do
            numB = Int(Rnd * 9) + 1
        
        Loop While (numA + numB < 10)
        
        rngA.Offset(i * 2, 0).Value = numA
        rngA.Offset(i * 2, 1).Value = "+"
        rngA.Offset(i * 2, 2).Value = numB
        rngA.Offset(i * 2, 3).Value = "="
        
    Next i
    
    For i = 0 To 8
        
        numA = Int(Rnd * 9) + 1

        Do
            numB = Int(Rnd * 9) + 1
        
        Loop While (numA + numB < 10)
        
        rngA.Offset(i * 2, 6).Value = numA
        rngA.Offset(i * 2, 7).Value = "+"
        rngA.Offset(i * 2, 8).Value = numB
        rngA.Offset(i * 2, 9).Value = "="
        
    Next i
    
End Sub

Sub makeMath_under100_easy()
    
    Dim aSht As Worksheet
    Dim rngA As Range
    Dim numA As Integer
    Dim numB As Integer
    
    Set aSht = ActiveSheet
    Set rngA = [B3]
    
    Randomize
    
    aSht.Cells(1, 12).Value = Date
    
    For i = 0 To 8
        
        numA = Int(Rnd * 20) + 1

        Do
            numB = Int(Rnd * 10) + 1
        
        Loop While (numA + numB > 100)
        
        rngA.Offset(i * 2, 0).Value = numA
        rngA.Offset(i * 2, 1).Value = "+"
        rngA.Offset(i * 2, 2).Value = numB
        rngA.Offset(i * 2, 3).Value = "="
        
    Next i
    
    For i = 0 To 8
        
        numA = Int(Rnd * 20) + 1

        Do
            numB = Int(Rnd * 10) + 1
        
        Loop While (numA + numB > 100)
        
        rngA.Offset(i * 2, 6).Value = numA
        rngA.Offset(i * 2, 7).Value = "+"
        rngA.Offset(i * 2, 8).Value = numB
        rngA.Offset(i * 2, 9).Value = "="
        
    Next i
    
End Sub
Sub makeMath_under100_normal()
    
    Dim aSht As Worksheet
    Dim rngA As Range
    Dim numA As Integer
    Dim numB As Integer
    
    Set aSht = ActiveSheet
    Set rngA = [B3]
    
    Randomize
    
    aSht.Cells(1, 12).Value = Date
    
    For i = 0 To 8
        
        numA = Int(Rnd * 90) + 1

        Do
            numB = Int(Rnd * 10) + 1
        
        Loop While (numA + numB > 100)
        
        rngA.Offset(i * 2, 0).Value = numA
        rngA.Offset(i * 2, 1).Value = "+"
        rngA.Offset(i * 2, 2).Value = numB
        rngA.Offset(i * 2, 3).Value = "="
        
    Next i
    
    For i = 0 To 8
        
        numA = Int(Rnd * 90) + 1

        Do
            numB = Int(Rnd * 10) + 1
        
        Loop While (numA + numB > 100)
        
        rngA.Offset(i * 2, 6).Value = numA
        rngA.Offset(i * 2, 7).Value = "+"
        rngA.Offset(i * 2, 8).Value = numB
        rngA.Offset(i * 2, 9).Value = "="
        
    Next i
    
End Sub
Sub makeMath_under100_hard()
    
    Dim aSht As Worksheet
    Dim rngA As Range
    Dim numA As Integer
    Dim numB As Integer
    
    Set aSht = ActiveSheet
    Set rngA = [B3]
    
    Randomize
    
    aSht.Cells(1, 12).Value = Date
    
    For i = 0 To 8
        
        numA = Int(Rnd * 90) + 1

        Do
            numB = Int(Rnd * 90) + 1
        
        Loop While (numA + numB > 100)
        
        rngA.Offset(i * 2, 0).Value = numA
        rngA.Offset(i * 2, 1).Value = "+"
        rngA.Offset(i * 2, 2).Value = numB
        rngA.Offset(i * 2, 3).Value = "="
        
    Next i
    
    For i = 0 To 8
        
        numA = Int(Rnd * 90) + 1

        Do
            numB = Int(Rnd * 90) + 1
        
        Loop While (numA + numB > 100)
        
        rngA.Offset(i * 2, 6).Value = numA
        rngA.Offset(i * 2, 7).Value = "+"
        rngA.Offset(i * 2, 8).Value = numB
        rngA.Offset(i * 2, 9).Value = "="
        
    Next i
    
End Sub

요렇게 코드가 모두 작성이 되셨다면

단추마다 매크로를 연결해 줍니다.

이런식으로 함수를 연결해 주면 됩니다.

어렵지 않죠?

엑셀에서 버튼을 이용하여 매크로를 실행시키는 예제는 아래 포스트를 참고해 주세요.

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

2020/06/22 - [DEV/VBA] - [vba] 초등학교 연산 자동 문제집 - 곱셈 추가

안녕하세요. 이번 강좌에서는 VBA 를 이용하여 초등학교 저학년 아이를 위한 연산문제를 자동으로 내주는 스크립트를 작성해 보겠습니다. VBA 를 좀 해봤다 하면서 막상 일상 생활에 사용하려면 사용할데가 마땅히 떠오르지 않죠? 초등학교 저학년 자녀가 있으신 분은 수학 연산 학습지를 한번 살펴보세요. 별거 없습니다. 간단한 문제 들이 그저 나열되어 있을 뿐이죠.

이제 우리는 VBA 를 조금 다룰 줄 알게 되었기 때문에 VBA 를 이용하여 사랑하는 아이를 위한 연산 문제를 만들어주는 스크립트를 작성해 보려고 합니다.

현재 활성화된 시트와 특정 영역을 설정하는 방법은 앞선 포스트에서 다루었으니 해당 포스트를 참고하시면 되고요. 

오늘 작성할 포스트에서 새로 나타나는 특별한 함수, 반복문이 있습니다. 

• Randomize / Rnd : 임의의 숫자를 만들어 내는 함수
• Int : 소수형 숫자를 정수형으로 변경해주는 함수
• Do ... While : 어떤 조건이 만족할 때까지 계속해서 반복실행을 하는 함수

아래와 같은 기능을 개발할 예정입니다.

• 현재 선택된 시트에 동작하도록 한다.
• 두개의 임의의 숫자를 만들어 지정한 칸에 붙인다.
• 만약 두개의 숫자의 합이 10이 넘는다면 10이 넘지 않는 두번째 숫자를 구한다. (쉬운버전)
• 첫번째 숫자 뒤에는 "+" 기호를 넣고 두번재 숫자 뒤에는 "=" 기호를 넣는다.

자 먼저 함수를 정의하고 입력할 시트와 칸을 지정, 그리고 숫자 두개를 지정하겠습니다. 감이 오시나요?

Sub makeMath_under10()
    
    Dim aSht As Worksheet
    Dim rngA As Range
    Dim numA As Integer
    Dim numB As Integer
    
    Set aSht = ActiveSheet
    Set rngA = [B3]
    
End sub

 요렇게요.

아직 아무런 동작도 실행되지는 않지만 필요한 변수는 다 설정했고 입력할 시트와 셀도 지정을 했습니다.

자 이제 한번에 갑니다. 조금 복잡할수도 있지만 잘 보세요~ ( '로 시작하는 문장은 주석입니다. )

Sub makeMath_under10()
    
    Dim aSht As Worksheet
    Dim rngA As Range
    Dim numA As Integer
    Dim numB As Integer
    
    Set aSht = ActiveSheet
    Set rngA = [B3]
    
    ' 이 함수에서 난수를 사용할 것임을 선언함'
    Randomize
    
    'Rnd 라는 함수에서 난수 0.00001 ~ 0.99999 사이의 숫자를 생성'
    '만들어진 9 이하의 숫자를 정수로 변경'
    '최종 만들어진 숫자에 1 을 더함 : 0이 나올수 있기 때문에'
    '만들어진 숫자를 numA 에 적용함'
    numA = Int(Rnd * 9) + 1
	
    'Do 와 Loop 사이의 수식을 While 뒤에 나오는 조건이 만족하는 동안 계속 반복함'
    Do
    	numB = Int(Rnd * 9) + 1
    Loop While (numA + numB > 10)
    
    
    'rngA 를 기준으로 우측으로 각 칸마다 필요한 값을 입력'
    rngA.Offset(0, 0).Value = numA
    rngA.Offset(0, 1).Value = "+"
    rngA.Offset(0, 2).Value = numB
    rngA.Offset(0, 3).Value = "="
    
End sub

주석으로 설명을 했습니다만 어려울 수 있는 부분만 추가 설명을 하겠습니다.

Int( 숫자 ) 라는 함수는 '숫자' 에 해당하는 어떤 수 도 정수형으로 변경해 주는 함수 입니다. 

Rnd 를 통해 만들어진 소수형 숫자에 9를 곱해 최대 9가 나올수 있도록 범위를 확장해 준 뒤 Int 라는 함수를 이용하여 정수로 변경해주는 문장입니다. 만약 Rnd 에서 0.00001 이런 값이 나오는 경우 1 미만의 값이 나올 수 있기 때문에 제일 마지막에 + 1 을 해주어 절대로 0은 나오지 않도록 작성하였습니다.

그 뒤에 나오는 Do ... Loop While 부분이 조금 어려우실 수도 있겠는데요. 조금 생각해보면 그다지 어렵지는 않습니다.

Do 와 Loop 사이에 어떤 문장을 계속 반복하라는 뜻인데요. 이 반복은 어떤 조건이 만족할때만 반복시키게 됩니다. 여기에서는 (numA + numB > 10) 이라는 조건을 걸었죠. 말하자면 Do Loop 사이에서 구해진 numB 라는 값과 그 전에 구해진 numA 라는 값을 더했을 때 10이 넘는다면 numB 를 다시 구하도록 하는 역할을 합니다. 만약 두 숫자를 더했을 때 10이 넘지 않는 다면 반복문은 종료 되고 그 다음 줄로 빠져나오게 됩니다. 이렇게 하면 numA 와 numB를 더했을 때 절대로 10이 넘지 않게 됩니다. 저희 둘째가 아직 어려서 10 넘는 더하기는 무리이기 때문에 10이 넘지 않도록 하였지만 만약 10이 넘는 덧셈이 필요하시면 해당 부분을 제거하시면 됩니다. 

조금 응용을 해보시면 반드시 10이 넘는 계산만 만드시는 것도 가능하겠죠?

위의 스크립트를 실행하면 아래와 같이 작성이 됩니다.

전 한 페이지에 문제를 좀더 내려고 합니다. For 문을 이용해서 반복적으로 문제를 계속해서 만들어 나갈 겁니다.

Sub makeMath_under10()
    
    Dim aSht As Worksheet
    Dim rngA As Range
    Dim numA As Integer
    Dim numB As Integer
    
    Set aSht = ActiveSheet
    Set rngA = [B3]
    
    Randomize
        
    For i = 0 To 8
        
        numA = Int(Rnd * 9) + 1

        Do
            numB = Int(Rnd * 9) + 1        
        Loop While (numA + numB > 10)
        
        rngA.Offset(i * 2, 0).Value = numA
        rngA.Offset(i * 2, 1).Value = "+"
        rngA.Offset(i * 2, 2).Value = numB
        rngA.Offset(i * 2, 3).Value = "="
        
    Next i
    
    For i = 0 To 8
        
        numA = Int(Rnd * 9) + 1

        Do
            numB = Int(Rnd * 9) + 1        
        Loop While (numA + numB > 10)
        
        rngA.Offset(i * 2, 6).Value = numA
        rngA.Offset(i * 2, 7).Value = "+"
        rngA.Offset(i * 2, 8).Value = numB
        rngA.Offset(i * 2, 9).Value = "="
        
    Next i
    
End Sub

요렇게 작성했습니다. 이렇게 만들고 코드를 실행하면 아래와 같은 한페이지에 총 16 문제가 들어있는 문제집이 만들어 집니다.

그런데 모양이 보기에 좋지 않죠? 음...

이제 함수를 실행할때 마다 문제가 새로 만들어 질 건데요. 그 전에 화면을 좀 다듬어 봐야 겠네요.

출력할 용지도 고려하고 아이가 볼 문제니 글자 크기도 좀 키우고 해야 겠죠?

최종으로 만들어진 화면입니다.

스크립트를 실행할 때 마다 문제가 계속 바뀌니 항상 새로운 문제를 낼 수 있게 되었습니다.

물론 아이가 좋아할지는 미지수입니다.

숫자를 쓴걸 보니 그다지 재미는 없나 보네요. ( 0 이 아니고 10을 쓴거네요. 1이 아주 작게 ㅋ)

그럼 이만~

2020/04/17 - [DEV/VBA] - [vba] 초등학교 연산 문제 자동 출제, 이번엔 뺄셈에 도전

2019/09/10 - [DEV/VBA] - [VBA] 엑셀로 초등학교 저학년 문제집 만들기 (자동버전)

요이번 강좌는 에셀 시트의 모든 셀의 색상을 한번에 제거하는 방법을 알려 들리려고 합니다. 강좌라고 할것도 없을 만큼 간단한 스크립트인데요. select all 한다음 셀 색상을 빼내도 되긴 하겠지만 자동화 과정 중에 셀 색상을 초기화 해야 하는 경우가 있다면 아래의 스크립트를 이용하여 모두 초기화 시킨 후 자동화 과정을 수행할 수 있습니다.

Sub crearCellColor()

    Cells.Interior.Color = xlNone

End Sub

간단하죠?

이렇게 특정 시트에 대한 지정없이 사용하는 것은 특정 시트에 바로 스크립트를 작성하는 경우인데요. ThisWorkBook 과 같은 공용 위치에서 스크립트를 작성할 때는 색상을 초기화 해줄 시트를 지정해 주어야 합니다.

Sub crearCellColor()

    Dim aSht As Worksheet
    
    Set aSht = ActiveSheet
    
    aSht.Cells.Interior.Color = xlNone

End Sub

요렇게 상단에 worksheet 개체를 정의 하여 주고 해당 시트가 현재 활성화 되어 있는 시트임을 알려준뒤 해당 시트에서 실행을 하면 됩니다.

선택된 영역 또는 특정영역에 컬러는 채워넣을 때는 RGB(255,255,255) 와 같은 함수를 이용합니다. 만약 셀에 노란색을 채워 넣으려면 아래와 같이 하면 됩니다.

Sht.Cells(2, 3).Interior.Color = RGB(255, 255, 0)

셀에 여러가지 방법으로 색상을 채워넣는 강좌는 아래 포스트를 참고해주세요~

https://diy-dev-design.tistory.com/32?category=791049

또한 셀의 영역을 설정하는 방법은 아래 포스트를 참고해 주세요

https://diy-dev-design.tistory.com/20?category=791049

셀의 테두리의 속성을 지정하거나 숨기기, 또는 숨김 해제가 필요한 경우 아래 포스트를 참고해주세요

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

이상으로 이번 포스트를 마치겠습니다.

감사합니다.