VBA实现Excel至MS Project开始日期导入：起始时间异常问题求助

It sounds like the root of your problem is that when importing date values from Excel that don’t include a time component, VBA defaults to setting the time to midnight (12:00 AM). This throws off end date calculations because MS Project uses that exact start time to compute when the task finishes based on its duration. Here’s how to resolve this:

1. Add a Default Working Start Time to Date-Only Values #

If your Excel data doesn’t include specific start times for tasks, you can explicitly add a default working time (like 8:00 AM, matching your project’s calendar) to the date before assigning it to the MS Project task. Modify your code snippet like this:

Dim StartDate As Date
' Initialize your task index properly before the loop (e.g., taskIndex = 1)
Dim taskIndex As Integer
taskIndex = 1

For j = 1 To 10000
    StartDate = wbAs.Worksheets(i).Cells(j, 2).Value
    
    ' Check if the time component is midnight (no time entered in Excel)
    If TimeValue(StartDate) = TimeValue("00:00:00") Then
        ' Add your desired default start time (e.g., 8:00 AM)
        StartDate = DateValue(StartDate) + TimeValue("08:00:00")
    End If
    
    prProject.Tasks(taskIndex).Start = StartDate
    taskIndex = taskIndex + 1
Next j

This ensures that any task without a specified start time will begin at your project’s typical working hour instead of midnight, making end date calculations align with real-world scheduling.

2. Verify Excel Cell Data and Formatting #

• If your Excel cells should include time values but aren’t being captured correctly, check that the cells are formatted to show both date and time (e.g., mm/dd/yyyy hh:mm AM/PM). Even if the time is present in the cell’s underlying value, a date-only format might make it invisible, but VBA should still read it correctly.
• Double-check that the cells in column 2 of your Excel worksheet actually contain valid date/time values (not text). You can add a validation check to avoid errors:

  If IsDate(wbAs.Worksheets(i).Cells(j, 2).Value) Then
      StartDate = wbAs.Worksheets(i).Cells(j, 2).Value
      ' ... rest of your code ...
  Else
      ' Handle invalid date values here (e.g., skip or log the error)
  End If
  

3. Side Note: Fix Index Variable Confusion #

In your original code, you’re using i for both the worksheet index and the task index. This can lead to bugs if you’re importing from multiple worksheets or have more tasks than worksheets. Using separate variables (like wsIndex for worksheets and taskIndex for tasks) will keep your logic clear and prevent unintended overwrites or skipped entries.

内容的提问来源于stack exchange，提问作者Andrea M.