none
Outputting Data from an MS Project file to Excel RRS feed

  • Question

  • Hi All

    I am looking for some guidance in populating a pre-existing Excel Spreadsheet in the next empty column with data collated using a macro.  The data is stored in a number of variables and is currently output to a MsgBox.  A copy of an extract of the code is below for information.  Any guidance/pointers would be appreciated:

    statString = "Plan Name: " & ActiveProject.Name & vbCrLf '& "As of PROJECT STATUS DATE:  " & ActiveProject.StatusDate '& " the Project Task count is: " & vbCrLf & vbCrLf
    statString = statString & vbCrLf & vbCrLf
    statString = statString & "PLAN CHANGES FROM PREVIOUS VERSION - METRICS: " & vbCrLf & vbCrLf
    statString = statString & "Tasks Added since last Plan: " & vbTab & newtasksCt & vbCrLf
    statString = statString & "Tasks Removed from Last Plan: " & vbTab & removedtaskCt & vbCrLf
    statString = statString & "Task Description Changes: " & vbTab & nameCt & vbCrLf
    statString = statString & "Task Duration Changes: " & vbTab & durationCt & vbCrLf
    statString = statString & "Task Percentage Complete less than previous plan: " & vbTab & percentCompleteCt & vbCrLf
    statString = statString & "Start Date Slippage: " & vbTab & startCt & vbCrLf
    statString = statString & "Finish Date Slippage: " & vbTab & finishCt & vbCrLf
    statString = statString & "Early Start Date: " & vbTab & earlyStartCt & vbCrLf
    statString = statString & "Early Finish Date: " & vbTab & earlyFinishCt & vbCrLf
    statString = statString & "Baseline Start Date changes: " & vbTab & baseStartCt & vbCrLf
    statString = statString & "Baseline Finish Date changes: " & vbTab & baseFinishCt & vbCrLf
    statString = statString & "Changes to Predecessors: " & vbTab predCt & vbCrLf
    statString = statString & "Changes to Successors: " & vbTab & succCt & vbCrLf
    statString = statString & "Resource Name Changes: " & vbTab & resNameCt & vbCrLf
    statString = statString & vbCrLf & vbCrLf
    statString = statString & "NOTE: Some changes to Predecessors, Successors and Resource Names may be due to additional tasks being added to the plan from last week."
    MsgBox statString, vbOKOnly + vbCritical, "SSCL PLAN CHANGES METRICS"

    Kind regards

    Tony


    TKHussar

    Tuesday, July 21, 2015 3:18 PM

Answers

  • Hi TKHussar,

    Normally, we need to use Application.Quit to quit Excel application if the Excel application is not visible. And if there is an execption in the code to lead Quit function was not executed, the process will remain.

    In addition, if you have any other problem with developing with Office, I suggest that you reopen a new thread so that other communities can recognize the issue easily and you would get more effective response.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by TKHussar Tuesday, July 28, 2015 10:26 AM
    Tuesday, July 28, 2015 3:08 AM
    Moderator

