Problem with VBA Code for Massive Publish

[Incanus]

Hello:

I've created a small VBA program for Project Professional 2007 in
order to implement massive publishing automation tasks.

The program uses a list of names of Gantts currently available in
Project Server, each one created as an individual Project task. The
task list is written in the same MPP file that contains the VBA
program.

As the Project Professional session is already logged with Project
Server, the VBA program simply loads each Gantt from the list on
Project Professional, attempts to publish it and then closes the
Gantt.

"The rub" is that, on receiving the publish VBA command, Project
Professional opens the publishing window, awaiting for a user click on
the Publish button.

Thus, a batch process becomes an interactive process... which defeats
a big part of the program functionality.

Here follows the VBA code for Project Professional 2007:

Sub Publicacion_Normal()
' Publicacion_Normal

Dim Tarea As Task
Dim Gantt As String

' Each task corresponds to a Gantt in Project Server that needs
publishing
For Each Tarea In ActiveProject.Tasks

' Creating the loading string for the given Gantt
Gantt = "<>\" & Tarea.Name

' Opening the given Gantt from Project Server
FileOpenEx Name:=Gantt, ReadOnly:=False

'Save the given Gantt before publishing it
FileSave

'Publishing the given Gantt
Publish

'SendKeys "{ENTER}"

'Waiting for the publishing process to be finished
TiempoPausa = 20
Inicio = Timer
Do While Timer < Inicio + TiempoPausa
DoEvents
Loop

'Closing the given Gantt in protected state
FileClose Save:=pjDoNotSave, NoAuto:=True

Next Tarea

End Sub

Note that the 'SendKeys "{ENTER}" command was an attempt to force a
click on the publishing window's Publish button (it didn't work).

I'll welcome any indication that could help me solve this problem.

Thanks in advance,

Sebastián Armas
Project Manager

 

[Jack Dahlgren]

At the beginning of the code insert the following line:

displayalerts = false

then at the end, displayalerts = True

That should take care of the popup, but post back if it doesn't

-Jack Dahlgren

 

[John]

Hello:

I've created a small VBA program for Project Professional 2007 in
order to implement massive publishing automation tasks.

The program uses a list of names of Gantts currently available in
Project Server, each one created as an individual Project task. The
task list is written in the same MPP file that contains the VBA
program.

As the Project Professional session is already logged with Project
Server, the VBA program simply loads each Gantt from the list on
Project Professional, attempts to publish it and then closes the
Gantt.

"The rub" is that, on receiving the publish VBA command, Project
Professional opens the publishing window, awaiting for a user click on
the Publish button.

Thus, a batch process becomes an interactive process... which defeats
a big part of the program functionality.

Here follows the VBA code for Project Professional 2007:

Sub Publicacion_Normal()
' Publicacion_Normal

Dim Tarea As Task
Dim Gantt As String

' Each task corresponds to a Gantt in Project Server that needs
publishing
For Each Tarea In ActiveProject.Tasks

' Creating the loading string for the given Gantt
Gantt = "<>\" & Tarea.Name

' Opening the given Gantt from Project Server
FileOpenEx Name:=Gantt, ReadOnly:=False

'Save the given Gantt before publishing it
FileSave

'Publishing the given Gantt
Publish

'SendKeys "{ENTER}"

'Waiting for the publishing process to be finished
TiempoPausa = 20
Inicio = Timer
Do While Timer < Inicio + TiempoPausa
DoEvents
Loop

'Closing the given Gantt in protected state
FileClose Save:=pjDoNotSave, NoAuto:=True

Next Tarea

End Sub

Note that the 'SendKeys "{ENTER}" command was an attempt to force a
click on the publishing window's Publish button (it didn't work).

I'll welcome any indication that could help me solve this problem.

Thanks in advance,

Sebastián Armas
Project Manager

Sebastian,
If Jack's suggestion doesn't work, and I suspect it may not since what
you are seeing is a window not an alert message, try the following. The
Publish Method has two arguments and even though both arguments are
optional, sometimes VBA wants to see a value for at least one of the
arguments. In your code include an argument with the Publish command and
see if that helps.

