How to pull Project info from Excel into MS Project, using a MS Project macro

Question

I'm working within MS Project and trying to use a macro to import project information from a spreadsheet to MS Project. What I have so far works fine for importing the Task information from the spreadsheet. I'd also like to import the Enterprise Custom Fields Project information, like Project Departments -- the information you would see in the Project Information dialog box. I am able to pass hard-coded information to those fields using ProjectSummaryTask.SetField, like this:

ActiveProject.ProjectSummaryTask.SetField FieldID:=FieldNameToFieldConstant("Project Departments", pjProject), Value:="Analytics"

But I don't know how to access the actual Excel spreadsheet and reference its cells that contain the information I need, so that I could then use a variable with that information in place of the text ("Analytics") in the example above.

The entirety of what I have so far is:

Sub Import()

MapEdit Name:="Map ", Create:=True, OverwriteExisting:=True, DataCategory:=0, CategoryEnabled:=True, _
    TableName:="Cleaned", FieldName:="Name", ExternalFieldName:="Summary", ExportFilter:="All Tasks", ImportMethod:=0, _
    HeaderRow:=True, AssignmentData:=False, TextDelimiter:=Chr$(9), TextFileOrigin:=0, UseHtmlTemplate:=False, IncludeImage:=False
MapEdit Name:="Map ", DataCategory:=0, FieldName:="Outline Level", ExternalFieldName:="Outline_Level"
MapEdit Name:="Map ", DataCategory:=0, FieldName:="Created", ExternalFieldName:="Created"
MapEdit Name:="Map ", DataCategory:=0, FieldName:="Start", ExternalFieldName:="Start_date"
MapEdit Name:="Map ", DataCategory:=0, FieldName:="Finish", ExternalFieldName:="Finish"
MapEdit Name:="Map ", DataCategory:=0, FieldName:="% Complete", ExternalFieldName:="%_Complete"
MapEdit Name:="Map ", DataCategory:=0, FieldName:="Notes", ExternalFieldName:="Notes_w_Comments"
FileOpenEx Name:="Mypath\Project Import Prep.xlsx", _
    ReadOnly:=False, Merge:=0, FormatID:="MSProject.ACE.14", map:="Map ", DoNotLoadFromEnterprise:=True
    
ActiveProject.ProjectSummaryTask.SetField FieldID:=FieldNameToFieldConstant("Project Departments", pjProject), Value:="Analytics"
ActiveProject.ProjectSummaryTask.SetField FieldID:=FieldNameToFieldConstant("Customer", pjProject), Value:="OA"

End Sub

Everything works, but I want to automatically populate the Project Departments and Customer fields from my spreadsheet, using variables instead of the hard code.

What do I need to do to enable and use the spreadsheet content as variables? Can you at least point me in the right direction?

I've done a lot of digging for answers, but everything I've found so far has been for working from within Excel instead of MS Project.

Rachel Hettinger Rachel Hettinger · Accepted Answer · 2021-03-23T17:16:38

Here is code that you can run from within Project to automate Excel and get the values from a worksheet:

Sub GetValuesFromExcel()

    Dim xl As Excel.Application
    Set xl = CreateObject("Excel.Application")
    xl.Visible = True
    
    Dim wbk As Excel.Workbook
    Set wbk = xl.Workbooks.Open("C:\...\<filename>.xlsx", UpdateLinks:=False, ReadOnly:=True)
    
    Dim Dept As String
    Dim Customer As String
    Dept = wbk.Worksheets("Sheet1").Range("A2")
    Customer = wbk.Worksheets("Sheet1").Range("B2")
    
    wbk.Close False
    xl.Quit
    
    ActiveProject.ProjectSummaryTask.SetField FieldNameToFieldConstant("Project Departments"), Dept
    ActiveProject.ProjectSummaryTask.SetField FieldNameToFieldConstant("Customer"), Customer
    
End Sub

This code uses early binding to the Excel object library so you'll need to set a reference to that file (Tools Menu: References, check the box for the Microsoft Excel Object Library).

And of course update the filename, sheet name, and range references.

How to pull Project info from Excel into MS Project, using a MS Project macro

1 Answers