Using Excel to create and save MS Project document

[Dawber]

I am trying to create a Microsoft Project document from a template, input two pieces of data gathered from an Excel spreadsheet, name the document with the same two pieces of data, save the MS Project file and exit the application.

I have no idea how to open the MS Project application but based on limited knowledge of inputting data into a Word Document, I would imagine the code to look something like this: (Please assume that the data for “Site” and “Project” is correctly gathered and defined earlier in the code)

Dim appProj As MSProject.Application
Dim aProg As MSProject.Document
Set appProj = CreateObject("MSProject.Application")
Set aProj = appProj.Documents.Open(Filename:="C: \MS Project.mpt")
aProj.Application.Visible = True
FilePageSetupHeader Alignment:=pjRight, Text:=Site & " - " & Project
FileSaveAs Name:="C:\" & Site & " - " & Project & ".mpp", FormatID:="MSProject.MPP"
aProj.Close
Set aProj = Nothing
appProj.Quit
Set appProj = Nothing

 

[SkipVought]

Hi,

And what happens when you run this?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Dawber]

Hi Skip,

I got a compile error "User-defined type not defined" with the second line highlighted, so I changed MSProject.Document to MSProject.Project

The code now terminates at the fourth line:

Set aProj = appProj.Documents.Open(Filename:="C: \MS Project.mpt")

with a Runtime error 438, Object doesn't support this property or method. Changing "Documents" to "Project" or "Projects", leads to the same error.

 

[SkipVought]

Set a reference to Microsoft Project m.n Object Library in Tools > References...

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TonyJollans]

Having changed MSProject.[green]Document[/green] to MSProject.[blue]Project[/blue], don't you think it would be worth trying changing the failing appProj.[green]Documents[/green].Open to appProj.[blue]Projects[/blue].Open before asking for more help?

When you do get over this problem, you will probably run into difficulties using MSProject methods without qualification (FilePageSetupHeader, for example, should probably be appProj.FilePageSetupHeader)

You would also be well served to Code Option Explicit as the first line of your module. This would trigger the compiler to identify another potential problem.

Setting a reference to the MS Project Type Library, as Skip suggests, would certainly help, but this isn't actually that difficult. We're here to help, not to do it all for you.


Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[Dawber]

I have already set that reference Skip.

 

[Dawber]

Tony,

I realise that you are here to help and that is all I'm asking for, not to do it for me, hence if you look at my first response to Skip, you will see that I had tried your MSProject.Project idea, aswell as .Projects.

I appreciate both of you taking the time to respond.

 

[TonyJollans]

Sorry, Dawber, I should have looked a bit further, and known better than to post like that. I don't know Project very well and it is not straight forward.

I just recorded opening a file in Project and it gave me a FileOpenEx statement. This does not give a reference directly, but you can pick one up. Try this:

[blue]Set appProj = CreateObject("MSProject.Application")
appProj.FileOpenEx Name:="C:\MS Project.mpt"
Set aProg = appProj.ActiveProject
[green]' ... etc.[/green][/blue]

Having said that, what is a .mpt file? I tested with a .mpp.

Sorry, I'm just about to go out and can't look at this any more at the moment.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[SkipVought]

Here's some code I played with. Discovered that there does not seem to be a Close Method for the Project object. Go figure!

But this runs...

Sub test()
    Dim oMP As MSProject.Application, oPJ As MSProject.Project
    
    Set oMP = CreateObject("MSProject.Application")
    
'I cannot set oPJ to this statement...get an error on .FileOpenEx???
    oMP.FileOpenEx "C:\Documents and Settings\ii36250\My Documents\project1.mpp"
    
    Set oPJ = ActiveProject
    
    With oPJ
        .Parent.Visible = True
        
        
    End With
    oMP.Quit
    
    Set oPJ = Nothing
    Set oMP = Nothing
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TonyJollans]

there does not seem to be a Close Method for the Project object

[blue][tt]FileCloseEx[/tt][/blue] should do it.

Now that I see that .mpt files are Templates, I don't know if you want to Open the Template ([blue][tt]FileOpenEx[/tt][/blue]) or create a new project based on it ([blue][tt]FileNew[/tt][/blue]).

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website