Start Date from excel to ms project (with VBA)

Question

I'm trying to transfer the data of a project from MS Excel to MS Project with VBA.

The problem is that, as I'm doing it now, the starting hour of each task is set at 12:00 AM, which gives me problems on the evaluation of the EndDate (assuming that I operate with StartDate and Duration of each activity).

Dim StartDate As Date

For j = 1 To 10000
    StartDate = wbAs.Worksheets(i).Cells(j, 2).Value
    prProject.Tasks(i).Start = StartDate
    i = i + 1

I did not copy the whole code since it is a bit long, I hope it is understandable. Basically, I cycle on each row of an Excel worksheet and I transfer in MS Project the StartDate of each activity, present in the 2nd column.

Doing like that, I'm not able to pass the StartHour, while I'd like to tell MS Project that all the activities are starting at 09:00 AM. Is that possible?

Thanks

Answer 1

Why not just set the hour to 9:00am before setting the project start? This should help: How to set only the time part of a datetime

Dim StartDate As Date

StartDate = wbAs.Worksheets(i).Cells(j, 2).Value
Dim ProjDate As DateTime = New DateTime(StartDate.Year, StartDate.Month, StartDate.Day,9,0,0,0)
prProject.Tasks(i).Start = ProjDate

Answer 2

Quick Answer

Add the missing hour portion to a date-only value to get the correct start date and time in Project:

prProject.Tasks(i).Start = StartDate + prProject.DefaultStartTime

Long Answer

Dates always have a time component, but if not set explicitly it is 12:00 AM. Dates are actually numbers with the integer part representing the day and the fraction part representing the time. In VBA, today's date is 43208 (e.g. April 18, 2018 is the 43,208th day if you start at 1/1/1900).

The value you wanted to enter was the date from Excel + 9:00 AM which is 9 ÷ 24.

While you can add 9 ÷ 24, what you should do is to use the default start time set up in your schedule. Using that value will save you from having to update your code if the default start time changes (or differs in another schedule that you might run this macro on).