John
Project MVP

 

[Jack Dahlgren]

John,

Despite what you suspect, DisplayAlerts = False DOES eliminate that box
during publishing. It is simple to test it to allay any suspicion. Give it a
try! It will take less than a minute:

Sub publishmewithannoyingmessagebox()
Publish
End Sub

Sub publishmewithOUTannoyingmessagebox()
DisplayAlerts = False
Publish
DisplayAlerts = True
End Sub

-Jack

 

[Incanus]

Hello:

Thank you all for your kind and prompt replies. I tried _both_
suggestions:

DisplayAlerts = False
Publish Republish:=Null, WssUrl:=Null
DisplayAlerts = True

....to no avail, sadly enough. The publishing window keeps popping up.

Bypass alternative: is there anyway in VBA to tell the publishing
window to click at the Publish button?

Thanks in advance,

Sebastián Armas
Project Manager

P.S. Sory for the quoted text. I'm sending a copy of it all to my
work's e-mail

 

[John]

John,

Despite what you suspect, DisplayAlerts = False DOES eliminate that box
during publishing. It is simple to test it to allay any suspicion. Give it a
try! It will take less than a minute:

Sub publishmewithannoyingmessagebox()
Publish
End Sub

Sub publishmewithOUTannoyingmessagebox()
DisplayAlerts = False
Publish
DisplayAlerts = True
End Sub

-Jack

Jack,
Unfortunately it's something I can't test because I don't use Project
Server. I only jumped in because I remember having a similar problem
with another VBA method with optional arguments, (I don't recall exactly
which method), and I had to resolve it, (i.e. keep the window from
popping up), by providing at least one argument.

Nonetheless, it looks like Sebastian tried both our suggestions but
neither worked. Too bad. I don't have any other suggestions. Hopefully,
you will.

John
Project MVP

 

[Jack Dahlgren]

The code you posted below works for me in VBA without a dialog box popping up.
If I comment out the displayalerts = false then it displays the dialog box.

Try the simple process I posted first to confirm that it is the issue. Then
look at the rest of your code to figure out what else might be going on.

Run two cases exactly like this:

sub pubme()
DisplayAlerts = False
Publish Republish:=Null, WssUrl:=Null
DisplayAlerts = True
end sub

sub pubme2()
'DisplayAlerts = False
Publish Republish:=Null, WssUrl:=Null
DisplayAlerts = True
end sub

Do both display the alert?

-Jack Dahlgren

 

[Jack Dahlgren]

John,
No problem. I've solved that issue in the past, and the solution works on
every machine I have available. I can't replicate his issue.

I think he needs to isolate that part of his code and see if there is
something else going on. If the code is in the Enterprise global then
sometimes you need to quit project and restart to reload the global to see
the effects of the change.

Other than that, it should work.

-Jack Dahlgren

 

[John]

John,
No problem. I've solved that issue in the past, and the solution works on
every machine I have available. I can't replicate his issue.

I think he needs to isolate that part of his code and see if there is
something else going on. If the code is in the Enterprise global then
sometimes you need to quit project and restart to reload the global to see
the effects of the change.

Other than that, it should work.

-Jack Dahlgren

Jack,
Thanks for the feedback.
John

 

[Incanus]

Jack:

Bellow you wrote:

John,
No problem. I've solved that issue in the past, and the solution works on
every machine I have available. I can't replicate his issue.

I think he needs to isolate that part of his code and see if there is
something else going on. If the code is in the Enterprise global then
sometimes you need to quit project and restart to reload the global to see
the effects of the change.

How you do that, exactly?

Thanks in advance,

Sebastián Armas

 

[Brian Lukanic]

Jack,

Thank you so much for being the first post on this boards to show an example
of the WssURL syntax. I have been looking all over the web for an actual
syntactical example.

