Controlling Project from Access VBA

[Biggles]

I run a VBA sub to update actual time in assignments. I then want to open
each plan in a Project database to allow Project to calculate the task actual
time and time remaining, and the percentage work completed. Then I want to
close the plan. I will then, using VBA, change the actual and remaining
duration, and the pct complete. I have a question about the second part.

I am reading Rod Gill's book, and he gives an example of how to control
Project from Excel. I want to change it and control Project from Access.
When I run this:

Sub OPENANDSAVE()

Dim projapp As MSProject.Application
Dim v_PROJ_FILE As String

'On Error Resume Next
'On Error GoTo 0
Set projapp = GetObject(, "MSProject.Application")
If projapp Is Nothing Then
Set projapp = CreateObject("MSProject.Application")
End If

Set rs(5) = New ADODB.Recordset

ssql(9) = "SELECT PROJ_ID, PROJECT_NAME FROM MSP_PROJECTS WHERE PROJ_ID
< 7 "

rs(5).Open ssql(9), conn_proj

Do Until rs(5).EOF

v_PROJ_FILE = "<" & proj_db & ">\" & rs(5)!PROJECT_NAME
Debug.Print v_PROJ_FILE
projapp.FileOpen Name:=v_PROJ_FILE, FORMATID:="MSPROJECT.MPD",
ReadOnly:=False

projapp.Visible = True

projapp.FileClose pjSave

rs(5).MoveNext
Loop

Set projapp = Nothing



End Sub

At the getobject line, I get the error 429

ActiveX component can't create object.

What do I need to do?

 

[Jack Dahlgren]

That is an issue with "Getobject". Modify the code so you just
"CreateObject" only.
Details on this error can be found here:
http://zo-d.com/blog/archives/programming/working-with-other-applications.html

-Jack Dahlgren

 

[Rod Gill]

Hi,

Controlling Project from Access is just the same as from Excel. GetObject
only gets an object that already exists. If it doesn't (Project is not
running) then an error is returned. This is why you need error checking on
to catch this and start Project using a createObject. Remove the comment '
from:
'On Error Resume Next
and your code should work provided you have added a reference to Project.

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[Rod Gill]

PS thanks for buying the book!!

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[Biggles]

After a few minor alterations and a better understanding from you guys, (like
defining conn_proj in the sub), I got it to work. Thanks