엑셀, 곽승주님의 오튜공구함 제작으로 배워보는 VBA 이야기, Excel

안녕하세요. 오늘은 통합문서와 관련한 네 가지의 매크로를 소개할 차례입니다. 보시면 알겠지만 사실 따지고 보면 두 가지 입니다. 모든 통합문서를 닫는 경우 이를 자동으로 저장할지 아니면 모두 저장하지 않을 것인지, 일일이 사용자의 확인을 받을 것인지를 만들어 보았습니다. 기능이 조금씩 다른 만큼 코드도 조금씩 다릅니다(핵심은 같지만)

코드는 다음과 같습니다.
‘모든 통합문서를 저장하고 닫는 프로시져
Sub AllCloseWithSaving()
     Dim wbk       As Workbook

     Application.DisplayAlerts = False
각종 엑셀의 경고메시지를 보이지 않도록 합니다. 이를 False로 하는 이유는 저장하고 닫는 과정에서 필요없는 메시지가 나오는 것을 막기 위한 것입니다.
     For Each wbk In Application.Workbooks
          If Not (wbk Is ThisWorkbook) Then
               wbk.Save
통합문서를 저장합니다.
              wbk.Close
통합문서를 닫습니다.
          End If
     Next
     Application.DisplayAlerts = True
End Sub

‘모든 통합문서를 저장하지 않고 닫는 프로시져
Sub AllCloseWithoutSaving()
     Dim wbk As Workbook

     Application.DisplayAlerts = False
     For Each wbk In Application.Workbooks
          If Not (wbk Is ThisWorkbook) Then
               wbk.Saved = True
저장여부를 True로 줌으로써 통합문서가 저장된 것처럼 합니다. 이렇게 닫는 경우(Close)실제로 저장하지 않고 닫는 결과가 됩니다.

               wbk.Close
          End If
     Next
     Application.DisplayAlerts = True
End Sub

‘모든 통합문서를 사용자의 확인을 받고 저장 또는 그냥 닫는 프로시져
Sub AllCloseWithConfirm()
     Dim wbk As Workbook

     Application.DisplayAlerts = True
일일이 사용자의 확인을 받고 닫으려는 경우 DisplayAlerts속성을 True로 합니다.
     For Each wbk In Application.Workbooks
          If Not (wbk Is ThisWorkbook) Then
               wbk.Close
          End If
     Next
End Sub

위의 코드를 보시면 다음과 같은 공통된 루틴이 있습니다.
For Each wbk In Application.Workbooks
     If Not (wbk Is ThisWorkbook) Then
          ...
     End If
Next
wbk라는 Workbook개체변수를 선언하고 Application.Workbooks 컬렉션을 루프돌면서 Workbook을 닫습니다. 이때 닫으려는 통합문서가 오튜공구함(ThisWorkbook)이 아니면 닫습니다. 이 개체가 저 개체와 같은 것인지를 확인하는 연산자는 여러분들에게 익숙한 ‘=’이 아니라 Is입니다. 도움말을 보면 Is에 대해 다음과 같이 설명하고 있습니다.

Is 연산자
두 개체가 가리키는 변수가 동등한지를 비교합니다.
result = object1 Is object2
Is 연산자 구문은 다음과 같은 구성 요소로 되어 있습니다.

Is 연산자 예제
아래 예제에서는 Is 연산자를 사용하여 두 개의 개체 참조를 비교합니다. 개체 변수 이름은 일반적인 이름이며, 예를 들기 위해서만 사용합니다.

Dim MyObject, YourObject, ThisObject, OtherObject, ThatObject, MyCheck
Set YourObject = MyObject ' 개체 참조를 지정합니다.
Set ThisObject = MyObject
Set ThatObject = OtherObject

MyCheck = YourObject Is ThisObject ' True를 반환합니다.
MyCheck = ThatObject Is ThisObject ' False를 반환합니다.

