Prev: UDF to Calculate YTM
Next: if statement
From: JonM on 6 Apr 2010 14:35
I was wondering if anyone uses project and excel together. I need to extract
the task usage view from ms project. I have tried the project dev group and
can't seem to find exactly what I need. We need this data in excel to do
pricing for proposals.

Thanks.
From: EricG on 7 Apr 2010 09:56
I've only done a little work pulling Project data into Excel. Here's a
snippet of code that might be useful. The routine takes as arguments a
folder path, a Project file name, and a Variant array to return data to the
calling routine. Uses OLEDB to query the Project file so that it doesn't
have to be open to grab data.

HTH,

Eric

Sub Get_Project_Data(My_Folder As String, Proj_File_Name As String,
numReturned As Long, retData() As Variant, _
success As Boolean)
Dim conData As New ADODB.Connection
Dim rstAssigns As New ADODB.Recordset
Dim intCount As Integer
Dim strSelect As String
Dim strResults As String

On Error GoTo MSProj_Pull_Failed

conData.ConnectionString =
"Provider=Microsoft.Project.OLEDB.11.0;PROJECT NAME=" & My_Folder &
Proj_File_Name

conData.ConnectionTimeout = 30
conData.Open
'
' Query for all tasks in the data which have the UniqueID that we are
looking for.
'
strSelect = "SELECT TaskUniqueID, TaskStart, TaskFinish, TaskName,
TaskEarlyStart, " & _
"TaskEarlyFinish, TaskLateStart, TaskLateFinish,
TaskPredecessors, " & _
"TaskSuccessors, TaskMilestone, TaskDuration,
TaskPercentComplete " & _
"FROM Tasks WHERE TaskUniqueID > 0 ORDER BY
TaskUniqueID "
rstAssigns.Open strSelect, conData

retData = rstAssigns.GetRows(5000) ' GetRows pulls as much data as
there is, or up to 5000 records...
numReturned = UBound(retData, 2) + 1 ' retData will be a zero-based
array
'
rstAssigns.Close
conData.Close
success = True
Exit Sub
'
MSProj_Pull_Failed:
success = False
'
End Sub


"JonM" wrote:

> I was wondering if anyone uses project and excel together. I need to extract
> the task usage view from ms project. I have tried the project dev group and
> can't seem to find exactly what I need. We need this data in excel to do
> pricing for proposals.
>
> Thanks.
From: JonM on 7 Apr 2010 11:46
Eric,

Thanks for your reply. I am not a coder or VBA'er, I did copy your example
over and pasted it in the editor. However I cannot figure out how to get it
to work. I am sure some formatting or something got skewed in the copy and
paste.

I was hoping someone knew of a utility or add in that would accomplish this.
As it is right now I am saving as a pdf and using another utility to pull
into excel. Was hoping to simplify this process.

"EricG" wrote:

> I've only done a little work pulling Project data into Excel. Here's a
> snippet of code that might be useful. The routine takes as arguments a
> folder path, a Project file name, and a Variant array to return data to the
> calling routine. Uses OLEDB to query the Project file so that it doesn't
> have to be open to grab data.
>
> HTH,
>
> Eric
>
> Sub Get_Project_Data(My_Folder As String, Proj_File_Name As String,
> numReturned As Long, retData() As Variant, _
> success As Boolean)
> Dim conData As New ADODB.Connection
> Dim rstAssigns As New ADODB.Recordset
> Dim intCount As Integer
> Dim strSelect As String
> Dim strResults As String
>
> On Error GoTo MSProj_Pull_Failed
>
> conData.ConnectionString =
> "Provider=Microsoft.Project.OLEDB.11.0;PROJECT NAME=" & My_Folder &
> Proj_File_Name
>
> conData.ConnectionTimeout = 30
> conData.Open
> '
> ' Query for all tasks in the data which have the UniqueID that we are
> looking for.
> '
> strSelect = "SELECT TaskUniqueID, TaskStart, TaskFinish, TaskName,
> TaskEarlyStart, " & _
> "TaskEarlyFinish, TaskLateStart, TaskLateFinish,
> TaskPredecessors, " & _
> "TaskSuccessors, TaskMilestone, TaskDuration,
> TaskPercentComplete " & _
> "FROM Tasks WHERE TaskUniqueID > 0 ORDER BY
> TaskUniqueID "
> rstAssigns.Open strSelect, conData
>
> retData = rstAssigns.GetRows(5000) ' GetRows pulls as much data as
> there is, or up to 5000 records...
> numReturned = UBound(retData, 2) + 1 ' retData will be a zero-based
> array
> '
> rstAssigns.Close
> conData.Close
> success = True
> Exit Sub
> '
> MSProj_Pull_Failed:
> success = False
> '
> End Sub
>
>
> "JonM" wrote:
>
> > I was wondering if anyone uses project and excel together. I need to extract
> > the task usage view from ms project. I have tried the project dev group and
> > can't seem to find exactly what I need. We need this data in excel to do
> > pricing for proposals.
> >
> > Thanks.
 | 
Pages: 1
Prev: UDF to Calculate YTM
Next: if statement