I am going to try that in a similar VBA I am writing. But I want to take it
a bit further, can you tell me:
1. Is it possible to have VBA not only publish the URL but ALSO tell the
server to make the published project be a subproject of an existing
workspace? My VBA code has a variable called "parentworkspace" that I want to
populate the dropdown with. As the original poster mentioned, it is fruitless
to have this macro if it requires user intervention.
2. As a followup, let's say I can't programmatically check the box that says
"make this be a subproject." Then can I simply make the WSS URL be
"parentworkspace/childworkspace" to force the same effect?
3. What does republish:=Null mean? Do I need to use the republish syntax if
I simply want to publish for the first time and include a WssURL? (such as,
"Publish WssUrl:=parentworkspace/childworkspace"
4. Lastly - and I will try this by myself to find out for sure - does the
WSS URL need to be the FULL http://foo.com/PWA/parent/child URL, or do I
simply enter "parent/child" in as the WssURL? I am assuming it is the latter.

Brian

 

[Jack Dahlgren MVP]

I haven't explored the way that parent child workspaces work so I can't
answer if it is enough just to put the workspace under it like this:

http://myprojectserver.company.com/PWA/parentwebsite/childwebsite

But give it a try. There is no better way to learn!
You could easily create this string and I think there are some properties
which will give you the server URL which you can use in place of
hard-coding. I don't have project available now to look up the specific
property, but search the object browser for URL and you should find it.

Republish controls whether the publish is incremental or complete.
Republish republishes the whole plan. I can imagine you want to do that on
the first pass, but after that the incremental publish is probably
sufficient.

-Jack Dahlgren

 

[Jack Dahlgren MVP]

As I wrote before:

Run two macros exactly like this:

sub pubme()
DisplayAlerts = False
Publish Republish:=Null, WssUrl:=Null
DisplayAlerts = True
end sub

sub pubme2()
'DisplayAlerts = False
Publish Republish:=Null, WssUrl:=Null
DisplayAlerts = True
end sub

Do they both pop up the message?

Do not put them in the enterprise global. Just run them from a file.
If they both give a message, then I don't know what to say. The first one
should NOT give a pop up asking if you want to save.

If the first one is fine and the second gives a message then we need to look
at the rest of your code and see what is going on.

-Jack Dahlgren

----- Original Message -----
From: "Incanus" <[email protected]>
Newsgroups: microsoft.public.project.developer
Cc: <[email protected]>
Sent: Thursday, September 10, 2009 2:47 PM
Subject: Re: Problem with VBA Code for Massive Publish


Jack:

Bellow you wrote:

John,
No problem. I've solved that issue in the past, and the solution works on
every machine I have available. I can't replicate his issue.

I think he needs to isolate that part of his code and see if there is
something else going on. If the code is in the Enterprise global then
sometimes you need to quit project and restart to reload the global to see
the effects of the change.

How you do that, exactly?

Thanks in advance,

Sebastián Armas

 

[Incanus]

Jack:

Here's my current code, wich is running in a separate MPP file, not in
the global, mind you. I call de Publicacion_Normal() sub that then
calls the Publica() ...and the pop up is still there. Any suggestions?

Sub Publica()
'Se publica la Gantt
DisplayAlerts = False
Publish Republish:=Null, WssUrl:=Null
DisplayAlerts = True
End Sub

Sub Publicacion_Normal()
' Publicacion_Normal
' Macro grabada el vie 8/14/09 por BANCO\sarmas.
' Iterador en base a lista de tareas para publicación de Gantts en
Project Server

Dim Tarea As Task
Dim Gantt As String

' Se itera en la lista de tareas
' cada tarea tiene el nombre de una Gantt de Project Server a Publicar
For Each Tarea In ActiveProject.Tasks

' Se crea el string de carga de la Gantt
Gantt = "<>\" & Tarea.Name

' Se abre la Gantt de Project Server
FileOpenEx Name:=Gantt, ReadOnly:=False

'Se graba previo a la publicación
FileSave

'Se publica la Gantt
Call Publica

'SendKeys "{ENTER}"

TiempoPausa = 20 ' Asigna hora de inicio.
Inicio = Timer ' Establece la hora de inicio.
Do While Timer < Inicio + TiempoPausa
DoEvents ' Cambia a otros procesos.
Loop

'Se cierra la Gantt sin cambios y protegida
FileClose Save:=pjDoNotSave, NoAuto:=True

Next Tarea

End Sub

 

[Jack Dahlgren MVP]

What happens when you run the code I showed below?
-Jack

Jack:

Here's my current code, wich is running in a separate MPP file, not in
the global, mind you. I call de Publicacion_Normal() sub that then
calls the Publica() ...and the pop up is still there. Any suggestions?

Sub Publica()
'Se publica la Gantt
DisplayAlerts = False
Publish Republish:=Null, WssUrl:=Null
DisplayAlerts = True
End Sub

Sub Publicacion_Normal()
' Publicacion_Normal
' Macro grabada el vie 8/14/09 por BANCO\sarmas.
' Iterador en base a lista de tareas para publicación de Gantts en
Project Server

Dim Tarea As Task
Dim Gantt As String

' Se itera en la lista de tareas
' cada tarea tiene el nombre de una Gantt de Project Server a Publicar
For Each Tarea In ActiveProject.Tasks

' Se crea el string de carga de la Gantt
Gantt = "<>\" & Tarea.Name

' Se abre la Gantt de Project Server
FileOpenEx Name:=Gantt, ReadOnly:=False

'Se graba previo a la publicación
FileSave

'Se publica la Gantt
Call Publica

'SendKeys "{ENTER}"

TiempoPausa = 20 ' Asigna hora de inicio.
Inicio = Timer ' Establece la hora de inicio.
Do While Timer < Inicio + TiempoPausa
DoEvents ' Cambia a otros procesos.
Loop

'Se cierra la Gantt sin cambios y protegida
FileClose Save:=pjDoNotSave, NoAuto:=True

Next Tarea

End Sub

 

[Incanus]

Jack:

Sorry for my late reply: I was out of the office on holiday

When I ran:

DisplayAlerts = False
Publish Republish:=Null, WssUrl:=Null
DisplayAlerts = True

The Publishing window kept popping up, waiting for me to press the
"Publish" button.

Is there an alternative way to do this, say, a scripted batch file
using command line interface?

I do know about the "stsadm" command, but it doesn't seem to have any
publishing options.

Awaiting your kind advice,

Sebastián Armas
Project Manager

 

[Jack Dahlgren MVP]

Hmm...

Very odd. I can not reproduce your issue.
I think you can publish through the PSI.

-Jack Dahlgren

Jack:

Sorry for my late reply: I was out of the office on holiday

When I ran:

DisplayAlerts = False
Publish Republish:=Null, WssUrl:=Null
DisplayAlerts = True

The Publishing window kept popping up, waiting for me to press the
"Publish" button.

Is there an alternative way to do this, say, a scripted batch file
using command line interface?

I do know about the "stsadm" command, but it doesn't seem to have any
publishing options.

Awaiting your kind advice,

Sebastián Armas
Project Manager

 

[Incanus]

Jack:

I believe so, too. Can PSI be used from VBA?

Regards,

Sebastián Armas
Project Manager

 

[Rod Gill]

Yes, but it's not pretty. You are better creating a stand alone program that
uses it.

--

Rod Gill
Microsoft MVP for Project

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



Jack:

I believe so, too. Can PSI be used from VBA?

Regards,

Sebastián Armas
Project Manager

 

[Incanus]

Rod:

OK. Not pretty... mind you, I'm not giving up on VBA, but, seeing as
I've already tried the pretty approach with VBA, to no avail: i.e. the
Publish windows keeps appearing, and I can't "click" on it's
"Publish" button through VBA (see code above)...

....How would you use from VBA the Project.QueuePublish method to
publish a project without creating any workspace, merely publishing
the project from it's current draft version?

Awaiting your kind reply,

Sebastián Armas
Project Manager