MS project VBA - Setting Formating In Excel

Question

I am trying to use the following code which is run from MS Project to populate an xls sheet.

As part of that I want to set formating including some merging & font setting.

I have 2 issues: 1) "Application.CutCopyMode = False" runs ok in xls but fails when I use the code in MS Project 2) Formating set out is not applied to the cells but does not error

Any thoughts ?

Thanks

Terran

'add the task details from row i onwards
'Removes tasks that are complete and not valid such as roll ups
    For Each t In pj.Tasks
        If t.Rollup = False Then
            If t.Milestone = True Then
                If t.Text18 Like "" Then
                    If t.Text19 Like "" Then
                        i = i + 1

                        'merge the 1st 2 cells
                        'ActiveSheet.Range(i2, i4).Select
                        ' https://learn.microsoft.com/en-us/office/troubleshoot/office-developer/select-cells-rangs-with-visual-basic
                        'ActiveSheet.Cells(i, 2).Select
                        'With Selection
                        '    .HorizontalAlignment = xlLeft
                        '    .VerticalAlignment = xlBottom
                        '    .WrapText = False
                        '    .Orientation = 0
                        '    .AddIndent = False
                        '    .IndentLevel = 0
                        '    .ShrinkToFit = False
                        '    .ReadingOrder = xlContext
                        '    .MergeCells = True
                        'End With

                        'put the values in the cells & set font

                        ActiveSheet.Cells(i, 2).Select

                        With Selection.Font
                            .Name = "Arial"
                            .Size = 8
                            .Strikethrough = False
                            .Superscript = False
                            .Subscript = False
                            .OutlineFont = False
                            .Shadow = False
                            .Underline = xlUnderlineStyleNone
                            .ThemeColor = xlThemeColorLight1
                            .TintAndShade = 0
                            .ThemeFont = xlThemeFontNone
                        End With
                        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
                        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
                        With Selection.Borders(xlEdgeLeft)
                            .LineStyle = xlContinuous
                            .ColorIndex = 0
                            .TintAndShade = 0
                            .Weight = xlHairline
                        End With
                        With Selection.Borders(xlEdgeTop)
                            .LineStyle = xlContinuous
                            .ColorIndex = 0
                            .TintAndShade = 0
                            .Weight = xlHairline
                        End With
                        With Selection.Borders(xlEdgeBottom)
                            .LineStyle = xlContinuous
                            .ColorIndex = 0
                            .TintAndShade = 0
                            .Weight = xlHairline
                        End With
                        With Selection.Borders(xlEdgeRight)
                            .LineStyle = xlContinuous
                            .ColorIndex = 0
                            .TintAndShade = 0
                            .Weight = xlHairline
                        End With
                        With Selection.Borders(xlInsideVertical)
                            .LineStyle = xlContinuous
                            .ColorIndex = 0
                            .TintAndShade = 0
                            .Weight = xlHairline
                        End With
                        With Selection.Borders(xlInsideHorizontal)
                            .LineStyle = xlContinuous
                            .ColorIndex = 0
                            .TintAndShade = 0
                            .Weight = xlHairline
                        End With

' ------

                        ActiveSheet.Cells(i, 5).Select

                        With Selection.Font
                            .Name = "Arial"
                            .Size = 8
                            .Strikethrough = False
                            .Superscript = False
                            .Subscript = False
                            .OutlineFont = False
                            .Shadow = False
                            .Underline = xlUnderlineStyleNone
                            .ThemeColor = xlThemeColorLight1
                            .TintAndShade = 0
                            .ThemeFont = xlThemeFontNone
                        End With
                        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
                        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
                        With Selection.Borders(xlEdgeLeft)
                            .LineStyle = xlContinuous
                            .ColorIndex = 0
                            .TintAndShade = 0
                            .Weight = xlHairline
                        End With
                        With Selection.Borders(xlEdgeTop)
                            .LineStyle = xlContinuous
                            .ColorIndex = 0
                            .TintAndShade = 0
                            .Weight = xlHairline
                        End With
                        With Selection.Borders(xlEdgeBottom)
                            .LineStyle = xlContinuous
                            .ColorIndex = 0
                            .TintAndShade = 0
                            .Weight = xlHairline
                        End With
                        With Selection.Borders(xlEdgeRight)
                            .LineStyle = xlContinuous
                            .ColorIndex = 0
                            .TintAndShade = 0
                            .Weight = xlHairline
                        End With
                        With Selection.Borders(xlInsideVertical)
                            .LineStyle = xlContinuous
                            .ColorIndex = 0
                            .TintAndShade = 0
                            .Weight = xlHairline
                        End With
                        With Selection.Borders(xlInsideHorizontal)
                            .LineStyle = xlContinuous
                            .ColorIndex = 0
                            .TintAndShade = 0
                            .Weight = xlHairline
                        End With

                        'change number formnat
                        With Selection.NumberFormat = "d/m/yyyy"
                        End With


