MS Project Task转存入数据库的一段宏
最近在忙一些项目管理的上的事情，主要是把资源整合和任务统筹，因此要把项目进度计划里面的任务整合到公司的项目管理系统中。正好用到以前练习时用到的一小段宏，这里跟大家分享一下。
1: 'Read tasks from project files
2: Public Sub readTasks()
3: Dim ts As Tasks
4: Dim t As Task
5: Set ts = ActiveProject.Tasks
6:
7: Dim cn As ADODB.Connection
8: Set cn = New ADODB.Connection
9: With cn
10: .ConnectionString = "Provider=SQLOLEDB.1;Password=XXXXX;Persist Security Info=True;User ID=XXXXX;Initial Catalog=XXXX;Data Source=XXXXXX"
11: .Open
12: End With
13:
14: Dim cmd As ADODB.Command
15: Set cmd = New Command '实例化Command对象
16:
17: cmd.ActiveConnection = cn
18:
19: '======clear old data=================
20: cmd.CommandText = "delete from dbo.ProjectSchedule"
21:
22: cmd.Execute
23:
24:
25: Dim sql As String
26: Dim pt As Task
27:
28: '====================================================
29: For Each t In ts
30: If Not t Is Nothing Then
31:
32: sql = "insert into dbo.ProjectSchedule (prjID, taskID, taskparentID, taskName, taskDuration, taskStart, taskFinish, taskActualStart, taskActualFinish, taskPredecessors, taskCompleted, taskResourceName) values (1, "
33: 'taskID
34: sql = sql + CStr(t.ID) + ", "
35:
36: 'taskparentID
37: If t.OutlineLevel > 1 Then
38: sql = sql + CStr(t.OutlineParent.ID) + ", "
39: Else
40: sql = sql + "0, "
41: End If
42:
43: 'taskName
44: sql = sql + "'" + t.Name + "', "
45:
46: 'taskDuration
47: 'sql = sql + "0, "
48: sql = sql + CStr(t.Duration / 480) + ", "
49:
50: 'taskStart
51: sql = sql + "'" + CStr(t.Start) + "', "
52: 'sql = sql + Format(t.Start, "#yyyy-MM-dd HH:mm") + ", "
53:
54: 'taskFinish
55: sql = sql + "'" + CStr(t.Finish) + "', "
56: 'sql = sql + Format(t.Finish, "#yyyy-MM-dd HH:mm") + ", "
57:
58: 'taskActualStart
59: If CStr(t.ActualStart) = "NA" Then
60: sql = sql + "null, "
61: Else
62: sql = sql + "'" + CStr(t.ActualStart) + "', "
63: End If
64: 'sql = sql + Format(t.ActualStart, "#yyyy-MM-dd HH:mm") + ", "
65:
66: 'taskActualFinish
67: If CStr(t.ActualFinish) = "NA" Then
68: sql = sql + "null, "
69: Else
70: sql = sql + "'" + CStr(t.ActualFinish) + "', "
71: End If
72: 'sql = sql + Format(t.ActualFinish, "#yyyy-MM-dd HH:mm") + ", "
73:
74: 'taskPredecessors
75: sql = sql + "'" + t.Predecessors + "', "
76:
77: 'taskCompleted
78: sql = sql + CStr(t.PercentComplete) + ", "
79:
80: 'taskResourceName
81: sql = sql + "'" + t.ResourceNames + "') "
82:
83: cmd.CommandText = sql
84:
85: 'MsgBox sql
86:
87: cmd.Execute
88: End If
89: Next t
90:
91:
92: Set cmd = Nothing
93: cn.Close
94: Set cn = Nothing
95: End Sub
