none
Reading Task Update Data from Excel and populating activities in MSP using VBA RRS feed

  • Question

  • Hi All

    I am trying to read task updates from Excel using a copy of some VBA code kindly posted on the web but it does not write anything back to the project plan even though it is picking up the data in Excel ok.  A copy of the code is shown below.  Any help/guidance appreciated.

    Tony

    Sub Update_MSP()
    
    Dim xlstartdate, xlfinishdate, puid As String
    Dim xlpercentcomplete As Integer
    Dim xlduration As Integer
    Dim prstartdate, prfinishdate As Date
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    
    Dim Proj As Project
    Dim t As Task
    Dim Asgn As Assignment
    Dim i, j, c1 As Integer
    
    '************************************
    'NEED TO SKIP SUMMARY TASKS
    'ADD CODE TO SKIP THESE
    '************************************
    
        'open the source workbook, read only
        Set xlBook = Workbooks.Open("D:\CPP Build Template\CPP Build Plans\MPS Plans WIP\Update from Excel Test\Book1.xlsx", True, True)
        c1 = 3
         Do While Sheets("Sheet1").Cells(c1, 1) <> ""
            'read data from excel file starting from row 2
            'and format start date to start at 8 AM and finish date to finish at 5PM
            puid = Sheets("Sheet1").Cells(c1, 3)               ' Unique id
            xlstartdate = (Sheets("Sheet1").Cells(c1, 5))          ' Excel file Start date in dd/mm/yyyy format
            xlfinishdate = CDate(Sheets("Sheet1").Cells(c1, 7))         ' Excel file Finish date in dd/mm/yyyy format
            xlpercentcomplete = CDate(Sheets("Sheet1").Cells(c1, 11))   ' Excel percentcomplete
            xlduration = CDate(Sheets("Sheet1").Cells(c1, 9))           ' Excel file Finish date in dd/mm/yyyy format
            c1 = 3
            For Each t In ActiveProject.Tasks
            If t.Summary = False Then
               ' find the excel file unique id in the Project file based on uniqueid and update
               ' actual start, actual finish, perecent complete and duration
               If t.UniqueID = puid Then
               t.ActualStart = xlstartdate
               t.ActualFinish = xlfinishdate
               t.PercentComplete = xlpercentcomplete
               t.Duration = xlduration
               'need to exit the code once found to the next line in the excel sheet
               Exit For
               End If
            End If
            Next t
             c1 = c1 + 1
         Loop
     End Sub
    


    TKHussar

    Tuesday, September 8, 2015 12:42 PM

Answers

  • Hi John

    The "D" drive is a local drive on my laptop.  I have not yet tried it on my C Drive.

    Tony


    TKHussar

    Update:

    I have also tried it on my C drive and I get the same message. It only happens every other time I run the routine.  This seems to indicate that I am not closing "something" off at the end of the routine each time I run it.

    Tony

    Further Update:

    I have removed the killExcell routine call and replaced it with "Workbooks("BOOK1.XLSx").Close SaveChanges:=False" which appears to resolve the issue :-).  My next step is now to modify the routine to pick up all excel workbooks in the folder and read updates from all of them.  If I struggle then as Arnie would say.... "I'll be back.............".

    Thanks again for your help John.




    • Edited by TKHussar Friday, September 11, 2015 10:40 AM
    • Marked as answer by TKHussar Friday, September 11, 2015 10:40 AM
    Thursday, September 10, 2015 5:42 PM

