locked
Project 2010 Data Types into Excel RRS feed

  • Question

  • I'm an old hand at exporting things to Excel from Project, but have recently started using 2010 (moved on from 2007).

    It is causing me nightmares because it exports everything as Text (in the Data Type field) when I create a Map.

    Is there any way of getting it to export Numbers as Numbers and Dates as Dates instead of text, Excel just won't recognise them without me going through converting them?

    Tuesday, May 15, 2012 11:06 AM

Answers

  • Here you go: paste this code into a Module in Excel and it will convert the active sheet. It should be easy to add new features to it. It currently detects cells with work or duration ending with d or days and converts the cell to a number with the h or days added as a format. Now you can sum values. Dates are converted to dates and you can format them in Excel as you like. The macro sorts in you local windows short date format.

    Sub Test()
    Dim Rng As Range
        For Each Rng In ActiveSheet.Range("A1", ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))
            If Right(Rng.Text, 1) = "h" Then
                Rng = Val(Rng.Text)
                Rng.NumberFormat = "0 ""h"""
            ElseIf Right(Rng.Text, 4) = "days" Then
                Rng = Val(Rng.Text)
                Rng.NumberFormat = "0 ""days"""
            ElseIf IsDate(Rng.Value) Then
                Rng = CDate(Rng.Text)
                Rng.NumberFormat = "d/m/yy"
            End If
        Next Rng
    End Sub


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Wednesday, May 16, 2012 3:25 AM

All replies

  • While I am on the subject of formatting, is there any way to get User Definable Fields (like Date1 - 10) to come out in UK Format Dates instead of US Format?
    Tuesday, May 15, 2012 12:56 PM
  • I think you may need to have a macro in place to get these conversions done. Moving the thread to Project customization Forum, so that you can get more suggestions on macro/programming approach.

    Sapna S

    Tuesday, May 15, 2012 1:15 PM
  • I think you have given me the answer I was looking for, in upgrading my package I have downgraded in terms of functionality.

    One thing I am not is experienced in producing anything other than very basic macros.

    It is very disappointing to find something that what was standard has been removed from the package for anyone not using US Formats.


    • Edited by Cid Eaton Tuesday, May 15, 2012 4:16 PM
    Tuesday, May 15, 2012 2:12 PM
  • Here you go: paste this code into a Module in Excel and it will convert the active sheet. It should be easy to add new features to it. It currently detects cells with work or duration ending with d or days and converts the cell to a number with the h or days added as a format. Now you can sum values. Dates are converted to dates and you can format them in Excel as you like. The macro sorts in you local windows short date format.

    Sub Test()
    Dim Rng As Range
        For Each Rng In ActiveSheet.Range("A1", ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))
            If Right(Rng.Text, 1) = "h" Then
                Rng = Val(Rng.Text)
                Rng.NumberFormat = "0 ""h"""
            ElseIf Right(Rng.Text, 4) = "days" Then
                Rng = Val(Rng.Text)
                Rng.NumberFormat = "0 ""days"""
            ElseIf IsDate(Rng.Value) Then
                Rng = CDate(Rng.Text)
                Rng.NumberFormat = "d/m/yy"
            End If
        Next Rng
    End Sub


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Wednesday, May 16, 2012 3:25 AM