'vba'에 해당되는 글 1건

  1. 2008.06.29 MS 프로젝트와 Excel 연동

프로젝트 관리를 하다보면 아주 많은시간을 반복적이고, 단순하지만 시간이 많이 걸리는 문서작업을 해야 한다.

지금 참여하고 있는 프로젝트를 예를 들면 일주일에 세번 팀, 부서, 본부 프로젝트 보고를 해야 한다. 프로젝트 보고를 위해서는 요구사항 및 진척률과 같은 지표를 관리해서 보고서를 작성해야 한다. 이러한 작업을 하기 위해서 많은 노력들을 하고 경우에 따라서는 시스템을 구성하는 경우도 있지만, 일반적으로는 MS프로젝트, 엑셀, 파워포인트를 이용해서 작업을 한다.

큰 프로젝트 일수록 제출 문서에 대한 표준화가 되어 있어서 그러한 표준화된 양식에 따라서 작업을 진행해야 하는데, MS 프로젝트로 관리되는 일정과 엑셀로 관리되는 요구사항 추적 매트릭스를 일치시키고 작업(WBS)에 자원을 할당하다 보면 하루가 부족할 지경이다. 특히 요구사항의 수가 300개를 넘어간 이후 진행 상황을 업데이트 하는 일이 고역이 되고 있다. 게다가 최종 상태만 알고 있으면 되는 것이 아니라 일주일 단위로 추가된 요구사항과 완료된 요구사항을 알아야 하는 있이 종종 발생했다.

그래서 여러가지 고민을 하다가 VBA를 이용해서 MS프로젝트와 Excel을 연동해 보기로 했다.

먼저 Visual Basic Editor를 열어야 한다. Excel이나 MS 프로젝트에서 Alt-F11 키로 에디터를 실행할 수 있다.

그 다음으로는 마우스 오른쪽 버튼을 눌러서 모듈을 삽입하여야 한다.

프로그래밍하면서 필요한 부분은 도움말을 열어서 찾아보면 되는데 베이직을 코딩해본지 10년은 지난 것 같아서 문법에 대한 부분이 기억이 가물가물 했다. 이번 작업을 하면서 사용한 문법은 다음과 같다.

1. 변수 선언 : Dim 변수명 As 변수타입
(예) Dim TaskName As String
        Dim objTask As Object
        Dim idx As Integer

2. 배열 선언 : Dim 변수명() As 변수타입
(예) Dim strReqID() As String

3. 배열 재할당 : ReDim 변수명(배열크기)
(예) ReDim strReqID(nWBSCount)

4. For Loop
For 시작값 To 종료조건
    ....
Next

(예)
    For idx = 1 To nWBSCount
        total = total + idx
    Next idx

5. If, Else If, Else
If 조건 Then
   ...
Else If 조건 Then
   ...
Else
   ...
End If

(예)
    If value = 100 Then
        MsgBox "완료"
    Else If value > 0 And value < 100 Then
        MsgBox "진행중"
    Else
        MsgBox "시작안됨"
    End If

Excel에서 MS Project를 접근하기 위해서는 다음과 같은 코드가 필요하다.

Set pj1 = CreateObject("MSProject.Project")
pj1.Application.FileOpen "C:\요구사항_20080626.mpp"
Set pj = pj1.Application.ActiveProject

MS Project의 개체 구조는 MS Project의 Visual Basic Editor 도움말을 참고하면 되는데 다음과 같다.
 
여기서는 Tasks 컬렉션 개체만 사용하면 된다. (Tasks개체는 MS 프로젝트의 작업에 대한 정보를 가져오거나 설정할 때 사용할 수 있다.)

Excel의 개체구조의 경우 Excel의 Visual Basic Editor 도움말을 참고하면 되는데 다음과 같다.

Excel에서 각 셀에 접근하기 위해서 Worksheet개체의 Cells라는 속성을 사용한다.
Cells속성은 행과 열의 번호를 인덱스로 지정할 수 있다. 예를 들어 "상세 정보"라는 이름의 워크시트의 C20번 셀에 접근하기 위해서는
Worksheets("상세 정보").Cells(20,3)이라고 사용하면 된다.

