' if on Excel >= 2010 then select all entries and autoformat table SwaWorkbook.Sheets(1).Columns("I").NumberFormat = "0.0"Į(1).Activate ' ugly, but works SwaWorkbook.Sheets(1).Columns("H").NumberFormat = "0.0" SwaWorkbook.Sheets(1).Columns("G").NumberFormat = "0.0" SwaWorkbook.Sheets(1).Columns("D").ColumnWidth = 70 SwaWorkbook.Sheets(1).Columns("C").ColumnWidth = 6 SwaWorkbook.Sheets(1).Columns("B").ColumnWidth = 70 SwaWorkbook.Sheets(1).Columns("A").ColumnWidth = 20 SwaWorkbook.Sheets(1).Columns("A:I").AutoFitĮxcelApp.Goto swaWorkbook.Sheets(1).Range("A2") ' pimp excel file, close excel file and clean up SwaWorksheet.Rows(i). = 6 ' finish next week -> yellow If intCalendarWeekFinish = intCalendarWeek + 1 Then SwaWorksheet.Rows(i). = 3 ' finish this week -> red If intCalendarWeekFinish = intCalendarWeek Then
Ms project predecessors mod#
IntCalendarWeekFinish = (t.Finish - datTestDate - 3 + (Weekday(datTestDate) + 1) Mod 7) \ 7 + 1 ' if Finish Date in the same calendar week then highlight the entire rowĭatTestDate = DateSerial(Year(t.Finish + (8 - Weekday(t.Finish)) Mod 7 - 3), 1, 1) ' Debug.Print t.Text28 " " t.OutlineParent.Name " " realname " " t.Name, t.Start t.Finish t.Work / 60 t.ActualWork / 60 t.RemainingWork / 60 SwaWorksheet.Cells(i, 9) = t.RemainingWork / (60 * 8) SwaWorksheet.Cells(i, 8) = t.ActualWork / (60 * 8) SwaWorksheet.Cells(i, 7) = t.Work / (60 * 8) SwaWorksheet.Cells(i, 2) = t.OutlineParent.Name If realname = r.Name And t.RemainingWork > 0 Then Realname = Left(t.ResourceNames, InStr(t.ResourceNames, "[") - 1) ' now dump all tasks with remaining work > 0 SwaWorksheet.Rows(1). = TrueĮxcelApp.Calculation = xlCalculationManual SwaWorksheet.Cells(1, 9) = "Remaining Work " SwaWorksheet.Cells(1, 8) = "Actual Work " SwaWorksheet.Cells(1, 2) = "Summary Task" ' write header: name, date, actual work in hours. Set swaWorksheet = excelApp.Worksheets(1) ' work with first worksheet
Set excelApp = CreateObject("Excel.Application") ' filename is swaPath + year + KW (leading zero) + Name + ".xlsx"Īpplication.StatusBar = "Dumping " + swaFilename SwaFilename = swaPath + swaPrefix + "-" + CStr(Year(VBA.Date)) + "-" + strCalendarWeek + "-" + r.Name + ".xlsx" IntCalendarWeek = (VBA.Date - datTestDate - 3 + (Weekday(datTestDate) + 1) Mod 7) \ 7 + 1 'check out the actual calendar week ' list here all employees that should not be dumpedĭatTestDate = DateSerial(Year(VBA.Date + (8 - Weekday(VBA.Date)) Mod 7 - 3), 1, 1) SwaPath = "C:\TEMP\" ' must finish with backslash and MUST BE ACCESSIBLE FOR USER (hint C:\ does not work in my case) ' works only iff there is at most one resource per taskĭim excelApp As Object, swaWorkbook As Object ' dumps all tasks for all resources into individual excel files
IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1) If you run the macro twice in one week, you’ll first have to remove the files of the first run.Ĭaveat: the macro works reliably only if there is at most one resource assigned to a task.įunction IsInArray(stringToBeFound As String, arr As Variant) As Boolean The first column contains a field (text28) which I use in MS Project to assign tasks to projects.Īll tasks that finish in the current week are highlighted in red, all task that finish in the subsequent week are highlighted in yellow. Note that work, actual work and remaining work are displayed in days. Here is what the resulting files look like. customize the file names (defaults to Timesheet – – –.customize the target directory (defaultl “C\:temp”).exclude resources (see the NoShow list).Here is a simple macro to export a list of tasks to an Excel spreadsheet for all active resources on the project. Nonetheless everyone has to know what’s going on. Not many people on projects typically have MS Project licenses and can read my project plan on their own. Export timesheets from MS Project to MS Excel This entry was posted in Project Management, Project tips and tagged project2003, project2010 on Jby Stefan Waldherr. MsgBox msg, vbInformation, "Predecessors, percent complete 100 and finish dates" If t.PercentComplete 100 Then msg = msg & t.ID & vbTab & Left(t.Name, 30) & vbTab & Format(t.Finish, "dd.mm.yyyy") & vbNewLine Msg = (1).Name & vbNewLine & vbNewLineįor Each t In (1).PredecessorTasks
Ms project predecessors how to#
No idea, how to use this code? Check out, how to add VBA code to your computer. The solution: add a button to MS Project and run a macro that lists all unfinished predecessors along with its line number and finish date. Have you ever been annoyed, that MS Project does not list you dates of predecessors in its task form? How do you quickly determine the driving predecessor? If you have many predecessors for a tasks, finding the driving task (without switching views…) can become pretty cumbersome.