' ------

                            ActiveSheet.Cells(i, 6).Select

                            With Selection.Font
                                .Name = "Arial"
                                .Size = 8
                                .Strikethrough = False
                                .Superscript = False
                                .Subscript = False
                                .OutlineFont = False
                                .Shadow = False
                                .Underline = xlUnderlineStyleNone
                                .ThemeColor = xlThemeColorLight1
                                .TintAndShade = 0
                                .ThemeFont = xlThemeFontNone
                            End With
                            Selection.Borders(xlDiagonalDown).LineStyle = xlNone
                            Selection.Borders(xlDiagonalUp).LineStyle = xlNone
                            With Selection.Borders(xlEdgeLeft)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlEdgeTop)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlEdgeBottom)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlEdgeRight)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlInsideVertical)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlInsideHorizontal)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With

                            'change number formnat
                            With Selection.NumberFormat = "d/m/yyyy"
                            End With

    ' ------



                            ActiveSheet.Cells(i, 7).Select

                            With Selection.Font
                                .Name = "Arial"
                                .Size = 8
                                .Strikethrough = False
                                .Superscript = False
                                .Subscript = False
                                .OutlineFont = False
                                .Shadow = False
                                .Underline = xlUnderlineStyleNone
                                .ThemeColor = xlThemeColorLight1
                                .TintAndShade = 0
                                .ThemeFont = xlThemeFontNone
                            End With
                            Selection.Borders(xlDiagonalDown).LineStyle = xlNone
                            Selection.Borders(xlDiagonalUp).LineStyle = xlNone
                            With Selection.Borders(xlEdgeLeft)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlEdgeTop)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlEdgeBottom)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlEdgeRight)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlInsideVertical)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlInsideHorizontal)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With

                            'change number formnat
                            With Selection.NumberFormat = "d/m/yyyy"
                            End With

    ' ------

                            ActiveSheet.Cells(i, 8).Select

                            With Selection.Font
                                .Name = "Arial"
                                .Size = 8
                                .Strikethrough = False
                                .Superscript = False
                                .Subscript = False
                                .OutlineFont = False
                                .Shadow = False
                                .Underline = xlUnderlineStyleNone
                                .ThemeColor = xlThemeColorLight1
                                .TintAndShade = 0
                                .ThemeFont = xlThemeFontNone
                            End With
                            Selection.Borders(xlDiagonalDown).LineStyle = xlNone
                            Selection.Borders(xlDiagonalUp).LineStyle = xlNone
                            With Selection.Borders(xlEdgeLeft)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlEdgeTop)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlEdgeBottom)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlEdgeRight)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlInsideVertical)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlInsideHorizontal)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With

                            'change number formnat
                            With Selection.NumberFormat = "d/m/yyyy"
                            End With

    ' ------

                            ActiveSheet.Cells(i, 2).Select

                            With Selection.Font
                                .Name = "Arial"
                                .Size = 8
                                .Strikethrough = False
                                .Superscript = False
                                .Subscript = False
                                .OutlineFont = False
                                .Shadow = False
                                .Underline = xlUnderlineStyleNone
                                .ThemeColor = xlThemeColorLight1
                                .TintAndShade = 0
                                .ThemeFont = xlThemeFontNone
                            End With
                            Selection.Borders(xlDiagonalDown).LineStyle = xlNone
                            Selection.Borders(xlDiagonalUp).LineStyle = xlNone
                            With Selection.Borders(xlEdgeLeft)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlEdgeTop)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlEdgeBottom)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlEdgeRight)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlInsideVertical)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlInsideHorizontal)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With

                            'change number formnat
                            With Selection.NumberFormat = "d/m/yyyy"
                            End With

    ' ------

                            ActiveSheet.Cells(i, 9).Select

                            With Selection.Font
                                .Name = "Arial"
                                .Size = 8
                                .Strikethrough = False
                                .Superscript = False
                                .Subscript = False
                                .OutlineFont = False
                                .Shadow = False
                                .Underline = xlUnderlineStyleNone
                                .ThemeColor = xlThemeColorLight1
                                .TintAndShade = 0
                                .ThemeFont = xlThemeFontNone
                            End With
                            Selection.Borders(xlDiagonalDown).LineStyle = xlNone
                            Selection.Borders(xlDiagonalUp).LineStyle = xlNone
                            With Selection.Borders(xlEdgeLeft)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlEdgeTop)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlEdgeBottom)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlEdgeRight)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlInsideVertical)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlInsideHorizontal)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With

                            'change number formnat
                            With Selection.NumberFormat = "d/m/yyyy"
                            End With

    ' ------

                            ActiveSheet.Cells(i, 10).Select

                            With Selection.Font
                                .Name = "Arial"
                                .Size = 8
                                .Strikethrough = False
                                .Superscript = False
                                .Subscript = False
                                .OutlineFont = False
                                .Shadow = False
                                .Underline = xlUnderlineStyleNone
                                .ThemeColor = xlThemeColorLight1
                                .TintAndShade = 0
                                .ThemeFont = xlThemeFontNone
                            End With
                            Selection.Borders(xlDiagonalDown).LineStyle = xlNone
                            Selection.Borders(xlDiagonalUp).LineStyle = xlNone
                            With Selection.Borders(xlEdgeLeft)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlEdgeTop)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlEdgeBottom)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlEdgeRight)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlInsideVertical)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With
                            With Selection.Borders(xlInsideHorizontal)
                                .LineStyle = xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = xlHairline
                            End With

                            'change number formnat
                            With Selection.NumberFormat = "d/m/yyyy"
                            End With

                            xlSheet.Cells(i, 2).Value = t.Name
                            xlSheet.Cells(i, 5).Value = t.BaselineStart
                            xlSheet.Cells(i, 6).Value = t.BaselineFinish
                            xlSheet.Cells(i, 7).Value = t.Start
                            xlSheet.Cells(i, 8).Value = t.Finish
                            xlSheet.Cells(i, 9).Value = t.ActualStart
                            xlSheet.Cells(i, 10).Value = t.ActualFinish

                            'xlSheet.Cells(i, 11).Value = t.Notes
                        End If
                    End If
                End If
            End If
        Next t

