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
Comments
Post a Comment