Identify if Summary and note in another column

[dew]

I'm a novice Excel VBA programmer and way below that with Project VBA, so
please help because I'm spinning my wheels.

I want to identify if something is a Summary task and put a flag to that
effect in another column. I've got the id part, but not the flag to another
column. Here's my code:

Sub TestFont()

Dim T As Task

TableEdit Name:="&Entry", TaskTable:=True, NewName:="", FieldName:="",
NewFieldName:="Text14", Title:="Font", Width:=10, Align:=2, ShowInMenu:=True,
LockFirstColumn:=True, RowHeight:=2, ColumnPosition:=0, AlignTitle:=1
TableApply Name:="&Entry"

For Each T In ActiveProject.Tasks

If Not (T Is Nothing) Then

If T.Summary Then
SetTaskField Field:="Text14", Value:="summary"
'MsgBox T.Name
End If

End If

Next T

End Sub

 

[JackD]

I'm a novice Excel VBA programmer and way below that with Project VBA, so
please help because I'm spinning my wheels.

I want to identify if something is a Summary task and put a flag to that
effect in another column. I've got the id part, but not the flag to another
column. Here's my code:

Sub TestFont()

Dim T As Task

TableEdit Name:="&Entry", TaskTable:=True, NewName:="", FieldName:="",
NewFieldName:="Text14", Title:="Font", Width:=10, Align:=2, ShowInMenu:=True,
LockFirstColumn:=True, RowHeight:=2, ColumnPosition:=0, AlignTitle:=1
TableApply Name:="&Entry"

For Each T In ActiveProject.Tasks

If Not (T Is Nothing) Then

If T.Summary Then
SetTaskField Field:="Text14", Value:="summary"
'MsgBox T.Name
End If

End If

Next T

End Sub

Here is how I'd do it.

Sub SummaryStuff()
Dim T As Task
For Each T In ActiveProject.Tasks
If Not T Is Nothing Then
If T.Summary Then
T.Text14 = "Summary"
End If
End If
Next T
End Sub


-Jack

 

[dew]

Thanks so much! I really appreciate your speedy response.

You probably can help me with another one: I would like to capture the
field names as a row, or list, or file -- something visible that I can ue
outside of Project. How would I do that?

dew

 

[JackD]

Take a look at my website for several examples.
http://masamiki.com/project/macros.htm

I just posted a new one today dealing with field names that you can look at.
You can borrow bits and pieces from the export to excel or Write Properties
macros to see how to write to a text field or excel.

Take a look at them and then post back here if you have questions.

-Jack

 

[dew]

Thanks for replying. I hadn't realized that you did reply since I didn't
receive an email notice. Now I see the checkbox at the bottom. In the
meantime I figured out how to get the field names. What I'd like to do now
is just write the field names to a txt file. I don't know how to do that.
Here's what I have so far:

Sub CopyTitles()

Dim C As Integer
Path = "c:\Documents and Settings\All Users\Desktop\headers.txt"

SelectAll
ColCount = Selection.Columns.Count

For col = 1 To ColCount
SelectCellRight
Path = ActiveCell.FieldName
Next col

End Sub

 

[dew]

Thanks for replying. I hadn't realized that you replied because I didn't get
an email notice. Now I see the checkbox at the bottom. In the meantime I
figured out how to get the field names. Now I'd just like to write them to a
txt file. I don't know how to do that. Here's what I have so far:

Sub CopyTitles()

Dim C As Integer
Path = "c:\Documents and Settings\All Users\Desktop\headers.txt"

SelectAll
ColCount = Selection.Columns.Count

For col = 1 To ColCount
SelectCellRight
Path = ActiveCell.FieldName
Next col

End Sub

 

[dew]

Thanks for replying. I hadn't realized that you replied because I didn't get
an email notice. Now I see the checkbox at the bottom. In the meantime I
figured out how to get the field names. Now I'd just like to write them to a
txt file. I don't know how to do that. Here's what I have so far:

Sub CopyTitles()

Dim C As Integer
Path = "c:\Documents and Settings\All Users\Desktop\headers.txt"

SelectAll
ColCount = Selection.Columns.Count

For col = 1 To ColCount
SelectCellRight
Path = ActiveCell.FieldName
Next col

End Sub

 

[dew]

Thanks for replying. I hadn't realized you replied because I didn't receive
an email notice. Now I see the checkbox at the bottom. I'd like to write
teach task name into a txt file. The code below is a guide to what I want to
do, but it doesn't work.

