How to extraxt text from microsoft project files?

[karen]

Hello all:

I am wring VB to extract text from a project file.
Right now I got the text by hard coding each column in task, resource and resource address book.
I am wondering if there is a better way, like check all available columns and use a loop to traverse all column and extract the text in each column.

Any suggestion is appreciated.

Thanks

karen

 

[JackD]

There are many ways to export it. What format do you want it in and which
fields specifically do you want to export?

Here is some sample code which exports information about all the tasks into
a text file. It can easily be customized to write just about any project
information. Let me know if you need any help with it.


Sub NoteFile()
Dim mystring As String
Dim MyFile As String
Dim fnum As Integer
Dim myTask As Task

'set location and name of file to be written
'this writes to a text file at the root directory of the c: drive
MyFile = "c:\" & ActiveProject.Name & "_Project_Notes" & ".txt"

'set and open file for output
fnum = FreeFile()
Open MyFile For Output As fnum


'Build string with project info
mystring = ActiveProject.Name & " " & ActiveProject.LastSaveDate & " " &
Application.UserName

'write project info and then a blank line
Write #fnum, mystring
Write #fnum,

'step through tasks and write notes for each then a blank line
For Each myTask In ActiveProject.Tasks
If myTask.Notes <> "" Then
'edit the following line to include the fields you want, use " " to
include any text or spaces.
mystring = myTask.UniqueID & ": " & myTask.Name & ": " &
myTask.Notes
'Some other Examples: MyString = MyTask.Text1 & ": " & MyTask.Start
Write #fnum, mystring
Write #fnum,
End If
Next myTask
Close #fnum
End Sub


-Jack

Hello all:

I am wring VB to extract text from a project file.
Right now I got the text by hard coding each column in task, resource and resource address book.
I am wondering if there is a better way, like check all available columns

and use a loop to traverse all column and extract the text in each column.

 

[karen]

Hello Jack:

Thank you very much. I appreciate your advices. while the problem is that I don't know what columns are in the file. I got the file and run it in VB that gives me a .txt file with all information the project file has. It is the VB program who detects the project files and gets the column numbers and extract the text from those columns. Any idea on that?

Thanks

Karen

 

[Rod Gill]

Hi,

Another way is to use the oledb provider to extract data directly from .mpp
files. Project still needs to be installed, but you can loop through all
fields in a recordset.

See the prjoledb.htm file installed in one of project's program folders for
full details.

--
For VBA posts, please use the public.project.developer group.
For any version of Project use public.project
For any version of Project Server use public. project.server

Rod Gill
Project MVP
For Microsoft Project companion projects, best practices and Project VBA
development services
visit www.projectlearning.com/

Hello all:

I am wring VB to extract text from a project file.
Right now I got the text by hard coding each column in task, resource and resource address book.
I am wondering if there is a better way, like check all available columns

and use a loop to traverse all column and extract the text in each column.

 

[karen]

Thanks Rod. I am trying to get all fields in this way:
"
Dim lst as MSProject.List //or as variant. none of them worked
Dim selct as MSProject.Selection

set selct = ActiveSelection //it rturns 3

For each lst in selct.FieldNameList //error need object
Debug.print lst
Next lst

I don't know why it gives me error. I test the selct, it is a selection type and FieldNameList is a property of Selection and it returns a list object. I just don't understand why the error is there. Felt very frustrated.: (

Since I am new to VB and microsoft project, maybe I didn't use it correctly.
Any help is appreciated

Thanks

Karen

 

[JackD]

To extract data from a column (actually the column is just where a FIELD is
displayed so I'm going to call it a field from here) you need to tell your
code which field to use. So if you want to get the name of a task you would
get the value of task.name. For the start date of the task you would get the
value of task.start.

The data from the fields can be extracted through vba whether the column is
displayed or not.

If you want a really simple way to do this, just go to file menu, select
save as and for the file type select text file.
It will walk you through the process of creating a map where you choose
which fields you want to export.
Every project file has the same set of fields. They may look different
because the columns which display the fields are different, but the fields
are the same for all.

-Jack

Hello Jack:

Thank you very much. I appreciate your advices. while the problem is

that I don't know what columns are in the file. I got the file and run it
in VB that gives me a .txt file with all information the project file has.
It is the VB program who detects the project files and gets the column
numbers and extract the text from those columns. Any idea on that?

 

[karen]

There are over 200 fileds for either tasks or resources, and users can even insert a new column with a name they generated. The VB is like a tool that automatically get the text without people's action in it. I can't manually save it as a .txt file. And I tried the 'filesaveas' function which saves the file into a txt file but it calls the wizard. Since there is no people monitoring the process, the saveas couldn't complete. Is there a way to turn it off? Or any other ways to get the text.

Thanks

Karen

 

[JackD]

I guess the question is what do you need this for?
Most of the fields are standard. It doesn't matter what the user may have
renamed them to.
What will you do with this saved information?

-Jack

There are over 200 fileds for either tasks or resources, and users can

even insert a new column with a name they generated. The VB is like a tool
that automatically get the text without people's action in it. I can't
manually save it as a .txt file. And I tried the 'filesaveas' function which
saves the file into a txt file but it calls the wizard. Since there is no
people monitoring the process, the saveas couldn't complete. Is there a way
to turn it off? Or any other ways to get the text.

 

[Rod Gill]

Hi,

That isn't going to work. Lists belong in user forms and you aren't using
one. Read the prjoledb.htm file to get sample code on how to read from
a.mpp file. You can do this from Project or any Office application such as
Excel. In Excel you could then put the field names into a column.

--
For VBA posts, please use the public.project.developer group.
For any version of Project use public.project
For any version of Project Server use public. project.server

Rod Gill
Project MVP
For Microsoft Project companion projects, best practices and Project VBA
development services
visit www.projectlearning.com/

Thanks Rod. I am trying to get all fields in this way:
"
Dim lst as MSProject.List //or as variant. none of them worked
Dim selct as MSProject.Selection

set selct = ActiveSelection //it rturns 3

For each lst in selct.FieldNameList //error need object
Debug.print lst
Next lst

I don't know why it gives me error. I test the selct, it is a selection

type and FieldNameList is a property of Selection and it returns a list
object. I just don't understand why the error is there. Felt very
frustrated.: (

 

[karen]

Thanks Jack. It works now. I used the OLE DB to get all the data.
I appreciated your help!

Karen

 

[karen]

Thanks Rod. It works! I appreciated your help!

Karen