Handling Prompts

[Eric Frederich]

Hello,

I'm not a VB guy or really a Project Server guy but I have been tasked with
writing a little macro to open, calculate, save, and publish every project in
our database.

For some projects this is straight forward but for others there are modal
prompts which freeze this automation such as "The currency for this project
is different from the currency specified in the enterprise global.....
....Click Yes to overwrite this project's currency setting with that of the
enterprise global or click No to keep this project's setting."

Also, sometimes even though I'm doing a "FileSave" right before my
"Application.PublishAllInformation", I am still prompted sometimes saying
"When publishing to Project Server, your project will be saved. Do you want
to proceed?"

Basically, I'd like to know how (if it is possible) to bypass some of the
prompts that I'm getting so that I can spin through all of the projects in
the database without having someone sitting there clicking "yes", "yes",
"yes".

Below is a code excerpt. When the form loads it populates a ListBox with
all of the project names which it gets from an SQL query on the database.
Those project names are stored in ListBox1.List(i,0)

Thanks in advance,
~Eric

Private Sub ProcessSelectedButton_Click()
MsgBox "ListCount : " & ListBox1.ListCount
For i = 0 To ListBox1.ListCount - 1 Step 1
If ListBox1.Selected(i) Then
ListBox1.List(i, 1) = "selected"

FileOpen "<>\" & ListBox1.List(i, 0) 'Opens enterprise project
ListBox1.List(i, 1) = "FileOpen complete"

Application.CalculateProject
ListBox1.List(i, 1) = "CalculateProject complete"

FileSave ' doing a save here gets rid of the prompt caused by
publishing an unsaved project
ListBox1.List(i, 1) = "FileSave complete"

' Sometimes even though we saved the project above, we are still
asked ...
' "When publishing to Project Server, your project will be
saved. Do you want to proceed?"
Application.PublishAllInformation
ListBox1.List(i, 1) = "PublishAllInformation complete"

FileClose pjDoNotSave ' no need to save again
ListBox1.List(i, 1) = "FileClose complete"
Else
ListBox1.List(i, 1) = "-"
End If
Next i

End Sub

 

[Jonathan Sofer]

Try adding the line of code: DisplayAlerts = False

You can also add: MSProject.Application.ScreenUpdating = False

This will prevent the screen from showing all the work that the macro is
doing and making it look like it has gone crazy

Jonathan Sofer

 

[Eric Frederich]

Thanks Jonathan for the tip but it didn't work.

I just tried it and I still get this message...
"Your project needs to be saved now before this operation can continue.
Do you want to proceed?"

I looked at the help (F1) on DisplayAlerts and it says...
"True if Microsoft Office Project 2003 displays error messages when a macro
runs. Read/write Boolean."
This only seems to stop the showing VB errors, not MS Project 'yes/no' type
prompts.

Is there a way to use these commands without user interaction for batch type
operation.

Thanks,
~Eric

 

[Jack Dahlgren]

Display alerts will disable those "Are you sure? questions from the
application, not just the vba. So it seems that perhaps the prompt is
something different.
I'm not sure about Proj2003, but the first thing I'd try is turning off the
planning wizard to see if that helps.

-Jack Dahlgren

 

[Eric Frederich]

I set it to false and I still get messages like ...

"The start of task 110 Some Task of "Some Project.Published" is before the
project starts.
Try one of the following:
If you want the project to begin on this date, change the project start date.
If you entered a constraint that results in a task start date earlier than
the project start date, change either the constraint or the project start
date"

I also still get these types of messages.
"Your project needs to be saved now before this operation can continue.
Do you want to proceed?
OK, Cancel"
I get those right after a FileSave and right before a PublishAllInformation.

So right now this thing is useless as a batch processing tool if someone
needs to sit there and keep hitting "okay", "yes", "okay"

Below is the entire source ....

Private Sub ProcessSelectedButton_Click()
Dim projectName As String
For i = 0 To ListBox1.ListCount - 1 Step 1
If ListBox1.Selected(i) Then
projectName = ListBox1.List(i, 0)
ListBox1.List(i, 1) = "selected"
StatusLabelDynamic.Caption = projectName & " selected"

FileOpen "<>\" & projectName 'Opens enterprise project
ListBox1.List(i, 1) = "FileOpen complete"
StatusLabelDynamic.Caption = projectName & " FileOpen complete"

Application.CalculateProject
ListBox1.List(i, 1) = "CalculateProject complete"
StatusLabelDynamic.Caption = projectName & " CalculateProject
complete"

FileSave ' doing a save here gets rid of the prompt caused by
publishing an unsaved project
ListBox1.List(i, 1) = "FileSave complete"
StatusLabelDynamic.Caption = projectName & " FileSave complete"

Application.PublishAllInformation
ListBox1.List(i, 1) = "PublishAllInformation complete"
StatusLabelDynamic.Caption = projectName & "
PublishAllInformation complete"

FileClose pjDoNotSave ' no need to save again
ListBox1.List(i, 1) = "FileClose complete"
StatusLabelDynamic.Caption = projectName & " FileClose complete"
Else
ListBox1.List(i, 1) = "-"
End If
Next i
StatusLabelDynamic.Caption = "Finished"
End Sub

Private Sub RefreshButton_Click()
RefreshProjectList
MsgBox "Project List Refreshed"
End Sub


Private Sub RefreshProjectList()
Dim myWorkSpace As Workspace
Dim myConnection As Connection
Dim myRecordSet As Recordset

Set myWorkSpace = CreateWorkspace("*****", "*****", "*****", dbUseODBC)
Set myConnection = myWorkSpace.OpenConnection("*****")
Set myRecordSet = myConnection.OpenRecordset("Select proj_name from
msp_projects where proj_type=0")

ListBox1.Clear
Do While Not myRecordSet.EOF
Dim currentProjectName As String
currentProjectName = myRecordSet("proj_name")
ListBox1.AddItem currentProjectName
myRecordSet.MoveNext
Loop
End Sub


Private Sub UserForm_Initialize()
MSProject.Application.DisplayPlanningWizard = False
MsgBox "Warning!" & vbCrLf & _
"You are about to use a tool which blindly accepts and publishes
enterprise data. Use at own risk!" & vbCrLf & _
"You should NOT use this on a wireless connection."
RefreshProjectList
End Sub

 

[www.projectserver.cz]

The magic formula is

Application.DisplayAlerts =wdAlertsNone

 

[Jack Dahlgren]

Hmm... try
OptionsSchedule ScheduleMessages:=False
Beyond that you might need sendkeys.

-Jack Dahlgren

 

[Jack Dahlgren]

Isn't wdAlertsNone a microsoft word property?
It doesn't show up unless you reference the word library.
I don't see how it would work in this instance, but with Microsoft tools you
never know.

-Jack Dahlgren

 

[Eric Frederich]

Thanks. Without too much testing this seems to work but maybe I'm speaking
too soon. As Jack Dahlgren said above, wdAlertsNone doesn't how up in the F1
help anywhere. If you know where this is documented please tell us, or is it
just something that you know?

This doesn't fix all of the prompts though. I am still getting some.

I am afraid I have reached my limits on VB programming but I'd like to know
if it is possible to try to run a routine (open project, save project,
publish all information, save project, close project) and set a timeout of 1
minute so that if there is a prompt and it hangs it can go on to the next one?
Would this require some sort of threading?

Thanks,
~Eric