|
|
| |
|
| Save Files to local machine (folder) |
Scenario: If you want to save multiple project plans from project server to your local machine. To do this, you have to open each project plan and need to save individually to local machine.
Purpose: This macro automates the process of open and saves. You can specify the criteria, which plans need to open during this process. You have to define the SQL query to specify the list of the plans. Also you need to have system DSN on the machine where this macro is running. System DSN should establish the connection to Project Server database.
Steps:
- Create the system DSN from Control Panel and establish the connection to Project Server database. (Read-Only access on project server database is sufficient for this process)
- Please keep the following info ready for next steps:
System DSN Name; database User Name; database Password;
- Create the blank MS Project Plan (schedules are not required) and give name as “Projects Archival.mpp”
- On MS Project Plan, Tools -> Macro -> Visual basic editor
- Paste the following VBA code and set the following variables as per your environment
Please set the following variables in VBA Code, prior to running the macro.
strDSN = "sysdsn"
strpath = "C:\ArchivalPlans\"
cn.Open "DSN=sysdsn; uid=dbuser;pwd=password"
FileOpen Name:=PlanName, ReadOnly:=True, UserId:="dbuser", DatabasePassWord:="password", FormatID:="MSProject.ODBC", OpenPool:=pjDoNotOpenPool
Where strDSN is the system DSN Name; uid and UserID is the database user name and pwd/databasePassword are the database password.
Please use the same username and password combination for system dsn and connection info on VBA code.
*****************************************************************************
Sub PlanArchival()
Dim strPlan As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim StrSQL As String
Dim ProjectsA(600) As String
Dim index As Integer
Dim Counter As Integer
Dim PlanName As String
Dim strpath As String
Dim strfilepath As String
Dim ArchvDate As Date
Dim ArchvMonth As Integer
Dim ArchvDay As Integer
Dim ArchvYear As Integer
Dim ArchDayStamp As String
Dim strDSN As String
'Set Initial Values
Counter = 0 'keep track the count of the projects
strDSN = "sysdsn"
'file path to save the plan
strpath = "C:\ArchivalPlans\"
Set cn = New ADODB.Connection
cn.Open "DSN=sysdsn; uid=dbuser;pwd=password"
Set rs = New ADODB.Recordset
'Query to get the plans list
StrSQL = "SELECT PROJ_ID, PROJ_NAME FROM MSP_PROJECTS "
StrSQL = StrSQL & " Order by PROJ_ID"
rs.Open StrSQL, cn, 3, 3
Counter = Counter + 1
'get the list of project names into array, this array will be used to open
'the projects
While Not rs.EOF
'Append System DSN Name to the Project Plan.
ProjectsA(Counter) = strDSN"\" & rs(1)
Counter = Counter + 1
rs.MoveNext
Wend
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
'------------------------------------------------------
''Comment: Opening each project and save the plan
'-----------------------------------------------------------
'For index = 1 To Counter
For index = 1 To 1
strPlan = ProjectsA(index)
If index >= Counter Then
Exit Sub
End If
PlanName = ProjectsA(index)
FileOpen Name:=PlanName, ReadOnly:=True, UserId:="dbuser",
DatabasePassWord:="password", FormatID:="MSProject.ODBC", OpenPool:=pjDoNotOpenPool
ArchvDate = Date
ArchvDate = FormatDateTime(ArchvDate, vbShortDate)
ArchvMonth = DatePart("m", ArchvDate)
ArchvDay = DatePart("d", ArchvDate)
ArchvYear = DatePart("yyyy", ArchvDate)
'Getting archival day in m_dd_yyyy format
ArchDayStamp = "_" & ArchvMonth & "_" & ArchvDay & "_" & ArchvYear
'Replacing the .Published with archival day
strPlan = Replace(strPlan, ".Published", ArchDayStamp)
strfilepath = strpath & strPlan
FileSaveAs Name:=strfilepath, FormatID:="MSProject.MPP"
FileClose (pjDoNotSave)
Next
End Sub
*********************************************************
| |
|
