Attempting to use automation to add/delete tasks from an MS Project file. The following code is giving me problems...in the On Delete event of a form, I want to check to be sure a corresponding task in the MSProject file exists, and if so delete it:

Private Sub Form_Delete(Cancel As Integer)
Dim ProjectNo As String
ProjectNo = Forms![frmShopOrders]![ProjectNo]

Dim prjApp As MSProject.Application
Dim prjProject As MSProject.Project
Dim prjTask As MSProject.Task

Dim tName As String
tName = ProjectNo & ", " & Forms![frmShopOrders]![ShopOrderNo] & "-" & Me.TrackingDescription

Set prjApp = CreateObject("MSProject.Application")
prjApp.FileOpen "C:\Documents and Settings\jlw\My Documents\Copy of Mach Backlog.mpp"
Set prjProject = prjApp.ActiveProject

'Check to make sure the task exists in Project before attempting to delete.
For Each prjTask In prjProject.Tasks
If prjTask.Name = tName Then
'Task Exists, OK to delete
With prjProject
.Tasks(tName).Delete
End With
Else
MsgBox "The task was not found in the Project file." 'alert user that the task was not found in the project file.
End If
Next prjTask
End Sub

The error is highlighting the If prjTask.Name=tName line, and if I hover over prjTask, it says "prjTask=Nothing" so I'm guessing it isn't realizing what it is, but I declared it above (prjTask=MSProject.Task). Any help would be appreciated.

you decalred it but I don't see where you are then setting it:

I want it to loop through all of the tasks (For Each prjTask In prjProject.Tasks) to check for a match to the record I'm attempting to delete in Access (man it would be a whole lot easier if there was a DLookup or similar function in Project but as far as I know there is not).

I think I understand, so how about something like this: (WARNING: AIRCODE AT ITS MOST EXTREME!)

Your code regarding the MS Project object model seems to be OK, what if you run this in MS Project module?

Dim prjTask As Task
For Each prjTask In ActiveProject.Tasks
Debug.Print prjTask.Name
Next

Does the tasks get listed in the immediate pane?

Also, try to add this line:

MsgBox prjProject.Name

in your code.

Does the name show allright?

Martin

Hmmm...interesting, when I try to run the code as you suggest from the MS Project module it is also giving me the runtime error 91 and highlighting the same line.

There is no MoveFirst with project.

Does anyone know if I can set up an ODBC connection to Project and then be able to analyze the recordset rather than looping in Project? I've been looking but I'm not finding anything in cyber world about this...

Isn't the activeproject file empty then?

this means that the code is OK, but your file is not, obviously... or try to refer to the project by its name, not by Activeproject (relying on active objects is a bad habit, anyway)

Martin

<<still lurking in the shadows because I tried to ineffectively help, but ultimately would like to see how this gets resolved>>

Martin
<<in all humility...just trying to understand and learn>>:notworthy:
Isn't the idea behind "Active" referencing so that you can dynamically refer to a path, or an object, or a file without hard-coding your code? Please expand upon why "relying on active objects is a bad habit".

Derek- in my experience with MSProject automation a blank line in your project causes an error like this. It's like it's part of the task collection but it's not a task. I use code like this a lot:

You're my hero, Clippit, that solved the problem! Thank you very much for your help.

Rbianco, I too am curious as to why relying on active objects is a bad habit?

Happy to help!

Re ActiveProject: Without putting words in Martin's mouth, I suspect his concern is just making an assumption about the stae of things. If you know the name you expect, use it and don't assume that project is active. I have code that specifically works on whatever project is showing in the window- in that case I'd say I'll need something like ActiveProject as I won't know what project name to expect.

Gotcha. Thanks for the explanation (and again, for your solution to my problem).

One more question, if you don't mind (you seem to know a thing or two about Project automation which is a rare skill). I want to know if there were no matching records found in the task list that match my criteria but I can't figure out how to do that (I haven't used MS Project very much at all, and the VBA is quite different from Access/Excel VBA). If you can help I'd appreciate it.

Matching based on what? Description? I don't think there's anything built into Project but I can think of three approaches:
- read them all and write them to a temporary table and query it using a totals query
- read them all into an array in memory, sort it, and read through looking to see if any match the next
- loop through them all and for each task read through them all again (skipping the current one by id) and see if any match

There will be other approaches that would work.

Hello Ron,

working with active object is perfectly OK, if you have an Excel macro in Personal.xls for instance, that you want to use with any open workbook and the active sheet.

Example? This will change the text in activecell to Sentence case:


But, when you write a file specific macro (I mean, concrete file is being opened and worked with) it's always better to refer to it by its name (or index, but it's also not very reliable).

This enables you to write very efficient code (e.g. for Excel):

Workbooks("Book1.xls").Sheets("Sheet1").Range("A1").Value = Workbooks("Book2.xls").Sheets("Sheet2").Range("A1").Value

Note there is no activating, no copying, and no pasting.

When you work with active objects in Applications like MS Project, or MS Excel, you must always make sure that the concrete object is active, so that you don't overwrite values in the other file that may be also opened.

Remember, you never know what the user has been doing before you run your code. There may be another code running in the background hosted by another instance of the application! That is why I consider relying on active objects a bad habit.

P.S. I am also lurking in the shadows, I use VBA with MS Project very very rarely Same as you, I was attracted by this 'exotic' thread

Martin

Hi Steve,

1) thank you for solving the problem (very useful hint)

2) yes, this is what I had in mind. You never know what the user has done before you triggered the code. If you know the file/sheet/project/document...etc... name, use it (or assign the file pointer to an object variable).

Regards,

Martin

I'm almost with you...I think approach number 3 is kind of what I'm after. So the code I have right now (that you assisted with) is the following:

For Each prjTask In prjProject.Tasks
If Not prjTask Is Nothing Then 'Skip blank rows-Project Automation doesn't allow blank rows
If prjTask.Name = tName Then
With prjProject
.Tasks(tName).Duration = nDur & "hours"
.Tasks(tName).ResourceNames = tResource
If IsNull(Me.TrackingNeedBy) Then
Else
.Tasks(tName).Finish = dNeedBy
End If
End With
End If
End If
Next prjTask

My code then, is searching for task names that match tName (defined above in code but for simplicity sake let's say that's a control on my form). When it finds a match, it is then setting the duration and resource names for that task. I want to know if there were no tasks that matched tName. I can't see that there is any way to count the number of matches?

So are you trying to find out if there are no matches, or if there are multiple? I;ve never tried accessing a member of the tasks collection using its name so i don't know what that will do. Is it working so far?

Yes, it works fine. I want to find out if there are no matches (theoretically, there shouldn't be more than one, but if there are it's OK). The idea is that each task in my Access form should have a matching task in the Project file, so I want to know if there weren't any matches found.

I think you'll get an error if you try to refer to a task by name and there's no task with that name. You'd have to trp the error and than take whatever action is appropriate when there's no match.

I'll try that, thanks.