Problems transferring date type from MSProject to Excel

Question

Problems transferring date type from MSProject to Excel

Navigation

#1 by (1 votes)

0

Hello. I'm having trouble transferring some Baseline Start, Baseline Finish, Actual Start, Current Finish dates from MSProject to a worksheet in excel. Here is the snippet of code.

Dim xlSheet As Excel.Worksheet
Dim wb As Workbook
Dim Prog As MSProject.Project
Dim Proj As MSProject.Application
Dim t As Task
Dim i As Long

[...]

For Each t In Prog.Tasks
    i = i + 1

    Set xlSheet = wb.Worksheets(6)
        xlSheet.Cells(i, 1).Value = Proj.GetCellInfo(7, i).Task.Name
        xlSheet.Cells(i, 2).Value = Mid((Proj.GetCellInfo(10, i).Task.BaselineStart), 1, 11)
        xlSheet.Cells(i, 3).Value = Mid((Proj.GetCellInfo(11, i).Task.BaselineFinish), 1 , 11)
        xlSheet.Cells(i, 4).Value = Mid((Proj.GetCellInfo(12, i).Task.ActualStart), 1 , 11)
        xlSheet.Cells(i, 5).Value = Mid((Proj.GetCellInfo(13, i).Task.ActualFinish), 1 , 11)
        xlSheet.Cells(i, 6).Value = Proj.GetCellInfo(14, i).Task.ResourceNames

Next t

When I use the mid substring, it is to withdraw the times that come along with the date. But when this date is passed to the excel sheet, it comes in mm / dd / yyyy format. If I do not use the substring, the date comes in the format dd / mm / yyyy but with the hours.

When I do not use a substring, the date is passed like this 05/12/2016 08:00:00

When I use the Mid function, the value looks like this.

However,thedateispassedtotheworksheetwiththefollowingvalue.

What could be causing this inversion in the format?

excel vba

asked by anonymous 23.02.2017 / 14:30

1 answer

TypeError: 'float' object is not iterable in CHOICE function in 2D Python List Find the index of dataRow

score 1 · Accepted Answer

Your operating system is probably set to American English, since it is in this configuration that the date is expected to be provided with the month before the day (ie mm/dd/yyyy ).

Ideally you should configure your system to work in the user's date format (Windows has the locale setting in Control Panel). But a palliative solution is you force formatting in Excel. Use something like:

xlSheet.Cells(i,2).NumberFormat = "dd/mm/yyyy"

to define a specific format for a cell. As you yourself have already noted in the comments, you may wish to indicate that the format originally received is American and let Excel handle later (in that case, indicate% with% of% even in% with%).