Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and MS Project
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and MS Project
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and MS Project
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Project - VBA Project window - Excel 2003 | Excel Programming | |||
macro is not saved in project but in a project.txt version | Excel Programming | |||
How to convert MS Project to MS Excel. I don't have MS Project. | Excel Discussion (Misc queries) | |||
With VBA from Excel: Open Project, extract resource list and copy it to a worksheet, close project. | Excel Programming | |||
Assigning the Help 4, *.HLP file for a project programmatically in a protected Project | Excel Programming |