Thread: Macro for paste link a date from MS Project to Excel

I have done a lot of Excel VBA that interfaces with MS Project files. VBA can't access the Project file data without opening the file. (The same is true of accessing data from any file, from any application, using VBA.) It is possible to minimize the Project window, but the user would see the window come up first, and then be minimized; that may be sufficient for your purposes.

The devil is in the details. What do you mean by

Copy date in cell XXX

How will the VBA know what date(s) to get from Project? For VBA, the Project object model is quite different than the Excel object model, even though Project kinda sorta looks like Excel in certain views. In Project, there is a collection of Tasks, and each task has several dates: Start Date, Finish Date, Actual Start Date, Actual Finish Date, Baseline Start Date, Baseline Finish Date, etc.

I would like to give you some flavor of how "simple" this will be. Here is my code for opening an MS Project file. In this version, the user is prompted to open the file, but to open a specific file every time, only one line of code would change. This code includes minimizing the Project window:

' Global variable:
Dim MSP As Object ' The Project application

' Open Project and prompt the user for a Project file to open
' Return value is the Project object
' Project is left open, minimized
Public Function openProject() As project
   ' Create the Project application object.  This prevents errors if
   ' executing this code again in the same run of Excel as opposed to
   ' simply using MSProject
   Set MSP = CreateObject("MSProject.Application")
 
   MSP.FileOpen ' to open a particular file, just add the file name here as an argument
 
   On Error GoTo NoProject
   Set openProject = MSP.ActiveProject
   On Error GoTo 0
 
   MSP.AppMinimize
   Exit Function
 
NoProject:
 
   MSP.FileExit
   Set openProject = Nothing
 
   If Err.Number = 424 Then
      ' File not found
      MsgBox "No Project selected, exiting."
   Else
      MsgBox "Unexpected error attempting to open Project file" & vbCrLf & _
             "Exiting." & vbCrLf & _
             Err.Number & " " & Err.Description
   End If
 
 
   Exit Function
End Function