Hi everyone
Lots of great info on here and help,
My problem is I have a Ms Project File which gets updated
New Lines inserted and deleted in the project file
I need certin information to export to excel and then further work happens
in excel from there (which is ok)
The problem I have is if there is more data added in the project file, it
does not automatically come through into the excel file
I don't want to do an export to new file every time if I can help it.
I have a paste link from project to excel in excel8.0 link type if that make
sense

Does anyone have any code or ideas of what I could use to keep the 2 files
in sync.
I can add some extra columns in project like UniqueID and an Export Date,
but that doesn't help me with the new lines at the moment

Any Idea would be very much appreciated, I can put up the code I have so far
and the files for amy one to look at

Thanks for your help
Phil Keoghan

this is the type of lik I get into excel when I do a paste special from
project to excel as a link Excel 8.0
=MSProject.Project.9|'C:\Users\Phil\Desktop\Projec t\2010
Project.mpp'!'!LINK_3'

{=MSProject.Project.9|'C:\Users\Phil\Desktop\Proje ct\2010Project.mpp'!'!LINK_3'}
Sorry thats how it comes out with the brackets

Here's some sample VBA for how you can query an MS Project file from Excel
rather than having a link. The sample uses ActiveX data objects and queries
the project file directly. You can put this code (or a call to it) into the
Workbook_Open event in the Excel file to have the data update automatically
every time the file is opened. You'll have to decide which fields you want
to query, and then write the data to whatever worksheet you want it on (after
first clearing out the old data).

The sample writes the data to a text file.

HTH,

Eric

Option Explicit

Const MY_FOLDER = "D:\myFolder\"
Const PROJ_FILE_NAME = "My_Project_File.mpp"

Sub ConnectLocally()
Dim conData As New ADODB.Connection
Dim rstAssigns As New ADODB.Recordset
Dim intCount As Integer
Dim strSelect As String
Dim strResults As String

conData.ConnectionString =
"Provider=Microsoft.Project.OLEDB.11.0;PROJECT NAME=" & MY_FOLDER &
PROJ_FILE_NAME

conData.ConnectionTimeout = 30
conData.Open

strSelect = "SELECT TaskStart, TaskFinish, TaskName, TaskBaselineStart,
TaskBaselineFinish, " & _
"TaskCompleteThrough, TaskConfirmed, TaskCreated,
TaskEarlyStart, " & _
"TaskEarlyFinish, TaskLateStart, TaskLateFinish,
TaskResourceInitials, " & _
"TaskResourceNames, TaskSuccessors FROM Tasks WHERE
TaskUniqueID 0 ORDER BY TaskUniqueID "
rstAssigns.Open strSelect, conData

Do While Not rstAssigns.EOF
For intCount = 0 To rstAssigns.Fields.Count - 1
strResults = strResults & "'" & _
rstAssigns.Fields(intCount).Name & "'" & _
Space(40 - Len(rstAssigns.Fields(intCount).Name)) & vbTab & _
CStr(rstAssigns.Fields(intCount).Value) & vbCrLf
Next
strResults = strResults & vbCrLf
rstAssigns.MoveNext
Loop

conData.Close

Open MY_FOLDER & "Results.txt" For Output As #1
Print #1, strResults
Close #1

Shell "Notepad " & MY_FOLDER & "Results.txt", vbMaximizedFocus

End Sub


"Phil" wrote:

Hi everyone
Lots of great info on here and help,
My problem is I have a Ms Project File which gets updated
New Lines inserted and deleted in the project file
I need certin information to export to excel and then further work happens
in excel from there (which is ok)
The problem I have is if there is more data added in the project file, it
does not automatically come through into the excel file
I don't want to do an export to new file every time if I can help it.
I have a paste link from project to excel in excel8.0 link type if that make
sense

Does anyone have any code or ideas of what I could use to keep the 2 files
in sync.
I can add some extra columns in project like UniqueID and an Export Date,
but that doesn't help me with the new lines at the moment

Any Idea would be very much appreciated, I can put up the code I have so far
and the files for amy one to look at

Thanks for your help
Phil Keoghan