none
Excel.Style for Border RRS feed

  • Question

  • Hi All,

    I want to create a Excel Style to apply it on a lot of cells previously I was using BorderAround method of Range object and I want to achieve same functionality with Excel Style So here's my code for that:

    Excel.Style poValueStyle = ActiveWorkbook.Styles.Add("POValueStyle");
                poValueStyle.IncludeBorder = true;
                poValueStyle.Borders[(Excel.XlBordersIndex)Excel.Constants.xlTop].LineStyle = Excel.XlLineStyle.xlContinuous;
                poValueStyle.Borders[(Excel.XlBordersIndex)Excel.Constants.xlBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
                poValueStyle.Borders[(Excel.XlBordersIndex)Excel.Constants.xlLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
                poValueStyle.Borders[(Excel.XlBordersIndex)Excel.Constants.xlRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                poValueStyle.Borders.Weight = Excel.XlBorderWeight.xlThin;
                poValueStyle.Borders.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;

    What happens is this that it gives me border around cell and also diagonally and I don't want diagonal border.  

    Thanks,


    Attiqe Ur Rehman

    Saturday, March 1, 2014 4:44 PM

All replies

  • Hello Attiqe,

    Excel provides the Macro recorder for generating the VBA macro code automatically. You can read more about the recorder in the Create or delete a macro article. I'd recommend comparing the code generated automatically with your own. Thus, you will be able to find the cause.

    Sunday, March 2, 2014 1:27 PM
  • Eugene,

    Here's the recorded Macro's code:

    Sub Macro1()
    '
    ' Macro1 Macro
    '
    
    '
        With ActiveWorkbook.Styles("POBorderStyle")
            .IncludeNumber = True
            .IncludeFont = True
            .IncludeAlignment = True
            .IncludeBorder = True
            .IncludePatterns = True
            .IncludeProtection = True
        End With
        With ActiveWorkbook.Styles("POBorderStyle").Borders(xlLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With ActiveWorkbook.Styles("POBorderStyle").Borders(xlRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With ActiveWorkbook.Styles("POBorderStyle").Borders(xlTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With ActiveWorkbook.Styles("POBorderStyle").Borders(xlBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        ActiveWorkbook.Styles("POBorderStyle").Borders(xlDiagonalDown).LineStyle = _
            xlNone
        ActiveWorkbook.Styles("POBorderStyle").Borders(xlDiagonalUp).LineStyle = xlNone
        Range("J13").Select
    End Sub

    And I have converted it to C#.NET.

    Excel.Workbook activeWorkbook = CurrentInstance.ExcelApp.ActiveWorkbook;
                Excel.Style style = activeWorkbook.Styles.Add("POBorderStyle");
                style.IncludeBorder = true;
                style.IncludeNumber = true;
                style.IncludeFont = true;
                style.IncludeAlignment = true;
                style.IncludeBorder = true;
                style.IncludePatterns = true;
                style.IncludeProtection = true;
    
                style.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
                style.Borders[Excel.XlBordersIndex.xlEdgeTop].TintAndShade = 0;
                style.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
    
                style.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
                style.Borders[Excel.XlBordersIndex.xlEdgeBottom].TintAndShade = 0;
                style.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
    
                style.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
                style.Borders[Excel.XlBordersIndex.xlEdgeLeft].TintAndShade = 0;
                style.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;
    
                style.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                style.Borders[Excel.XlBordersIndex.xlEdgeRight].TintAndShade = 0;
                style.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;
    
                style.Borders[Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
                style.Borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Excel.XlLineStyle.xlLineStyleNone;

    And this time I only had left border and nothing else.

    Thanks,


    Attiqe Ur Rehman

    Sunday, March 2, 2014 7:29 PM
  • Hello Attiqe,

    VB.NET and VBA are entirely different technologies. That is why I'd recommend breaking the long chain of property and method calls and place them on separate lines. For example:

    style.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous

    The Borders property of the Style class returns a  Borders collection that represents the borders of a style or a range of cells (including a range defined as part of a conditional format). The indexer in .Net equals to the Item property in VBA and returns a Border object that represents one of the borders of either a range of cells or a style. So, I'd recommend to get the object once and set all property against the single object, not get a new object each time.

    Monday, March 3, 2014 12:22 PM
  • Got your point. Let me work on it and I will get back to you ASAP.

    Thanks,


    Attiqe Ur Rehman

    Monday, March 3, 2014 12:27 PM
  • Hi Attiqe,

    I think I could reproduce your issue, and I am trying to involve someone familiar with this topic to further look at this issue. There might be some time delay. Appreciate your patience.

    Sorry for any inconvenience and have a nice day!


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, March 4, 2014 2:04 AM
    Moderator
  • Eugene, 

    I tried doing it your way no result :(

    Luna,

    I am waiting for your reply anxiously. 

    Thanks,


    Attiqe Ur Rehman

    Thursday, March 6, 2014 7:29 AM
  • Hello Attique,

    The issue with the code (converted from macro to C#) seems to be a bug with Excel Interop.

    In your scenario, you could use the following lines of code to your initial code.

    poValueStyle.Borders[Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
    poValueStyle.Borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Excel.XlLineStyle.xlLineStyleNone;

    This will make sure that the diagonal lines are not visible. Let me know if this works for you.

    Thanks,

    Sreerenj G Nair

    Tuesday, March 25, 2014 6:58 PM
  • Sreerenj ,

    Thanks for reply but it doesn't work for me.

    Thanks,

    Attiqe


    Attiqe Ur Rehman

    Thursday, March 27, 2014 4:22 AM
  • Hello Attiqe,

    I added the lines to the code that you shared while posting the query and it worked for me. Could you please share the full code so that I can perform some tests? Also, make sure that you have the latest updates installed for Office.

    Thanks,

    Sreerenj G Nair

    Thursday, March 27, 2014 4:07 PM