작업이 끝난 후 MS Project 파일을 닫아주는 것을 잊으면 안된다.
pj1.Application.FileClose

전체 소스는 다음과 같다.

Sub ResourceNames()

    Dim strReqID() As String, endDate() As Date, nComplete() As Integer, XLS_strReqID() As String, XLS_nComplete() As String
    Dim TaskName As String
    Dim objTask As Object
    Dim idx As Integer, idx1 As Integer
    Dim nWBSCount As Integer, nReqTotal As Integer

    'MSProject 객체를 생성한다.
    Set pj1 = CreateObject("MSProject.Project")

    '프로젝트 파일을 Open한다.
    pj1.Application.FileOpen "C:\요구사항_20080626.mpp"

    'Open된 프로젝트 파일을 선택한다.
    Set pj = pj1.Application.ActiveProject

    '프로젝트에 등록된 전체 WBS Task수를 읽어온다.
    nWBSCount = pj.Tasks.Count

    '엑셀에 등록된 전체 요구사항 수를 읽어온다.
    nReqTotal = Worksheets("요약").Cells(3, 3)
    MsgBox "전체 WBS : " & nWBSCount & ", 요구사항 : " & nReqTotal

    'WBS Task수로 Array를 초기화 한다.
    ReDim strReqID(nWBSCount)
    ReDim endDate(nWBSCount)
    ReDim nComplete(nWBSCount)

    'Excel의 요구사항 수로 Array를 초기화 한다.
    ReDim XLS_strReqID(nReqTotal)
    ReDim XLS_nComplete(nReqTotal)

    'MPP파일에서 필요한 정보를 읽어온다.
    'Text2에는 요구사항 ID, Finish에는 완료일자, PercentComplete에는 완료율(100이면 작업 완료임) 저장되어 있다.
    For idx = 1 To nWBSCount
        Set objTask = pj.Tasks(idx)

        strReqID(idx) = objTask.Text2
        endDate(idx) = objTask.Finish
        nComplete(idx) = objTask.PercentComplete
    Next idx
    MsgBox "MPP파일을 다 읽었습니다."

    'XLS파일에서 필요한 정보를 읽어온다.
    '첫번째 컬럼에는 요구사항ID가 일곱번째 컬럼에는 요구사항 진행 상황이 저장되어 있다.
    For idx1 = 1 To nReqTotal
        XLS_strReqID(idx1) = Worksheets("내용").Cells(idx1 + 1, 1).Value
        XLS_nComplete(idx1) = Worksheets("내용").Cells(idx1 + 1, 7).Value
    Next idx1
    MsgBox "XLS파일을 다 읽었습니다."

    'MPP파일에서 읽은 내용을 기준으로 엑셀 파일을 업데이트 한다.
    For idx = 1 To nWBSCount
        If strReqID(idx) <> "" Then
            For idx1 = 1 To nReqTotal
                '엑셀에 값을 업데이트 하는 작업의 속도가 상당히 느리기 때문에 값이 변경된 경우에만 업데이트 하도록 한다.
                If strReqID(idx) = XLS_strReqID(idx1) And Worksheets("내용").Cells(idx1 + 1, 6).Value = "수용" Then
                    If Worksheets("내용").Cells(idx1 + 1, 17).Value <> endDate(idx) Then
                        Worksheets("내용").Cells(idx1 + 1, 17).Value = endDate(idx)
                    End If
                    If nComplete(idx) = 100 And Worksheets("내용").Cells(idx1 + 1, 7).Value <> "완료" Then
                        Worksheets("내용").Cells(idx1 + 1, 7).Value = "완료"
                    ElseIf nComplete(idx) < 100 And Worksheets("내용").Cells(idx1 + 1, 7).Value <> "미완료" Then
                        Worksheets("내용").Cells(idx1 + 1, 7).Value = "미완료"
                    End If
                    Exit For
                End If
            Next idx1
        End If
    Next idx

    pj1.Application.FileClose
    MsgBox "완료되었습니다."

End Sub

Posted by thinknote