All replies

  • Tony,

    There are some issues with the code but after a little cleanup and running on a couple of test files, it seems to work okay for me.

    The issues are

    1. Multiple declarations must by individually declared. For example the first declaration should be

    Dim xlstartdate as string, xlfinishdate as string, puid as string

    2. puid should not be a string, it should be an integer to match Project's data type

    3. The comment says data is being read starting at row 2 but "c1", which is the row variable, starts at  row 3 on the worksheet

    4. The comments indicates the start and finish dates are normalized to 8:00 am and 5:00 pm but there is nothing in the code that does that

    5. the xlfinish, xlpercentcomplete, and xlduration are all converted to a date via CDate. That makes no sense at all!

    5. Even though c1 is incremented at the end of the loop, it is reset to 3 in the middle of the loop. Why?

    So, several issues with the code itself. Clean it up and it should work fine. Note: I assume you are running this macro is Project and that you have set a reference to the Excel object library.

    I strongly recommend you not import percent complete or duration. Once Project has an actual finish date, it automatically updates percent complete to 100%, and rightly so. Users have a tendency to want to "force" Project by importing to much data. Project should only be given a start date and a duration or a start date and a finish date. Project calculates the missing variable.

    John

    Tuesday, September 8, 2015 6:06 PM
  • Hi John

    Thanks for your prompt response.  I have tidied up the code based on your suggestions however the following variables are not updated and as such does not update the plan. 

    I did manage to get the variables to update if I take out the check for a Summary Task but again it does not update the plan.

    t.ActualStart

    t.ActualFinish

    t.PercentComplete

    t.PercentComplete

    even though the linked variables contain the correct data.

    Your guidance would be appreciated.

               t.ActualStart = xlstartdate
               t.ActualFinish = xlfinishdate
               t.PercentComplete = xlpercentcomplete
               t.Duration = xlduration


    TKHussar


    • Edited by TKHussar Tuesday, September 8, 2015 7:05 PM
    Tuesday, September 8, 2015 6:50 PM
  • Tony,

    I don't understand what you mean by " the variables update but the plan does not".

    This is my re-write of the code. Note my Excel test file is on my desktop so the path is different. I also deleted variables that are not used and I commented out those parameters that should not be imported (i.e. percent complete and duration). If the xlfinishdate is null then importing the percent complete does make sense but if there is an actual finish date then do NOT import percent complete.

    Sub Update_MSP()

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim xlstartdate As Date, xlfinishdate As Date
    Dim prstartdate As Date, prfinishdate As Date
    Dim puid As Integer
    Dim xlpercentcomplete As Integer, xlduration As Integer

    Dim t As Task
    Dim c1 As Integer

    'open the source workbook, read only
    Set xlBook = Workbooks.Open("C:\Users\John\Desktop\TonyTest.xlsx", True, True)
    c1 = 3
    Do While Sheets("Sheet1").Cells(c1, 1) <> ""
        'read data from excel file starting from row 3
        puid = CInt(Sheets("Sheet1").Cells(c1, 3))                  ' Unique id
        xlstartdate = CDate(Sheets("Sheet1").Cells(c1, 5))          ' Excel file Start date in dd/mm/yyyy format
        xlfinishdate = CDate(Sheets("Sheet1").Cells(c1, 7))         ' Excel file Finish date in dd/mm/yyyy format
        'xlpercentcomplete = CInt(Sheets("Sheet1").Cells(c1, 11))   ' Excel percentcomplete
        'xlduration = CInt(Sheets("Sheet1").Cells(c1, 9))           ' Excel duration
        For Each t In ActiveProject.Tasks
            If t.Summary = False Then
               ' find the excel file unique id in the Project file based on uniqueid and update
               ' actual start & actual finish
                If t.UniqueID = puid Then
                    t.ActualStart = DateAdd("h", 8, xlstartdate)    '"normalize" date for 8:00 AM start
                    t.ActualFinish = DateAdd("h", 17, xlfinishdate) '"normalize" date for 5:00 PM finish
                    't.PercentComplete = xlpercentcomplete
                    't.Duration = xlduration
                    'need to exit the code once found to the next line in the excel sheet
                    Exit For
                End If
            End If
        Next t
        c1 = c1 + 1
    Loop
     End Sub

    John

    Tuesday, September 8, 2015 8:49 PM
  • Hi John

    The problem I was having, for example, was that the data from Excel was being picked up in the variable xlstartdate and was being transferred to the variable t.actualstart but it was not being written to MSP.  Your code works perfectly however the only outstanding issue I have is that Excel is left open in the background.  The code I am using to try and kill that process is shown below and works in another routine I use so not sure why it is not working here!!!!!

    Set xlApp = Nothing
    Call KillExcel
    End Sub
    Sub KillExcel()
    Dim sKill As String
    
    sKill = "TASKKILL /F /IM msexcel.exe"
    Shell sKill, vbHide

    Again many thanks for your help/guidance.

    Kind regards

    Tony


    TKHussar


    • Edited by TKHussar Wednesday, September 9, 2015 5:46 AM
    Wednesday, September 9, 2015 5:33 AM
  • Tony,

    If Excel is left open in the background, it is because it was already opened before running the macro. Nothing in the code opens Excel. Accessing the Excel file does NOT open Excel.

    If my input has helped please consider marking as answered or at least giving a vote as helpful.

    John

    Wednesday, September 9, 2015 3:12 PM
  • Hi John

    I check there is no Excel.exe process running in the background, then I run the routine which now works perfectly (many thanks).  However when I then check again if there is an Excel.exe process running in the background there is one running following the execution of the routine.  I accept that the routing does NOT open Excel, it simply access the workbook and reads data from it. However this stops me from being able to open the Excel workbook I am using to read the updates the MSP plan unless I actually manually kill the process.

    When I open a new instance of Excel and try and open the source workbook it advises that the file is locked for editing.

    Any advice/help appreciated.

    Tony

    Update:

    I have slightly modified the kill routine and it appears to have resolved the issue.  Revised code:

    Set xlApp = Nothing
    Call KillExcel
    End Sub
    Sub KillExcel()
    Dim sKill As String
    
    sKill = "TASKKILL /F /IM excel.exe"
    Shell sKill, vbHide
    End Sub

    One final issue with the routine (I hope).  I am getting a "Runtime error 462" every other time I run the code. Also "The remote server machine does not exist or is unavailable".

    How do I fix this issue?

    Thanks

    Tony




    • Marked as answer by TKHussar Thursday, September 10, 2015 8:56 AM
    • Unmarked as answer by TKHussar Thursday, September 10, 2015 11:00 AM
    • Edited by TKHussar Thursday, September 10, 2015 11:03 AM
    Thursday, September 10, 2015 7:24 AM
  • Tony,

    Yeah, you right in that the workbook is never closed after opening and that would cause the lock out issue. It looks like you found a solution although a simple close should also work. For your reference, when I run the code on my test files, I am able to open the Excel file after the macro is finished without any further need to close Excel (which isn't open) or the Excel file itself.

    With regard to your error 462, that must be related to your use of the "D" directory which I assume is on some kind of server. Do you experience the same error if your Excel file is on your local drive (i.e. C: directory)?

    John

    Thursday, September 10, 2015 4:50 PM
  • Hi John

    The "D" drive is a local drive on my laptop.  I have not yet tried it on my C Drive.

    Tony


    TKHussar

    Update:

    I have also tried it on my C drive and I get the same message. It only happens every other time I run the routine.  This seems to indicate that I am not closing "something" off at the end of the routine each time I run it.

    Tony

    Further Update:

    I have removed the killExcell routine call and replaced it with "Workbooks("BOOK1.XLSx").Close SaveChanges:=False" which appears to resolve the issue :-).  My next step is now to modify the routine to pick up all excel workbooks in the folder and read updates from all of them.  If I struggle then as Arnie would say.... "I'll be back.............".

    Thanks again for your help John.




    • Edited by TKHussar Friday, September 11, 2015 10:40 AM
    • Marked as answer by TKHussar Friday, September 11, 2015 10:40 AM
    Thursday, September 10, 2015 5:42 PM