I am trying to insert data from excel file into project file i.e .mpp file. Following code works fine while putting data into simple cells (example cost column) in .mpp file.
import openpyxl
import win32com.client
file = 'PATH_TO_PROJECT_FILE'
project_app = win32com.client.Dispatch('MSProject.Application')
project_app.Visible = 1
project_app.FileOpen(file)
project = project_app.ActiveProject
project_tasks = project.Tasks
file_path = 'PATH_TO_EXCEL_FILE'
file = openpyxl.load_workbook(file_path,keep_vba=True)
sheet3 = file['OutputData']
for cell,task in zip(sheet3['D'][1:],project_tasks):
if '=' in str(cell.value):
break
task.Cost = cell.value
But in .mpp file, cells of duration column have spinners/spin buttons to set number of days. see picture below:
If I use the same code as above to set/insert value of duration using python, it does not change the value, moreover it does not throw any exception and the program exit in normal fashion
Same is the case with start date in project. see the picture below:
A solution came to my mind but it does not seem feasible. If I create a custom column and insert duration and start date into it. But the draw back of this solution is that the visuals in MS Project files are dependent on previously default columns.
How can I set/insert values into these type of cells? Or if there is anything I can do in MS Project? Or there's any solution using vba?
CodePudding user response：
Junaid, I'm not familiar with Python so I can't help with that but what you want to do is very possible with VBA. Here is a macro I wrote a few years ago that should help you get started in the right direction. John
'Macro written by John - Project
'Version 1.0 9/25/15 11:00 AM
Option Explicit
Option Compare Text
Public Const ver = " - 1.0"
Public xl As Excel.Application
Public WB As Excel.Workbook
Public S As Excel.Worksheet
Public c As Excel.Range
Public Tsks As Tasks
Public UID As Single
Public SeedDt As Date
Public DurVal As Single, HPD As Single, HPW As Single, cf As Single
Public numrows As Integer, i As Integer, p1 As Integer
Public curcel As Variant 'could be either a number or text
Sub ImportExcelDataToProject()
MsgBox "This macro imports the following data fields from Excel:" & vbCr & _
" Task Name" & vbCr & " Outline Level" & vbCr & _
" Duration" & vbCr & " Start (if necessary)" & vbCr & _
" Predecessors" & vbCr & " Resource Names" & vbCr & _
" Task Notes", vbInformation, "Import from Excel" & ver
'Open the Excel workbook to gather data
' Note: Excel need not be running
Set WB = Workbooks.Open(FileName:="C:\Users\John\Desktop\ExcelToProjectVBAImportX.xlsx")
Set S = WB.Worksheets(1)
'Create new Project file to receive imported data
FileNew
'----------------------
'Gather some basic parameters from Excel and Project
' Find earliest start date used in Excel workbook
sort1
' Find out how many rows of data in Excel worksheet
' (assumes first row is header, if there is none remove the "-1")
numrows = WB.Worksheets(1).UsedRange.Rows.Count - 1
' Find the default hours per day and hours per week settings for Project
HPD = ActiveProject.HoursPerDay
HPW = ActiveProject.HoursPerWeek
'-----------------------
'Read each row of data from the worksheet and create tasks in Project
Application.Caption = "Progress"
ActiveWindow.Caption = " Reading worksheet and exporting"
Set c = S.Range("B2") 'set reference to first column of data to be imported
Set Tsks = ActiveProject.Tasks
For i = 0 To numrows - 1
Tsks.Add.Name = c.Offset(i, 0).Value
'find the unique ID of the task just added
' since tasks are added in sequence, the count property identifies the current task
' (having the Unique ID facilitates expansion of the macro for increased functionality)
UID = Tsks(Tsks.Count).UniqueID
Tsks.UniqueID(UID).OutlineLevel = c.Offset(i, 1).Value
'skip remaining columns for this row if this is destined to be a summary line in Project
' (Project calculates duration and start and best practices dictate no resources assigned)
If c.Offset(i, 2).Value <> "" Then
'resolve units used in duration column of Excel worksheet
DecodeXLDurUnits
Tsks.UniqueID(UID).Duration = DurVal
Tsks.UniqueID(UID).Predecessors = c.Offset(i, 3).Value
'if no predecessors exist for this task AND it starts after the Project Start Date
' then set start date. Note: this will set a start-no-earlier-than (SNET) constraint
If Tsks.UniqueID(UID).Predecessors = "" And CStr(c.Offset(i, 4).Value) > SeedDt Then
Tsks.UniqueID(UID).Start = CStr(c.Offset(i, 4).Value)
End If
Tsks.UniqueID(UID).ResourceNames = c.Offset(i, 5).Value
End If
Tsks.UniqueID(UID).Notes = c.Offset(i, 6).Value
Next i
'------------------------
'Finally, close and exit
MsgBox "Data Import is complete", vbOKOnly, "Import from Excel"
Application.Caption = ""
ActiveWindow.Caption = ""
WB.Close savechanges:=False
End Sub
'This routine determines if duration column in Excel is in minutes, hours, days or weeks
' (most likely units) and then adjusts the data accordingly for import to Project
Sub DecodeXLDurUnits()
curcel = c.Offset(i, 2).Value
'default if duration column is in minutes
p1 = Len(CStr(curcel)) 1
cf = 1
If InStr(curcel, "h") > 0 Then
p1 = InStr(curcel, "h")
cf = 60
ElseIf InStr(curcel, "d") > 0 Then
p1 = InStr(curcel, "d")
cf = HPD * 60
ElseIf InStr(curcel, "w") > 0 Then
p1 = InStr(curcel, "w")
cf = HPW * 60
End If
'convert duration value to be in minutes for Project import
DurVal = CSng(Mid(curcel, 1, p1 - 1)) * cf
End Sub
'This routine examines the pre-formatted Excel Workbook Start column and finds the
' earliest date. This is then used to set the Project Start Date
Sub sort1()
Dim Cnt As Integer
numrows = S.UsedRange.Rows.Count
SeedDt = "12/31/2049" 'maintain compatibility with Pre-Project 2013 versions
Set c = S.Range("F2")
For i = 0 To numrows - 1
If c.Offset(i, 0).Value <> "" And c.Offset(i, 0).Value < SeedDt Then SeedDt = c.Offset(i, 0).Value
Next i
ActiveProject.ProjectStart = SeedDt
End Sub
CodePudding user response：
You can set task.Start and task.Duration just as you can set task.Cost. The fact that the MS Project UI has spinners for those fields is not relevant. Try a simple example without pulling data from Excel to start. Then validate that the data in Excel is in the proper format.
import win32com.client
file = 'PATH_TO_PROJECT_FILE'
project_app = win32com.client.Dispatch('MSProject.Application')
project_app.Visible = 1
project_app.FileOpen(file)
project = project_app.ActiveProject
project_tasks = project.Tasks
task = project_tasks(1) #pick a non-summary task
task.Cost = 100
task.Duration = "10 days"
task.Start = "10/01/22"
project_app.FileSave()
project_app.Quit(True)
BTW: On Summary tasks, Duration and Start are calculated fields and are therefore read-only.