Problems publishing to project server

[Steve Chermak]

I am trying to write code that will run overnight to publish project files to
project server, to the PWA. I have code in an Excel file that runs code to
open project server and publish 3 files. I utilize the Windows Scheduled
Tasks to launch an Autpen subroutine in the Excel file.

When I am logged on to the server and run the macro, the publishing goes ok.
But when this runs overnight, the first file does not publish and gives an
Object required (number 424) error. (The code errors at the set pjProj =
pjApp.ActiveProject command, in my code at the end of this post).

I can not figure out why my being logged on matters or what is missing (when
I am not logged on) for the first one or why after the first one the rest
publish ok.

I put error trapping code in the macro both to try and determine the problem
and also to proceed forward if one file can not be published. This will be
necessary even once I overcome this issue because there may be other reasons
unrelated to code errors why one of the files may not publish correctly and I
do not want the whole effort to stop because of a problem with only one file.

If anybody has any ideas, I would appreciate them. Below is the relevant
part of my code (a subroutine launched 3 times by other code):

*********************************

Public Sub Do_publishing(theEvent As String, theWkbkSN As String, theWkbkLN
As String)


pjWkbkShortName = theWkbkSN
pjWkbkLongName = theWkbkLN

myEvent = theEvent
errNum = 0
errDesc = ""
errSrc = ""


LogComment_withError myEvent, errNum, errDesc, errSrc

' ========= Open File ===============
myEvent = "ERROR Trying to Open File for " & pjWkbkLongName
On Error GoTo line01


pjApp.FileOpenEx Name:=pjWkbkLongName, _
ReadOnly:="False", _
NoAuto:=True, _
FormatID:=""

' ========= Set Variable ===============

myEvent = "ERROR trying to set variable for " & pjWkbkLongName
Set pjProj = pjApp.ActiveProject

' ========= Publish ===============

myEvent = "ERROR Trying to Publish for " & pjWkbkLongName
pjApp.Publish

' ========= Close ===============
myEvent = "ERROR Trying to Close File for " & pjWkbkLongName
pjApp.FileCloseEx Save:=pjSave, CheckIn:=True

' =========== Write to Log File ===========

myEvent = "Published OK"
errNum = 0
errDesc = " "
errSrc = " "
LogComment_withError myEvent, errNum, errDesc, errSrc

Exit Sub

line01:

errNum = Err
errDesc = Err.Description
errSrc = Err.Source

LogComment_withError myEvent, errNum, errDesc, errSrc

End Sub

 

[Rod Gill]

Where and when does pjApp get created? If Project Pro is not open and
connected to Project Server when your code runs you must provide code that
does connect to PS. If not it fail.

What's the content of the pjWkbkLongName variable? Why are you using what
reads like a work book name to open a project?

--

Rod Gill
Microsoft MVP for Project - http://www.project-systems.co.nz

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

I am trying to write code that will run overnight to publish project files
to
project server, to the PWA. I have code in an Excel file that runs code to
open project server and publish 3 files. I utilize the Windows Scheduled
Tasks to launch an Autpen subroutine in the Excel file.

When I am logged on to the server and run the macro, the publishing goes
ok.
But when this runs overnight, the first file does not publish and gives an
Object required (number 424) error. (The code errors at the set pjProj =
pjApp.ActiveProject command, in my code at the end of this post).

I can not figure out why my being logged on matters or what is missing
(when
I am not logged on) for the first one or why after the first one the rest
publish ok.

I put error trapping code in the macro both to try and determine the
problem
and also to proceed forward if one file can not be published. This will be
necessary even once I overcome this issue because there may be other
reasons
unrelated to code errors why one of the files may not publish correctly
and I
do not want the whole effort to stop because of a problem with only one
file.

If anybody has any ideas, I would appreciate them. Below is the relevant
part of my code (a subroutine launched 3 times by other code):

*********************************

Public Sub Do_publishing(theEvent As String, theWkbkSN As String,
theWkbkLN
As String)


pjWkbkShortName = theWkbkSN
pjWkbkLongName = theWkbkLN

myEvent = theEvent
errNum = 0
errDesc = ""
errSrc = ""


LogComment_withError myEvent, errNum, errDesc, errSrc

' ========= Open File ===============
myEvent = "ERROR Trying to Open File for " & pjWkbkLongName
On Error GoTo line01


pjApp.FileOpenEx Name:=pjWkbkLongName, _
ReadOnly:="False", _
NoAuto:=True, _
FormatID:=""

' ========= Set Variable ===============

myEvent = "ERROR trying to set variable for " & pjWkbkLongName
Set pjProj = pjApp.ActiveProject

' ========= Publish ===============

myEvent = "ERROR Trying to Publish for " & pjWkbkLongName
pjApp.Publish

' ========= Close ===============
myEvent = "ERROR Trying to Close File for " & pjWkbkLongName
pjApp.FileCloseEx Save:=pjSave, CheckIn:=True

