Enumerate Fields?

[EricG]

Is there an easy way in VBA to enumerate all the available fields (names,
aliases) in MS Project (i.e. "Number1", "Text2", "Start", etc) and their
types (long, boolean, etc)?

Thanks in advance for you help,

Eric

 

[Rod Gill]

HI,

No, but you've already listed most. Number fields are double, date fields
are date, text fields are string and a list of all custom field types (EG
Date, Text etc can be seen under Tools, Customize, Fields. So why do you
need code? What are you trying to achieve?

--

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

Is there an easy way in VBA to enumerate all the available fields (names,
aliases) in MS Project (i.e. "Number1", "Text2", "Start", etc) and their
types (long, boolean, etc)?

Thanks in advance for you help,

Eric


__________ Information from ESET Smart Security, version of virus
signature database 4869 (20100215) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4869 (20100215) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[EricG]

Nothing terribly important. I've been asked to generate an interface between
Excel (which I'm very good at) and MS Project (which I'm not at all good at)
that allows users to create custom queries of data from projects. Hard to
explain more than that without going into gory details.

What I'm trying to do specifically is have a drop-down list in Excel for
picking fields from a project. I want to be able to filter that list of
fields to either "All Fields" or the subset of fields that actually have data
in them.

Is there a table in an MS Project project that I can query that has the
"used fields" in it?

Thanks again,

Eric

 

[Jack Dahlgren MVP]

No there is no list of used fields.
You have to use some VBA to check them.
I have a sample which is a good starting point here:

http://masamiki.com/project/checkfields2.txt

-Jack

 

[Rod Gill]

Hi,

Jack's code is one way, but I'm a bit of a minimalist! Set a standard that
all custom fields must be renamed (Tools, Customize, Fields) then the
following code lists all renamed fields:

Dim FieldsUsed As String

Sub FieldsRenamed()
FieldsUsed = ""
ListNames "Text"
ListNames "Number"
ListNames "Cost"
ListNames "Date"
ListNames "Start"
ListNames "Finish"
ListNames "Duration"
Debug.Print FieldsUsed
End Sub

Function ListNames(Field As String)
Dim Count As Integer
Dim ThisField As String
On Error Resume Next
FieldsUsed = FieldsUsed & Field & " fields:" & vbCrLf
Err.Clear
For Count = 1 To 30
ThisField = CustomFieldGetName(FieldNameToFieldConstant(Field &
Count))
If ThisField <> "" Then
FieldsUsed = FieldsUsed & Field & Count & vbCrLf
End If
Next Count
FieldsUsed = FieldsUsed & vbCrLf
End Function


--

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

No there is no list of used fields.
You have to use some VBA to check them.
I have a sample which is a good starting point here:

http://masamiki.com/project/checkfields2.txt

-Jack



__________ Information from ESET Smart Security, version of virus
signature database 4869 (20100215) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4869 (20100215) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Jack Dahlgren MVP]

Rod,

If you can rely on a standard then so can I.

Set a standard that all used fields must be entered into a list.
Open the list.

-Jack

 

[EricG]

Gents,

I appreciate both of your inputs. It seems odd that I can't query a project
for its fields, much like I can query an Access database table for its
fields. I feel a painful bout of "manual labor" coming on.

Regards,

Eric

 

[EricG]

I found a nice list of fields in each Project table, along with some SQL
help, here:

http://www.oraxcel.com/projects/sqlxl/help/techniques/query/msproject.html

 

[Rod Gill]

That uses oledb code, which unfortunately is no longer supported in Project
2007 onwards.

--

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 found a nice list of fields in each Project table, along with some SQL
help, here:

http://www.oraxcel.com/projects/sqlxl/help/techniques/query/msproject.html




__________ Information from ESET Smart Security, version of virus
signature database 4872 (20100216) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4872 (20100216) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[EricG]

....and since we're switching to 2007 very soon at work, I should figure out
the "new" way of doing things. Can you tell me what database model MS
Project 2007 supports? Can I use ADODB in a similar way? I actually have a
pretty good solution working right now in 2003, but if I need to change it,
I'd rather do it now before I add too many features.

Thanks,

Eric

 

[Rod Gill]

Project 2007 does not support save to database any more either. So if you
want data in a database, you need to write VBA code, or an add-in to get it
there.

--

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

...and since we're switching to 2007 very soon at work, I should figure
out
the "new" way of doing things. Can you tell me what database model MS
Project 2007 supports? Can I use ADODB in a similar way? I actually have
a
pretty good solution working right now in 2003, but if I need to change
it,
I'd rather do it now before I add too many features.

Thanks,

Eric





__________ Information from ESET Smart Security, version of virus
signature database 4872 (20100216) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4872 (20100216) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[John Gregory]

Eric,

I recently had a need to do exactly what you require --- list all available built-in and custom fields in Project 2007, showing which are used. This is similar to what you see when you attempt to insert a column into an MS Project table. Unfortunately, the only way I was able to accomplish it was by using the full range of "constants" that MS Project uses. I looked them up and subtracted from the ranges all Enterprise fields (which are a legacy from Project 2003 Server).

I believe the following code will give you just what you're looking for:

ListFields(188743680, 188744278)
ListFields(188744799, 188744808)
ListFields(188744819, 188744956)

Private Sub ListFields(ByVal x As Long, ByVal y As Long)

For i = x To y
If Not IsNull(Application.FieldConstantToFieldName(i)) Then
If Application.FieldConstantToFieldName(i) <> "" Then
If Application.CustomFieldGetName(i) <> "" Then
LocalCustomFields.AddItem (Application.FieldConstantToFieldName(i) & " ( " & Application.CustomFieldGetName(i) & " )")
Else
LocalCustomFields.AddItem (Application.FieldConstantToFieldName(i))
End If

End If
End If
Next

End Sub


LocalCustomFields is your menu control (or some other element of your form such as a listbox). Also, I've adapted this from VB.NET, where I was using sorted lists to automatically put the results in order, so you're going to have to alphabetize what is returned. I think this should get you close, though.




EricG wrote:

Enumerate Fields?
15-Feb-10

Is there an easy way in VBA to enumerate all the available fields (names,
aliases) in MS Project (i.e. "Number1", "Text2", "Start", etc) and their
types (long, boolean, etc)?

Thanks in advance for you help,

Eric

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
SharePoint List Usage and Statistics
http://www.eggheadcafe.com/tutorial...d-9723a79fdb14/sharepoint-list-usage-and.aspx

 

[Jack Dahlgren MVP]

Or use Project Server...

-Jack Dahlgren

Project 2007 does not support save to database any more either. So if you
want data in a database, you need to write VBA code, or an add-in to get
it there.

--

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






__________ Information from ESET Smart Security, version of virus
signature database 4872 (20100216) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[EricG]

John,

Works great! This was just what I needed. I had to do a little translation
to get it to work inside my Excel VBA, but that was trivial. The only thing
I discovered is that the third call to ListFields errors out when using
Office 2003. It quits when i = 188744823 with the (not very helpful) error
message "An unexpected error occurred with the method" at the line:

If pjAPP.CustomFieldGetName(i) <> "" Then

Must be new fields added after Office 2003? In any case, your help is
greatly appreciated!

Regards,

Eric

 

[Dean C]

If what you are really trying to do is look at the database structure, then
step into the code below, stop at next mytask, open the locals window and
expand mytask. You can see all task fields available to visual basic (not all
are visible using this method).

sub ShowProjectStructure
for each mytask in activeproject.tasks
next mytask
End sub