' MyObject를 가정합니다 <> OtherObject
MyCheck = MyObject Is ThatObject

이번엔 현재의 통합 문서를 바탕화면에 바로 가기 키를 만드는 프로시져를 만들어 볼 차례입니다. 바탕화면에 바로 가기 키를 만드는 기능은 VBA나 엑셀에 없는 기능입니다. 눈치가 빠른 분들은 짐작하시겠지만 WSH(Windows Script Host)라는 개체를 참조해야 합니다. (참조를 이것 저것 하다 보니 참조가 깨지거나 프로젝트가 무거워지는 느낌이 있습니다. 개인적으로 사용할 목적이라면 참조가 많아도 상관없지만 상용인 경우 되도록 참조를 줄여야 에러확률이 적을 것입니다)

제가 이 낯선 WSH를 소개하는 이유는 오피스와 관련하여 많은 일을 할 수 있을 것 같아 보여서입니다( 역시 능숙하게 WSH를 사용하는 처지는 아닙니다 )
먼저 WSH에 대한 개요를 말씀 드리는 것이 순서일 것 같군요. 다음은 제가 예전에 써둔 WSH에 대한 글을 옮긴 것입니다.

번호 #3 /7 날짜 2001년1월8일(월요일) 13:50:53
이름 곽승주 제목 [WSH] 아직 생소한 WSH

WSH라는 단어를 들어 보신 적 있나요? 오늘은 아직 생소한 느낌이 있지만 향후 윈도우 프로그래밍의 감초역할을 할 것으로 기대되는 WSH(Windows Script Host,이하 WSH)에 대해 제가 아는대로 적어 볼까 합니다.

WSH는 VB스크립트, J스크립트등을 사용하여 윈도우와 윈도우 애플리케이션의 오브젝트모델에 접근할 수 있는 언어중립환경입니다. 즉 언어는 아니지만 지원하는 언어가 실행가능하게 만들어 주는 역할을 합니다.

이런 부분들은 NT계열의 OS에서 웹애플리케이션을 만들 때 사용하는 ASP와 비슷한 것 같군요. 아뭏튼 인터넷익스플로러, 엑셀, 메모장 등 자동화인터페이스를 가진 애플리케이션은 WSH를 통해 제어할 수 있습니다.

유닉스에는 쉘스크립트, 맥OS에는 애플스크립트, 도스에는 배치파일이 있지만 그동안 윈도우에는 자체적인 스크립트언어가 없었습니다. 제 개인적으로는 머나먼 도스시절에 4DOS셀을 가지고 파일을 관리하는 배치파일을 만들어 본 적이 있었죠. 그 당시엔 노턴커맨더를 흉내내려고 무진장 애를 썼습니다. 이제 윈도우에서 그보다 더 강력한 WSH가 생겼습니다. 도스의 배치파일은 제어할 수 있는 기능이 제한적이지만 WSH는 시스템레벨까지 접근할 수 있습니다.

