Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Project 2010 Data Types into Excel RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    1
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    1
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    1
    Sign in to vote

    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