Sub CopyTitles()

Dim ColCount As Integer
Dim Path, header As String

Path = "c:\Documents and Settings\All Users\Desktop\headers.txt"
Open Path For Output As #1

SelectAll
ColCount = Selection.Columns.Count
MsgBox ColCount

For col = 1 To ColCount
SelectCellRight
header = ActiveCell.FieldName
If col <> ColCount Then Write #1, header;
MsgBox header
Next col

Close #1
End Sub

 

[dew]

Sorry for all the posts. I got an error each time and didn't know that
they'd all end up posting. I've been trying to get on the forum all morning.
All I need now is how to count the number of used columns or how to use
For-Each to loop through each column. The code below does what I need, but I
won't know the number of columns in advance.

Thanks!

Sub CopyTitles()

Dim ColCount, col As Integer
Dim Path, header As String

Path = "c:\Documents and Settings\All Users\Desktop\headers.txt"
Open Path For Output As #1

SelectRow (1)

For col = 1 To 10
SelectCellRight
header = ActiveCell.FieldName
Write #1, header;
' MsgBox header
Next col

Close #1

End Sub

 

[JackD]

You could just run from col 1 to 100 (or someother suitably large number)
and use On Error to either exit the sub or exit the loop.

 

[dew]

When I tried that it just keeps repeating the name of the last column over
and over up until the number of the counter.

dew

 

[JackD]

Did you try something like this?

For i = 1 To ActiveProject.TaskTables(1).TableFields.Count
(Note that the first field is often the ID field so you probably need to
start counting at 2)

 

[dew]

Well, that code "sort of" works. I tried it on several different project
files from different PMs. On most of the files it left off several of the
fields. I can't see the commonality. Some have columns before the task
column and it worked on some and not others. This is the code I used:

Sub TestCopyHeaders()

Dim ColCount As Integer
Dim Path, header As String

Path = "c:\Documents and Settings\All Users\Desktop\headers.txt"
Open Path For Output As #1

SelectBeginning

For i = 2 To ActiveProject.TaskTables(1).TableFields.Count
header = ActiveCell.FieldName
Write #1, header;
SelectCellRight
Next i

Close #1

End Sub

 

[Jan De Messemaeker]

Hi,

This will only work when the active table is TaskTables(1).
activeproject.tasktables(activeproject.currenttable)
Always addresses the active table and is what you need
HTH

 

[dew]

Thanks so much!! I've got it working.

I have one weird thing happening though that you might know the answer to.
I'm copying and pasting from Project to Excel. With one particular project
it's pasting columns that aren't displayed and not getting the field names
right either. On different projects from several other users, it works just
fine. What might be going on?

dew

 

[Jan De Messemaeker]

Hi Dew,

These must be invisble columns, that are there in the table but with a
width=0.
Theys ar etruly "hidden"
When Project says "hide column" it means delete column from the table.
But zero width columns are still there.
HTH

 

[dew]

Thanks! That must be it.

Hi Dew,

These must be invisble columns, that are there in the table but with a
width=0.
Theys ar etruly "hidden"
When Project says "hide column" it means delete column from the table.
But zero width columns are still there.
HTH
--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
http://users.online.be/prom-ade/index.htm
32-495-300 620

 

[JackD]

If I were you I wouldn't be copying and pasting in VBA from any specific
table..
The easier way is to write some code which goes through and exports the data
you want. It does not need to be visible in the table to do this and it can
be in any order.

Take a look at the export heirarchy to excel macro on my site.
http://masamiki.com/project/macros.htm

You can modify it to export any of the task fields.
This is easier and much more reliable than depending on a certain table
formatting.

 

[dew]

Jan,

Does this syntax only work for Project 2003? It works fine for me and
another who has 2003. But another user with 2000 just tested it for me and
got an error -- runtime error 438 "Object doesn't support this property or
method" -- on the i = 2... line.

My code is:

Sub CopyHeaders()

Dim ColCount As Integer
Dim Path, header As String

Path = "c:\Documents and Settings\All Users\Desktop\headers.txt"
Open Path For Output As #1

SelectBeginning

For i = 2 To
ActiveProject.TaskTables(ActiveProject.CurrentTable).TableFields.Count
header = ActiveCell.FieldName
Write #1, header;
SelectCellRight
Next i

Close #1

End Sub

 

[JackD]

The Table, tables, tablefield and tablefields objects were new in Project
2002.
You are correct it won't work in Project 2000.