WSH에 대해 자세히 알고 싶으신 분들은 마이크로소프트사의 홈페이지(http://www.microsoft.com/korea/ntserver/management/
Techdetails/TechSpecs/WSH.asp)를 방문해보시길 바랍니다. 프로그램은 (http://www.microsoft.com/msdownload/vbscript/scripting.asp)에서 다운 받으셔도 돼지만 현재 사용중인 OS가 windows98,WindowsNT4.0(Option Pack4가 설치된), Windows2000이라면 WSH가 이미 설치되어 있을 겁니다.

어떻습니까? WSH에 대해 어느 정도 감을 잡으셨나요? 다음은 WSH개체를 사용하여 바로가기키를 만드는 함수를 소개합니다.

Function CreateShortcutOnDesktop(strFullFilePathName As String) As Long

    Dim WSHShell       As IWshRuntimeLibrary.WSHShell
WSHShell를 WSHShell 개체변수로 선언하였습니다.
     Dim WSHShortcut      As IWshRuntimeLibrary.IWshShortcut_Class
IWshShortcut_Class개체는 바로가기아이콘을 위한 개체입니다.

     Dim strDesktopPath As String
     Dim strFileName As String
     Dim strPath As String

     On Error GoTo fCreateShortcutOnDesktop_Err

     ' Create a Windows Shell Object

여기에서는 WSHShell 개체변수에 개체를 저장하는 부분입니다. 그런데 문제가 하나 있더군요 WSH가 버전이 바뀌면서 개체모델이 변경된 것 같더군요. 그래서 WSH 버전이 1.0인 경우 다음과 같이 합니다.
     Set WSHShell = New IWshRuntimeLibrary.IWshShell_Class
아니라면 다음과 같이 합니다.
     Set WSHShell = New IWshRuntimeLibrary.WSHShell

     ' Get the file's name and path...
   strFileName = Dir(strFullFilePathName)
     strPath = Left(strFullFilePathName, _
          Len(strFullFilePathName) - Len(strFileName))
넘겨받은 매개변수에서 파일의 이름과 경로를 분리하여 저장합니다.

     ' Make sure file exists
     If Not Len(strFileName) = 0 Then

          ' Read desktop path using WshSpecialFolders object
          strDesktopPath = WSHShell.SpecialFolders.Item("Desktop")
WSHShell개체에서 스페셜폴더중 하나인 바탕화면을 이름을 얻습니다.

          ' Create a shortcut object on the desktop
          Set WSHShortcut = WSHShell.CreateShortCut _
              (strDesktopPath & "\" & strFileName & ".lnk")
그 바탕 화면 이름에 파일이름 + “.lnk”를 붙여 바로 가기 키 파일을 만듭니다. 윈도우탐색기를 유심히 보신 분들은 아시겠지만 바탕화면의 각종 아이콘은 폴더에 lnk파일로 저장되어 있습니다. 윈도우버전에 따라 저장되는 폴더위치는 다릅니다. 가령 Win9x버전인 경우 “\Windows\바탕 화면” 이라는 폴더에 저장되고 Win2K인 경우 “\Documents and Settings\로그인명\바탕 화면”이라는 폴더에 저장됩니다.

     ' Set shortcut object properties and save it
     With WSHShortcut
          .TargetPath = WSHShell. _
               ExpandEnvironmentStrings(strFullFilePathName)
          .WorkingDirectory = WSHShell. _
               ExpandEnvironmentStrings(strPath)
          .WindowStyle = 4
          .IconLocation = WSHShell. _
               ExpandEnvironmentStrings(Application.Path _
                           & "\excel.exe ,0")
          .Save
위에서 TargetPath, WorkingDirectory, WindowStyle, IconLocation와 같은 프로퍼티는 다음 그림과 같이 [등록 정보]대화 상자의 [대상위치], [시작위치], [실행], [아이콘 변경] 버튼 클릭시 [아이콘 변경]대화 상자의 [파일 이름] 에 해당합니다.

          End With
          CreateShortcutOnDesktop = 1

     Else
          CreateShortcutOnDesktop = 0
     End If

Continue:
     ' Tidy Up
     Set WSHShell = Nothing
     Exit Function

fCreateShortcutOnDesktop_Err:
     CreateShortcutOnDesktop = -1
     Resume Continue
End Function

Public Sub CreateShortCut()
     If CreateShortcutOnDesktop(ActiveWorkbook.Path & "\" & ActiveWorkbook.Name) = 1 Then
          MsgBox "바탕 화면에 단축 아이콘을 만들었습니다.", vbInformation
     Else
          MsgBox "바탕 화면에 단축 아이콘을 만들지 못했습니다.", vbInformation
     End If
End Sub
현재의 통합문서 바로 가기를 바탕 화면에 저장하는 프로시져입니다.
　

오늘은 여기까지 입니다. 다음 시간에 수식과 관련한 내용을 살펴보도록 하겠습니다.

목차 | 이전 | 다음