' =========== Write to Log File ===========

myEvent = "Published OK"
errNum = 0
errDesc = " "
errSrc = " "
LogComment_withError myEvent, errNum, errDesc, errSrc

Exit Sub

line01:

errNum = Err
errDesc = Err.Description
errSrc = Err.Source

LogComment_withError myEvent, errNum, errDesc, errSrc

End Sub

__________ Information from ESET Smart Security, version of virus
signature database 4899 (20100226) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4899 (20100226) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Steve Chermak]

Hello Rod,

Before the first subroutine, these variables are defined:

Option Explicit
Dim pjApp As MSProject.Application, pjProj As MSProject.Project
Dim tStart As Single
Dim pjWkbkLongName As String, pjWkbkShortName As String
(there are others for the error logging also)

This is the main subroutine that runs the program: (the part pertinent to
the question):

Sub Autpen()


Workbooks.Open Filename:="C:\VPMI_overnight\VPMI_PublishLog.xls"

If OpenProjectServer() Then
pjApp.DisplayAlerts = False


myProject = pjApp.ActiveProject.Name
myEvent = "Subroutines ready to begin"
errNum = 0
errDesc = "The proj name now is - " & myProject
errSrc = ""
LogComment_withError myEvent, errNum, errDesc, errSrc

' begin publishing efforts by populating variables and running
common subroutine

myEvent = "Began MDU NE publishing"

pjWkbkShortName = "VPMI MDU 2010 - NE CONSTRUCTION"
pjWkbkLongName = "<>\VPMI MDU 2010 - NE CONSTRUCTION"
Do_publishing myEvent, pjWkbkShortName, pjWkbkLongName


myEvent = "Began SFU NE publishing"

pjWkbkShortName = "VPMI SFU 2010 - NE CONSTRUCTION"
pjWkbkLongName = "<>\VPMI SFU 2010 - NE CONSTRUCTION"
Do_publishing myEvent, pjWkbkShortName, pjWkbkLongName



myEvent = "Began MTU NE publishing"

pjWkbkShortName = "VPMI MTU 2010 - NE CONSTRUCTION"
pjWkbkLongName = "<>\VPMI MTU 2010 - NE CONSTRUCTION"
Do_publishing myEvent, pjWkbkShortName, pjWkbkLongName

Also, this is the code to open Project Server to start with (the test in the
"If OpenProjectServer()" above):

Function OpenProjectServer()

tStart = Timer

On Error Resume Next
Shell "C:\Program Files\Microsoft Office\Office12\WINPROJ.EXE /s
""http://vpmiweb1/PWA"""
Do While pjApp Is Nothing And Timer < (tStart + 30)
'Set pjApp = GetObject(, "MSProject.Application")
Set pjApp = GetObject(, "MSProject.Application")
Loop

' Comment: If Project Opened Successfully, return this function as True, _
If NOT, return this function as false
If Not pjApp Is Nothing Then
OpenProjectServer = True
Else
OpenProjectServer = False
End If

End Function


The pjWkbkLongName is the variable for the projects in the PWA

Steve

 

[Rod Gill]

When your excel macro runs, under what account does it run, yours?

--

Rod Gill
Microsoft MVP for Project - http://www.project-systems.co.nz

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

Hello Rod,

Before the first subroutine, these variables are defined:

Option Explicit
Dim pjApp As MSProject.Application, pjProj As MSProject.Project
Dim tStart As Single
Dim pjWkbkLongName As String, pjWkbkShortName As String
(there are others for the error logging also)

This is the main subroutine that runs the program: (the part pertinent to
the question):

Sub Autpen()


Workbooks.Open Filename:="C:\VPMI_overnight\VPMI_PublishLog.xls"

If OpenProjectServer() Then
pjApp.DisplayAlerts = False


myProject = pjApp.ActiveProject.Name
myEvent = "Subroutines ready to begin"
errNum = 0
errDesc = "The proj name now is - " & myProject
errSrc = ""
LogComment_withError myEvent, errNum, errDesc, errSrc

' begin publishing efforts by populating variables and running
common subroutine

myEvent = "Began MDU NE publishing"

pjWkbkShortName = "VPMI MDU 2010 - NE CONSTRUCTION"
pjWkbkLongName = "<>\VPMI MDU 2010 - NE CONSTRUCTION"
Do_publishing myEvent, pjWkbkShortName, pjWkbkLongName


myEvent = "Began SFU NE publishing"

pjWkbkShortName = "VPMI SFU 2010 - NE CONSTRUCTION"
pjWkbkLongName = "<>\VPMI SFU 2010 - NE CONSTRUCTION"
Do_publishing myEvent, pjWkbkShortName, pjWkbkLongName



myEvent = "Began MTU NE publishing"

pjWkbkShortName = "VPMI MTU 2010 - NE CONSTRUCTION"
pjWkbkLongName = "<>\VPMI MTU 2010 - NE CONSTRUCTION"
Do_publishing myEvent, pjWkbkShortName, pjWkbkLongName

