Problems transferring data type from Msproject to Excel

Asked

Viewed 57 times

0

Hello. I’m having trouble transferring some dates like Baseline Start, Baseline Finish, Actual Start, Actual Msproject Finish to a spreadsheet in excel. Here is the code snippet.

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 remove the schedules that come along with the date. But when this date is passed to the spreadsheet in excel, it comes in mm/dd/yyyy format. If I don’t use substring, the date comes in dd/mm/yyyy format but with the hours.

When I don’t use a substring, the date is passed so 05/12/2016 08:00:00

When I use the Mid function, the value is like this. inserir a descrição da imagem aqui

However, the date is passed to the spreadsheet with the following value. inserir a descrição da imagem aqui

What could be causing this inversion in format?

excel vba

  • Whether Excel automatically converts the date to "mm/dd/yyyy", probably your system is configured in English, which would justify the change. Try putting xlSheet.Cells(i,2).NumberFormat = "dd/mm/yyyy" after the data have been copied.

  • Luiz, you are right, my system is in English. But your solution worked when I put the format "mm/dd/yyyy". The reason I do not know.

  • The reason is this: the configuration of the system in English (American) awaits the date with the month before the day. If it worked as you said, probably the system is in English and Excel in Portuguese. But anyway, if it worked, it’s worth it. I will post as an answer to help other people in the future. So, if you really think the answer helped you, please consider mark her as accepted, okay?

1 answer

1


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

The ideal would be for you to configure your system to work in the user’s date format (Windows has the locale setting in the Control Panel). But a stopgap solution is you force the formatting in Excel. Use something like:

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

to define a specific cell formatting. As you have already noted in comments, maybe you should indicate that the format originally received is American and let Excel handle later (in this case, indicate "mm/dd/yyy" even in the NumberFormat).

Browser other questions tagged excel vba

You are not signed in. Login or sign up in order to post.