All replies

  • Hi Tony,

    >>I am looking for some guidance in populating a pre-existing Excel Spreadsheet in the next empty column with data collated using a macro.  <<

    We can write the data into spreadsheet via the Excel object modle in the Macro. And to get the next empty column, we can use UsedRange and here is an example that write the string to the first row of empty column for your reference:

    ActiveSheet.Cells(1, ActiveSheet.UsedRange.Cells(1, 1).Column + ActiveSheet.UsedRange.Columns.Count).Value = "Hello"

    In addition, here are some helpful links for your reference:

    How do I... (Excel 2013 developer reference)

    Range Object (Excel)

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 22, 2015 8:03 AM
    Moderator
  • Hi Fei Xue

    Many thanks for your response which was really helpful.  I have now resolved the issue of updating a particular worksheet with the VBA.  However when I run the routine again it tells me that the workbook is already open and opening it again will lose any changes made.  However the workbook is not visible.  Here is the relevant part of my code I am using:

    Workbooks.Open FileName:="D:\CPP Build Template\SSCL Plan Metrics Report.xlsx", IgnoreReadOnlyRecommended:=True
    Worksheets("Design").Activate
    
    TkVal = Cells(4, NextCol).Value
    '        xlApp.Visible = True
    Do Until TkVal = ""
        TkVal = Cells(4, NextCol).Value
        If TkVal <> "" Then
            NextCol = NextCol + 1
        End If
    Loop
    
    ActiveSheet.Cells(4, NextCol) = newtasksCt
    ActiveSheet.Cells(5, NextCol) = removedtaskCt
    ActiveSheet.Cells(6, NextCol) = nameCt
    ActiveSheet.Cells(7, NextCol) = durationCt
    ActiveSheet.Cells(8, NextCol) = percentCompleteCt
    ActiveSheet.Cells(9, NextCol) = startCt
    ActiveSheet.Cells(10, NextCol) = finishCt
    ActiveSheet.Cells(11, NextCol) = earlyStartCt
    ActiveSheet.Cells(12, NextCol) = earlyFinishCt
    ActiveSheet.Cells(13, NextCol) = baseStartCt
    ActiveSheet.Cells(14, NextCol) = predCt
    ActiveSheet.Cells(15, NextCol) = succCt
    ActiveSheet.Cells(16, NextCol) = resNameCt
    
    Set xlApp = Nothing

    Any help would be appreciated.

    Many thanks in anticipation.

    Kind regards

    Tony


    TKHussar


    • Edited by TKHussar Thursday, July 23, 2015 5:43 AM
    Wednesday, July 22, 2015 9:11 AM
  • Hi Tony,

    There is no property and method we can display the specific workbook. However we can make the Excel application which open the specfic workbook visible via the Application.Visible Property.

    In addtion, to save the changes for the wrokbook, we can Workbook.Save Method.

    Hope it is helpful.

    Regards & Fei 


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 24, 2015 6:00 AM
    Moderator
  • Hi Fei

    Many thanks for your help so far.  My code is only working every other time I run it.  Firstly the file opens as Reads Only even though it is not saved as a Read Only file. and secondly when the code fails it is always on the first line when it is trying to write to Excel.  My full code is below for information.  Any help/advice is appreciated:

    Sub CopyPlan_Compare_Metrics()
    Dim t As Task
    Dim percentCompleteCt, newtasksCt, removedtaskCt, earlyStartCt, earlyFinishCt, nameCt, durationCt, startCt, finishCt, baseStartCt, baseFinishCt, predCt, succCt, resNameCt As Integer
    Dim xlApp As Excel.Application
    Dim objRange, objRange1, objRange2
    Dim xlRng As Excel.Range
    Dim TkVal
    Dim s As Worksheet
    Dim Row As Integer
    Dim LastRow As Integer
    Dim NextCol As Integer
    Dim xlFilename
    
    Const xlAscending = 1
    Const xlYes = 1
    
    xlFilename = "D:\CPP Build Template\SSCL Plan Metrics Report.xlsx"
    'Set tsks = ActiveProject.Tasks
    
    'Set Variable values
    removedtaskCt = 0
    nameCt = 0
    durationCt = 0
    percentCompleteCt = 0
    startCt = 0
    finishCt = 0
    baseStartCt = 0
    baseFinishCt = 0
    predCt = 0
    succCt = 0
    resNameCt = 0
    earlyStartCt = 0
    earlyFinishCt = 0
    newtasksCt = 0
    NextCol = 1
    
    For Each t In ActiveProject.Tasks
        If (Not t Is Nothing) And (Not t.Summary) Then
    
            'New Tasks added count
            If t.Text30 Like "*- current*" Then
                newtasksCt = newtasksCt + 1
            End If
            'Removed Tasks count
            If t.Text30 Like "*- previous*" Then
                removedtaskCt = removedtaskCt + 1
            End If
            'Change to Name Description
            If t.Text30 Like "Different*" Then
                If t.Text30 Like "Only*" Then
                    nameCt = nameCt + 1
                End If
            End If
            'Change to Durations
            If Not t.Text25 = "Yes" Then
                If t.Text2 <> t.Text1 Then
                    durationCt = durationCt + 1
                End If
            End If
            'Reduction in % complete if task started
            If t.Number3 < 0 Then
                percentCompleteCt = percentCompleteCt + 1
            End If
            'Start Date Slippage
            If t.Text6 Like "-*" Then
                GoTo StartEarly
                Else
                    If t.Text6 <> "" Then
                        If t.Text6 <> "0d" Then
                            startCt = startCt + 1
                        End If
                    End If
            End If
            GoTo Finishcheck
            
    StartEarly:
            'Early Start Date
            earlyStartCt = earlyStartCt + 1
    
    Finishcheck:
            'Finish Date Changes
            If t.Text9 Like "-*" Then
                GoTo FinishEarly
                Else
                If t.Text9 <> "0d" Then
                    If t.Text9 <> "" Then
                        finishCt = finishCt + 1
                    End If
                End If
            End If
            GoTo Basecheck
            
    FinishEarly:
            earlyFinishCt = earlyFinishCt + 1
    
    Basecheck:
            'Baseline Start Changes
            If t.Text9 <> "0d" Then
                baseStartCt = baseStartCt + 1
            End If
            'Baseline Finish Changes
            If t.Text15 <> "" Then
                If t.Text15 <> "0d" Then
                    baseFinishCt = baseFinishCt + 1
                End If
            End If
            'Predecessor Changes
            If Not t.Text30 Like "Current*" Then
                If t.Text25 <> "Yes" Then
                    If t.Text18 = "Different" Then
                        predCt = predCt + 1
                    End If
                End If
            End If
            'Successor Changes
            If t.Text25 <> "Yes" Then
                If t.Text21 = "Different" Then
                    succCt = succCt + 1
                End If
            End If
            'Resource Name Changes
            If t.Text24 <> "Equal" Then
                resNameCt = resNameCt + 1
            End If
        End If
    Next t
    
    'Subtract added tasks count to give correct duration changes figure
    If newtasksCt > durationCt Then
        durationCt = durationCt - newtasksCt
    End If
    
    'Subtract added tasks count to give correct Baseline Start Date changes figure
    If newtasksCt >= baseStartCt Then
        baseStartCt = baseStartCt - newtasksCt
    End If
    
    'Subtract added tasks count to give correct baseline finish date changes figure
    If baseFinishCt > newtasksCt Then
        baseFinishCt = baseFinishCt - newtasksCt
    End If
    
    'Start Excel and create a new Workbook
    
    Set xlApp = CreateObject("Excel.Application")
        xlApp.Visible = True
    '    xlApp.Workbooks.Add
            
    Workbooks.Open FileName:=xlFilename, IgnoreReadOnlyRecommended:=True
    Worksheets("Design").Activate
    
    TkVal = Cells(4, NextCol).Value
    
    Do Until TkVal = ""
        TkVal = Cells(4, NextCol).Value
        If TkVal <> "" Then
            NextCol = NextCol + 1
        End If
    Loop
    
    xlApp.ActiveSheet.Cells(4, NextCol) = newtasksCt
    xlApp.ActiveSheet.Cells(5, NextCol) = removedtaskCt
    xlApp.ActiveSheet.Cells(6, NextCol) = nameCt
    xlApp.ActiveSheet.Cells(7, NextCol) = durationCt
    xlApp.ActiveSheet.Cells(8, NextCol) = percentCompleteCt
    xlApp.ActiveSheet.Cells(9, NextCol) = startCt
    xlApp.ActiveSheet.Cells(10, NextCol) = finishCt
    xlApp.ActiveSheet.Cells(11, NextCol) = earlyStartCt
    xlApp.ActiveSheet.Cells(12, NextCol) = earlyFinishCt
    xlApp.ActiveSheet.Cells(13, NextCol) = baseStartCt
    xlApp.ActiveSheet.Cells(14, NextCol) = predCt
    xlApp.ActiveSheet.Cells(15, NextCol) = succCt
    xlApp.ActiveSheet.Cells(16, NextCol) = resNameCt
    
    'xlApp.ActiveWorkbook.Activate
    'xlApp.ActiveWorkbook.Close SaveChanges:=True
    
    'xlApp.Application.Workbooks(xlFilename).Save
    
    Set xlApp = Nothing
    
    End Sub

    Kind regards

    Tony


    TKHussar

    Friday, July 24, 2015 2:34 PM
  • Hi Tony,

    >>  Firstly the file opens as Reads Only even though it is not saved as a Read Only file. and secondly when the code fails it is always on the first line when it is trying to write to Excel.  <<

    If the workbook was opened before, when we open the spreadsheet again, the workbook is read only by default. Please ensure there is no Excel application open this file. We can close all the Excel applicaiton and kill all the Excel processes in the Task Manager.

    If the workbook is read only, the issue for the behavior is expected.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, July 27, 2015 7:17 AM
    Moderator
  • Hi Fei

    Many thanks for your response.  Your response the begs the question as to why an instance of Excel remains open after a Excel is exited.  I don't want to have to kill Excel each time I want to run my metrics report.

    Look forward to hearing from you.

    Kind regards

    Tony


    TKHussar

    Monday, July 27, 2015 1:48 PM
  • Hi TKHussar,

    Normally, we need to use Application.Quit to quit Excel application if the Excel application is not visible. And if there is an execption in the code to lead Quit function was not executed, the process will remain.

    In addition, if you have any other problem with developing with Office, I suggest that you reopen a new thread so that other communities can recognize the issue easily and you would get more effective response.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by TKHussar Tuesday, July 28, 2015 10:26 AM
    Tuesday, July 28, 2015 3:08 AM
    Moderator
  • Hi Fei

    Many thanks for all of your help.  It is really appreciated.  I think I have resolved my issue now having added a couple of extra lines of code.

    Kind regards

    Tony


    TKHussar

    Tuesday, July 28, 2015 10:26 AM