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		

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

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		

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

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,

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

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