How to Import Resource Availability from Excel when it varies by month

Question

I have a number of generic resources, eg Engineers, where each individual is treated as interchangeable with all the other Engineers. I want to show their availability as for instance 100% in October (equating to 1 engineer) and 200% in November (ie 2 engineers).

I know how to do this manually in Project via the Resource Information dialog, but I want to import this data from a table in Excel where these values are calculated.

Using this table as an example

Name     Max_Units  Available_From  Available_To
Engineer     100    01/10/2018      31/10/2018
Engineer     200    01/11/2018

If I import this data I was hoping to see one Engineer resource in the Resource Sheet with 2 entries in the Resource Information dialog under Resource Availabioity.

Instead I get 2 resources called Engineer in the Resource Sheet. If I double click on the first entry (the one with 100 units) to see the Resource Information dialog I see the data for the first line in the above table. But if I look at the second entry (the one with 200 units) I see this. Note it shows the wrong dates for Availability.

What I want is 1 resource in the Resource Sheet called Engineer but with his Availability in the Resource Information dialog showing as 100 in October and 200 from 1 November onwards. I know this can be done manually, but how can I import the data to achieve this result? Do I need VBA code? If so any suggestions are welcome.

Thanks

Eric

Answer 1

It's funny how often articulating a question can lead you to finding the answer your self. The following codes does the job in a few seconds and will save me many hours of boring data input. I am very happy!

Sub ImportSupply()

    ' This will open the Excel file containing the Supply profiles and import the data into the Availability table in MS Project for each Resource Type

    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")

    If xlApp Is Nothing Then
        Set xlApp = CreateObject("Excel.Application")
    Else
    End If

    xlApp.Visible = True

    ' Open the excel file
        Set xlWB = xlApp.Workbooks.Open(ResourceReportsFolderPath & ResourceReportsFilename) ' Name of Report file
        Set xlWS = xlWB.Worksheets("MSPdataSupply")

        xlWS.Activate
        Set tbl = xlWS.ListObjects("tblMSPdataSupply")

        TableRowsCount = tbl.ListRows.Count

    ' Capture the the relevant data from each row of tblSupply from Excel and insert into the Master plan file
        For TableRow = 1 To TableRowsCount
            ResourceName = tbl.DataBodyRange(TableRow, 2)
            MaxUnits = tbl.DataBodyRange(TableRow, 4)
            AvailableFrom = tbl.DataBodyRange(TableRow, 5)
            AvailableTo = tbl.DataBodyRange(TableRow, 6)

            ActiveProject.Resources(ResourceName).Availabilities.Add AvailableFrom, AvailableTo, MaxUnits
        Next TableRow

    ' To tidy up
        xlApp.UserControl = True
        Set xlApp = Nothing

End Sub