How Do I automatically import days of resource absence?

Question

I would like to import days of resource absence automatically through VBA macros.

I imagine the thing for example, a CSV file that could contain:

Resource1;25/08/2019;26/08/2019;01/11/2019
Resource2;25/08/2019;26/08/2019;01/11/2019

Thus MS project could update the resource calendar through this CVS file.

Has anyone already implemented it and is it feasible?

Thank you.

There is no such automatic import. You will need to write code to do this. Here's the relevant method: [BaseCalendarEditDays](https://learn.microsoft.com/en-us/office/vba/api/project.application.basecalendareditdays). And of course, each resource having a different work schedule will need its own [calendar](https://learn.microsoft.com/en-us/office/vba/api/project.calendar). — Rachel Hettinger, Aug 26 '19 at 17:11

score 0 · Answer 1 · answered Aug 29 '19 at 20:32

Kikou!

Based on what you provided, I came up with a structure to insert the missing dates.

I've taken some assumptions:

That the data is always on Sheet1 of your workbook
That column A never has valid information for your task, but always has content;
That column B onwards have Date values;
That there are no blank cells between one date and another;
That the dates are always ascending from left to right.

The macro structure will loop between all lines, and add missing dates between a column and the last.

Sub fill_date_gaps()
    last_row = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

    For curr_row = 1 To last_row

        last_col = Sheet1.Cells(curr_row, Columns.Count).End(xlToLeft).Column
        curr_col = 3

        '' For each line, checks a given date with the one preceding it
        '' checks if their difference is bigger than one day
        '' If so, move all one to the left and increment in one day
        Do
            this_date = Sheet1.Cells(curr_row, curr_col)
            date_prior = Sheet1.Cells(curr_row, (curr_col - 1))

            date_delta = DateDiff("d", date_prior, this_date)
            If date_delta > 1 Then
                Sheet1.Cells(curr_row, curr_col).Insert shift:=xlToRight
                Sheet1.Cells(curr_row, curr_col) = DateAdd("d", 1, date_prior)

                last_col = last_col + 1
            End If
            curr_col = curr_col + 1
        Loop Until curr_col > last_col
    Next curr_row
End Sub

Let me know your thoughts. As long as you have a given .csv, you could have this macro perform the task you've described.

Thank you for this example, at what point, do you update the resource schedule ? — Kikou, Aug 30 '19 at 06:55
This answer has nothing to do with the OP's question which pertains to resource calendars in Microsoft Project. — Rachel Hettinger, Sep 04 '19 at 21:27

How Do I automatically import days of resource absence?

1 Answers1