Also, this is the code to open Project Server to start with (the test in
the
"If OpenProjectServer()" above):

Function OpenProjectServer()

tStart = Timer

On Error Resume Next
Shell "C:\Program Files\Microsoft Office\Office12\WINPROJ.EXE /s
""http://vpmiweb1/PWA"""
Do While pjApp Is Nothing And Timer < (tStart + 30)
'Set pjApp = GetObject(, "MSProject.Application")
Set pjApp = GetObject(, "MSProject.Application")
Loop

' Comment: If Project Opened Successfully, return this function as True, _
If NOT, return this function as false
If Not pjApp Is Nothing Then
OpenProjectServer = True
Else
OpenProjectServer = False
End If

End Function


The pjWkbkLongName is the variable for the projects in the PWA

Steve





__________ Information from ESET Smart Security, version of virus
signature database 4906 (20100301) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4906 (20100301) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Steve Chermak]

Hi Rod,

I have tried to have the macro run both under my account and under the
administrator account for the server......either way it does not work right
when I am not logged on (as me or the administrator).

It seems worse trying to run it as the administrator, because in that mode
the first effort will error out at the publishing step, saying the method is
not available in this situation, then the second one will error saying object
required, and then the third will publish OK.

When I try to run it as me, then I just get the object required error the
first time, with the next two publishing OK.

Steve

 

[Rod Gill]

For the code to work, the active logon must have permission to open and
publish projects.

If one of the files doesn't work and the others do, make sure firstly that
the file isn't checked out (test for that first) then that the spelling is
100% accurate.

--

Rod Gill
Microsoft MVP for Project - http://www.project-systems.co.nz

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

Hi Rod,

I have tried to have the macro run both under my account and under the
administrator account for the server......either way it does not work
right
when I am not logged on (as me or the administrator).

It seems worse trying to run it as the administrator, because in that mode
the first effort will error out at the publishing step, saying the method
is
not available in this situation, then the second one will error saying
object
required, and then the third will publish OK.

When I try to run it as me, then I just get the object required error the
first time, with the next two publishing OK.

Steve



__________ Information from ESET Smart Security, version of virus
signature database 4907 (20100302) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4907 (20100302) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Steve Chermak]

Hi Rod,

I can both open and publish projects either with my logon or the
administrator logon, utilizing Project Professional 2007 to open the projects
on the server.

I also check the PWA to make sure that the files I am trying to publish
through code initiated by the Windows Scheduled Tasks are not on the force
check out list, before setting the Scheduled Tasks to start at a later time.
The three projects involved in this case are practice ones that are not being
used by someone else right now.

I have also changed the order of these to determine if it was something
pertinent to the original first one, and the same thing happens. The first
one errors no matter which of the three I have run first, and the other 2
publish.

Steve

 

[Rod Gill]

Try adding a 5 second delay before opening the first project. Let's just
make sure that project is fully loaded and ready to open projects.

--

Rod Gill
Microsoft MVP for Project - http://www.project-systems.co.nz

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

Hi Rod,

I can both open and publish projects either with my logon or the
administrator logon, utilizing Project Professional 2007 to open the
projects
on the server.

I also check the PWA to make sure that the files I am trying to publish
through code initiated by the Windows Scheduled Tasks are not on the force
check out list, before setting the Scheduled Tasks to start at a later
time.
The three projects involved in this case are practice ones that are not
being
used by someone else right now.

I have also changed the order of these to determine if it was something
pertinent to the original first one, and the same thing happens. The first
one errors no matter which of the three I have run first, and the other 2
publish.

Steve



__________ Information from ESET Smart Security, version of virus
signature database 4910 (20100302) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4910 (20100302) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Steve Chermak]

Hi Rod,

I put the delay in and still got the same results. Then I decided to try a
different approach and put the code in a microsoft project file instead of
excel, and eliminated the use of excel altogether by writing my log
information to a text file.

It seemed to work better but I still had problems.

But then I put code in to have a 70 second delay between publishing and
closing the file, to give Project time to properly save the project to the
server. This happens now for all 3 of the projects I am publishing.

After this, I got it to work OK running it both as me and as the
administrator, while not logged on and running through Windows Scheduled
Tasks.

Separate from the code in the Project file, I have the Project Professional
Program opening up into the server when it launches, and Automatically
detecting the connection state (so there is no prompt as this runs
overnight), and disabled the code to open Project Server as it already should
be in Project Server.

I am not sure if this is better than having it open up into the computer
mode and then opening Project Server in the code, but it seems to work.

I will have to test this further to make sure it really works all the time
as it should. Is how I have done this the best approach, or should I consider
launching this effort some other way, if there is a better way?

Thanks for the help you have given me.

Steve

 

[Rod Gill]

At this stage, whatever approach works is good!

--

Rod Gill
Microsoft MVP for Project - http://www.project-systems.co.nz

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