I've never used MS Project but i guess it's like any other MS app. `Application` means the program itself. If you execute this in Excel, it will refer to Excel itself, but if you execute this in Ms Project, it will refer to Ms Project and could be that command does not exist. You neet to bind Excel to an object variable and then use that variable.Something like `AppExcel.CutCopyMode = False` then would work. To know about bindings, check http://learnexcelmacro.com/wp/2018/09/vba-referencing-and-early-binding-vs-late-binding/ IT will explain about binding in Excel, but apply it into MS Project — Foxfire And Burns And Burns, Aug 22 '19 at 10:08
Hi FFABAB - Thanks for the Application pointer I had missed that (long day). I've managed to fix it how ever the problem of affecting the font still seems to be there. I have tried a few diffrent methods with the latest been the code on the first page where I call xlsheet.range.select. The strange thing is that I can actually place data in the same sheet no issue - its just the formating that is an issue. I'm begining to thing this is limitation... Thanks T — Terran Brown, Aug 23 '19 at 14:46
Well I kind of got it to work but as code goes it feels horrible. Also With Selection.NumberFormat = "d/m/yyyy" does not work either. I'm wondering if it is worth going down this road as it feels like I am hitting blockers. I may just export all infomation to excel and format it from there. T — Terran Brown, Aug 23 '19 at 15:48
`Selection` & `ActiveSheet` are secretly members of the Excel.Application object too; it's confusing because you can refer to them without explicitly qualifying the reference, but what's happening under the hood is that you are using `Application.Selection` and `Application.ActiveSheet` which won't necessarily exist in MS Project. So first get rid of `On Error Resume Next` if you have it, as it's probably hiding some helpful error messages, then you _could_ use `AppExcel.ActiveSheet...`, or instead I strongly recommend you [avoid using Select](https://stackoverflow.com/q/10714251/6609896) — Greedo, Aug 23 '19 at 15:59
Terran, The use of objects such as "Selection" and "Activesheet" is risky as it may be changed by the user at run time. Better to instantiate variables at the beginning of code execution and use these captured objects. Also, you don't need to "Select" cells, that's simply how the macro recorder captures what you did. I'd recommend you write a simple function to format a range of cells and call that with the various cells you want to format, passing in variable parameters such as font size, line weight etc. — Malcolm Farrelle, Sep 10 '19 at 12:18
Re: "Selection.NumberFormat = "d/m/yyyy" does not work either" << this should work - are you certain that the selected cells are actually dates and not text entries masquerading as dates? Try selecting the cells and changing the format manually to confirm they really are dates. — Malcolm Farrelle, Sep 10 '19 at 13:54
Hi @MalcolmFarrelle - in the end I have dumped the data in to a tab in a template then ran the code I needed with in Excel. No idea why it worked better that way how ever it does so I am happy at this time. Thanks for getting back to me. — Terran Brown, Sep 18 '19 at 09:43

MS project VBA - Setting Formating In Excel

0 Answers0