project 2003 database connection from excel

[dbonline]

I am trying to extract data from the project database via a macro from excel.
The problem I am having is the table and field names that are in the
pjdb.htm file do not work. I have found that instead of MSP_TASKS I have to
just type in Tasks for the table name. Instead of TASK_UID I have to type in
TaskUniqueID for the field name. Where can I find a list of the field and
table names that are required to extract data from project through VBA?

 

[Rod Gill]

Projdb is the only source of data for projects saved to a database. Note
that Project 2007 no longer supports saving to a database.

However, Tasks and TaskUniqueID are used when reading from .mpp files using
OLEDB. This method also is no longer supported in Project 2007.

--

Rod Gill
Microsoft MVP for Project

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

 

[JulieS]

Just a quick point of clarification. Are you trying to extract data
directly from the project file or have you saved the file as a
database? As Rod said, the pjdb.htm file is for project data stored
in an project database file. For information on extracting data
from the project file directly, fellow MVP Jack Dahlgren has written
some information you might find useful
http://zo-d.com/blog/archives/programming.html

Rod's book http://www.projectvbabook.com is also excellent and he
covers the project database as well as working with timephased data.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

 

[dbonline]

Thanks for the reply.
I am trying to extract from the project file itself. I did not save to a
database file. I thought it would be faster to pull the data directly from
the tables vs. opening the file and extracting the data that way. Am I
correct in assuming that?

Dean

 

[Jack Dahlgren]

db,

The project file (.mpp) does not have "tables" in it. It is stored in a
binary format and the provider converts this into a dataset. The database is
only an option if you have stored as an .mpd or .mdb file.

As you have found, the methods for accessing the data are slightly different
for database vs. file. I don't know which is faster. I suppose it depends on
what you are trying to do. If you are just reading data, probably it is
faster without opening the file. If you are doing something more complex and
manipulating the tasks, you will have to open the file.

-Jack Dahlgren

 

[dbonline]

Thanks for the reply.

So where can I find a list of these field names?

Dean

 

[JulieS]

Hello dbonline,

Insert > Column will show you all of the fields available in
Project. You can insert Task fields into a Task view or Resource
fields into a Resource view and then hover your mouse over the
inserted column heading to get quick help on the definition of the
data each fields holds as well as additional helpful information
about how the field is calculated.

I also suggest visiting the Object Browser in the VB editor.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

 

[dbonline]

Thanks for the reply. I'm not sure if I am conveying what I am trying to do
very well, so here is some of the code I am running.

strConn = "Provider=" & "Mircosoft.Project" & ".OLEDB.11.0;" & "Project Name
= " & IsFileName.Value
set rs = New ADODB.Recordset
Set CnnProject = New.ADODB.Connection
CnnProject .Open StrConn & ";"

StrSelect = "Select TaskUniqueID, TaskID, TaskWBS, TaskPercentComplete,
TaskStart, TaskFinish, TaskDuration From Tasks Order by TaskID"
rs.Open StrSelect, CnnProject

I found bits of this code on the internet but not sure where they got the
field names for the select statement. These field names in the select
statement do not match the column header names in project. So where do I
find a listing of these field names so I can further populate my select
statement? I hope by giving this code sample there is a better understanding
of what I am asking for.
Thanks again for all your support.

Dean

 

[Rod Gill]

As I suggested earlier, you are using oledb to read directly from .mpp
files. This method is read only and no longer supported by Project 2007. You
can find a list of all tables and fields in the pjoledb.htm file stored in
the same folder as pjdb.htm! pjdb.htm is for project saved to a database.

--

Rod Gill
Microsoft MVP for Project

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

 

[dbonline]

Rod, Thanks for the reply. I appreciate the help.

 

[K P Sendil Kumar]

Please visit

http://msdn.microsoft.com/en-us/library/aa140134(office.10).aspx

for a complete list of fields.

Regards,
Sendil