I am writing a macro to add a column of data from an MS Excel sheet into an already existing MS Project document. When I run my macro the correct project is opened, but I get an error message saying that the project I opened has been deleted, even though it has not been deleted.
Here is my current macro:
Sub newProjectColumn()
Dim appProj As MSProject.Application
Dim aProg As MSProject.Project
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Set wb = ActiveWorkbook
'~~> Open MS Project
Set ws = wb.Sheets("Sheet1")
Set appProj = CreateObject("Msproject.Application")
appProj.FileOpen "Project_1.mpp"
Set aProg = appProj.ActiveProject
appProj.Visible = True
'~~> Next, create new Column in project
TableEditEx Name:="Project_1.mpp", TaskTable:=True, _
NewFieldName:="Actual Duration", Title:="Actual Duration", Width:=12, _
ShowInMenu:=True, _
ColumnPosition:=29
'~~> Next, copy T:T from Excel.Then, paste to 29th column (i,29) in Project.
Set Rng = ws.Range("T:T")
ActiveSheet.Paste Destination:=Rng
SelectTaskColumn Column:="Actual Duration"
End Sub
Any help on what I have done wrong or if there is a better approach would be very much appreciated!
Here's the code you need to update the Actual Duration column. Change the table name as necessary. Update the range reference as necessary; do not include a header cell.
' add Actual Duration as the first column & apply the table changes
appProj.TableEditEx Name:="Entry", TaskTable:=True, _
NewFieldName:="Actual Duration", Title:="Actual Duration", Width:=12, ColumnPosition:=0
appProj.TableApply "Entry"
' copy updated values from Excel (no headers) and paste in new column in Project
Range("T2:T100").Copy
appProj.SelectColumn 2
appProj.EditPaste
' delete the column
appProj.ColumnDelete
Note that when adding a column using the TableEditEx method, the indexes start at 0 whereas when selecting a column using the SelectColumn method, the indexes start at 2.