none
VBA Project to Excel New Sheet RRS feed

  • Question

  • Good afternoon everyone!

    I'm trying to do something which feels like it should be painfully simple!

    I have a macro already written and functioning almost properly which creates a CSV report from my MSP Project.

    See here:

    Sub CheckIntegrity()
    ' Macro runs a Report inspired fy P6 Primavera Schedule Log Report.
    ' V1.1 Philip Day 03.10.2016
    
    Dim i As Integer
    Dim t As Task
    Dim My_filenumber As Integer
    Dim logSTR As String, PlannerName As String, ProjName As String
    Dim dp As Subproject
    Dim MastCount As Integer, subcount As Integer, totsubcount As Integer, Printcount As Integer
    Dim Count As Integer, Percentcount As String, Time As String
    'Dim TCalRes As Boolean
    Dim MasterName As String
    
    My_filenumber = FreeFile
    
    Count = 0
    i = 1
    Time = Now()
    'Remove any filters
    FilterApply Name:="All Tasks"
    
    'Show all Sub-tasks
    SelectSheet
        OutlineShowAllTasks
    
    'Check for Tasks with Calendar have resources?
    'TCalRes = False
    'If MsgBox("Check for Resources on tasks which have been assigned a calendar?", vbYesNo, "Check Resources?") = True Then
    '   TCalRes = True
    'End If
       
    For Each t In ActiveProject.Tasks
        
        If t Is Nothing Then
        GoTo Last
        End If
        
        If (Not t Is Nothing) And (Not t.Summary) Then
           SelectRow Row:=i, RowRelative:=False
         'Count total tasks
            Select Case ActiveProject.Subprojects.Count > "0"
    
                Case True
                    ProjName = t.Subproject
                    MastCount = ActiveProject.Tasks.Count
                    For Each sp In ActiveProject.Subprojects
                    subcount = sp.SourceProject.Tasks.Count
                    totsubcount = totsubcount + subcount
                    Next sp
                    Printcount = MastCount + totsubcount
                Case False
                    ProjName = t.Project
                    Printcount = ActiveProject.Tasks.Count
            End Select
    
            Select Case True
            Case t.Predecessors = ""
            logSTR = logSTR & "Predecessor not defined: " & " , " & "Project Name = " & ProjName & " , " & "Task ID = " & t.ID & " , " & t.Name & vbCrLf
            Case t.Successors = ""
            logSTR = logSTR & "Successor not defined: " & " , " & "Project Name = " & ProjName & " , " & "Task ID = " & t.ID & " , " & t.Name & vbCrLf
            Case t.ConstraintType
            logSTR = logSTR & "Constraint added: " & " , " & "Project Name = " & ProjName & " , " & "Task ID = " & t.ID & " , " & t.Name & vbCrLf
            Count = Count + 1
            'Case t.Calendar = "Night shift" And t.ResourceNames = "" And TCalRes = True
            'logSTR = logSTR & "No Resources on Task" & " , " & "Project Name = " & ProjName & " , " & "Task ID = " & t.ID & " , " & t.Name & vbCrLf
            End Select
                      
        End If
           
    i = i + 1
    
    Next t
    Last:
    
    Percentcount = (Count / Printcount) * 100
    
    PlannerName = InputBox("Please enter your name", "What's your name?", "Insert Your Name")
    
    'Set File Path for Integrity Log here
    MasterName = "C:\USERS\Upminster\Desktop\IntegrityLog.csv"
    
    Open MasterName For Append As #My_filenumber
    
            Print #My_filenumber, "Total Number of Tasks in Project: ", Printcount; vbCrLf; "Number of Constrained Tasks " & Count & ". Percent Constrained: " & Format(Percentcount, "0.0")
            Print #My_filenumber, Now(), "Report Run by: " & PlannerName
            Print #My_filenumber, logSTR
            Print #My_filenumber, vbCrLf; "End of Report"
    Close #My_filenumber
    
    MsgBox "Integrity Log created.", vbOKOnly, "Log Created!"
    
    End Sub
    
    
    

    Unfortunately, I'm trying to make it so that, instead of inserting the data beneath the old data, it creates a new sheet first, and prints the data there. Which I have been singly unable to achieve.

    Bonus points:

    One of my Cases is to test for a task which is not on the Task Calendar "None" and which does not have any resources assigned.

    You can see commented out my attempt to make this work so far, and I'm also struggling with getting this to work.

    Tuesday, October 4, 2016 12:37 PM

All replies

  • A few things:

    1) You don't need to do any of the filtering and selecting when you loop over activeproject.tasks. It just slows things down.

    2) Your "if t is nothing then goto last" statement will cause the routine to stop if it runs into a blank line in the middle of the file.

    3) I don't think you can make a csv file with multiple sheets. You could write VBA code to read the csv on the Excel side and add sentinel values to tell it when to create a new sheet.

    4) Change the check for MsgBox = True to MsgBox = vbYes. That should take care of the bonus situation.

    5) Beware of embedded commas in task and project names.

    Tuesday, October 4, 2016 1:25 PM
  • Hi Bill.B,

    Thanks for your reply!

    1. I have found in the past that if I do not apply the filters then the Macro enacts changes to the shown tasks, as though they were them all.

    i.e.  Macro to colour tasks with value "True"

    Active - Task 1 - True

    Inactive - Task 2 - True

    Active - Task 3 - False

    Annoyingly, if the filter is applied to hide the inactive task and I run the Macro the result is:

    Active - Task 1 - True = Coloured!

    Active - Task 3 - False = Coloured!

    Perhaps I'm making a rookie error elsewhere?

    2. You're right, but I regularly run another macro to check for blank tasks. You can't effectively export to P6 Primavera with blank rows anyway :)

    3. Ah this could be a big part of the problem, I'm going to try as you suggest!

    4. Thanks for the tip, obvious when you point it out. It's still not quite working, but I'll take a look at it from here instead of wasting more of your time.

    5. Thank you, I will have to be very careful with my task naming convention if a CSV is to be the answer I'm looking for!

    Tuesday, October 4, 2016 1:51 PM
  • Philip,

    Pardon me for jumping in. Bill brought up some excellent comments but let me ask you, why do you specifically want to dump the data into a CSV file? If your end goal is to eventually get the data to Excel, why not export to Excel directly? You might want to take a look at the following Wiki article. The macro there could easily be adapted to do what you want.

    http://social.technet.microsoft.com/wiki/contents/articles/32126.ms-project-truncated-project-notes-when-viewingexporting.aspx

    John

    Tuesday, October 4, 2016 4:01 PM
  • Thanks John, I've been super busy and haven't had much ofa chance to investigate this just yet. I've had a quick look and this looks very helpful, thank you!

    Phil

    Friday, October 7, 2016 2:30 PM
  • Phil,

    Yeah, I wondered what happened to you. I always worry about the alien abduction thing so glad to hear you're still with us [tongue in cheek]:-)

    Anyway, you're welcome and thanks for the feedback.

    John

    Friday, October 7, 2016 4:27 PM