2022-12-16

Deleting exceptions before adding new ones in MS Project VBA

Look for some help on deleting any existing exceptions in the calendar before I add the new ones from my spreadsheet. I'm not sure what else they want me to write.

Sub LoadHolidaysFromExcel()

Dim objXL As Object
Dim objWB As Object
Dim objWS As Object

Set objXL = CreateObject("Excel.Application")

MyFile = objXL.GetOpenFilename

Set objWB = objXL.Workbooks.Open(MyFile)

Set objWS = objWB.Worksheets(1)

'this next line is commented out for running the code, makes it visable
'objXL.Visible = True

objWS.Range("A1").Select
LR = objXL.ActiveCell.CurrentRegion.Rows.Count
'Call deleteCalendarExceptions
For x = 1 To LR - 1

MyName = objXL.ActiveCell.Offset(x, 0).Value
MyStart = objXL.ActiveCell.Offset(x, 1).Value
MyFinish = objXL.ActiveCell.Offset(x, 2).Value
MyCalendar = objXL.ActiveCell.Offset(x, 3).Value


ActiveProject.BaseCalendars(MyCalendar).Exceptions.Add Type:=1, Start:=MyStart, Finish:=MyFinish, name:=MyName

Next x


objXL.Workbooks.Close
MsgBox "all done!"
End Sub

I've tried the following but it fails

For Each x In ActiveProject.BaseCalendars(MyCalendar).Exceptions
    x.Delete


Here is the script to deleteCalendarExceptions

Sub deleteCalendarExceptions()
Dim e As Exception
Dim CalNam As String
CalNam = ActiveProject.Calendar.name
For Each e In ActiveProject.BaseCalendars(CalNam).Exceptions
    e.Delete
Next e
End Sub


No comments:

Post a Comment