locked
Microsoft Project Macro to Save Projects Locally - Runtime Error 1101 RRS feed

  • Question

  • Greetings,

    I am looking to save all Projects from a Project Server 2010 environment to a local drive. I located a Project Macro that was compiled to work with Project Server 2007. I made adjustments for Project Server 2010, including changing the 'FileOpen' to 'FileOpenEx'.

    But when the process gets to the 'FileOpenEx' call, I receive the error "Run-time error '1101'; The argument value is not valid.".

    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
    Dim strDBUser As String
    Dim strDBPswd As String
    
    'Set Initial Values
    Counter = 0 'keep track the count of the projects
    
    strDSN = "PS2010DraftDB"
    
    strDBUser = "DBAdmin"
    
    strDBPswd = "password"
    
    'File path to save the plan
    strpath = "C:\ArchivalPlans\"
    
    Set cn = New ADODB.Connection
    
    cn.Open "DSN=PS2010DraftDB; uid=DBAdmin;pwd=password"
    
    Set rs = New ADODB.Recordset
    
    'Query to get the plans list
    StrSQL = "SELECT PROJ_UID, PROJ_NAME FROM MSP_PROJECTS "
    StrSQL = StrSQL & " Order by PROJ_UID"
    
    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)
    
    FileOpenEx Name:=PlanName, ReadOnly:=True, UserId:=strDBUser, DatabasePassWord:=strDBPswd, 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

    In an attempt to correct this condition, I tried instantiating a Project object first as follows:

    Dim projApp As MSProject.Application
     
    'Excel Set up to access MS Project
     On Error Resume Next
     Set projApp = GetObject(, "MSProject.Application")
     If projApp Is Nothing Then
     Set projApp = New MSProject.Application
     End If
     projApp.Visible = True 

    Then I changed the FileOpenEx call to:

    projApp.FileOpenEx Name:=PlanName, ReadOnly:=True, UserId:=strDBUser, DatabasePassWord:=strDBPswd, FormatID:="MSProject.ODBC", openPool:=pjDoNotOpenPool

    This allowed the macro to continue running past the "Run-time '1101'" error, but then the Project to be saved locally doesn't open and when it gets to the 'FileClose' call, the Project running the macro is closed.

    Does anyone know how to correct this macro to run through and save all Projects to a local hard drive?

    Alternatively, is there another means of saving Projects from a Project Server 2010 environment to a local drive?

    I forgot to mention that the 'PlanName' variable is populating with the correct name of the fist Project from the SQL Query in the code, so the DNS correction referenced appears to be working just fine.

    Any suggestions most appreciated.

    ~Wayne



    • Edited by wwalkerbout Wednesday, November 9, 2016 5:47 PM
    Wednesday, November 9, 2016 5:40 PM

Answers

  • Code to open a Project Server project if Project Pro is already open and connected to Project Server, is

    fileopen "<>\Project name"

    Don't add the DSN

    I would also open read only. Make sure projects get closed and checked in properly (if checked out)


    Rod Gill
    Author of the one and only Project VBA Book and VBA developer.
    www.project-systems.co.nz

    • Proposed as answer by jacobUT Monday, March 6, 2017 2:36 PM
    • Marked as answer by wwalkerbout Monday, March 6, 2017 3:06 PM
    Sunday, March 5, 2017 4:57 AM

All replies

  • Did you find a solution to this? I am having the same issue.
    Friday, March 3, 2017 8:04 PM
  • Code to open a Project Server project if Project Pro is already open and connected to Project Server, is

    fileopen "<>\Project name"

    Don't add the DSN

    I would also open read only. Make sure projects get closed and checked in properly (if checked out)


    Rod Gill
    Author of the one and only Project VBA Book and VBA developer.
    www.project-systems.co.nz

    • Proposed as answer by jacobUT Monday, March 6, 2017 2:36 PM
    • Marked as answer by wwalkerbout Monday, March 6, 2017 3:06 PM
    Sunday, March 5, 2017 4:57 AM
  • Yes this was the issue. This was my first time with a Visio macro....:)
    Monday, March 6, 2017 2:36 PM
  • Hi Rod,

    Thanks for the feedback on this one. I never did get an answer to this issue and finally ended up approaching the process in a completely different way. So it is good to know there was something I was doing wrong at the time and I wasn't just imagining things.

    Eventually, I ended up leveraging PowerShell scripting to handle this process. I could do the same as this macro and leverage an SQL query to filter the Projects, even adjusting the query to skip Projects that were checked out.

    I could even used the Windows Task Scheduler to repetitively run processes with PowerShell. For example, the client I was working for at the time then wanted the Projects that were not checked out to be Published over night on a weekly basis.

    But I must try this change in the macro out, as it is always good to have different options for such processes.

    Thanks again for providing a solution for something that was really troubling me at the time.

    Cheers,

    Wayne

    Monday, March 6, 2017 3:04 PM
  • Wayne,

    I was looking at the Powershell side of things too but didn't venture far as I was able to get the Macro working. However, is it possible to share the powershell code snippet if you dont mind?

    Thanks in advance,
    Jake.

    Monday, March 6, 2017 3:20 PM
  • Hi Jake,

    Was out for the day, so please excuse the delayed response. I can provide you a copy of the PowerShell script that I developed to Publish all Enterprise Projects that have been checked-in, as I documented that one thoroughly (for my client's tech support guys). The one-off Project Save script I didn't document and can't seem to locate the details.

    The only issue is that it is a large script. It includes the process to log all actions, save a log file, attach the log file to an email message, send that he message to the tech support team and then . To help the tech support guys with additional information to understand the script, it is also copiously commented (overly so). This means it's too large to paste into a message on this forum (6000 char limit).

    But, if you email me (at wayneatwalkerboutdotcom), I can send you, via an email attachment, the documented PowerShell script and throw in the Task Scheduler configuration process for good measure. I don't see an option for attaching files to the posts on this forum.

    So, if you are interested, let me know and I'd be happy to send on the details.

    Cheers,

    Wayne

    Tuesday, March 7, 2017 5:07 AM
  • hi Wayne,

    I am looking for a powershell script to save project server 2010 schedule locally as .mpp files.

    as you mentioned in earlier post, your script is quite big, can you post guidelines with core commandlets helping achieving the objective will be helpful to wider audience out here.

    Thanks and looking forward for your reply.

    Tuesday, May 30, 2017 4:37 AM