[RESOLVED] Vb.net excel hide gridlines

[GideonE]

Can someone please help me with the command to hide gridlines when creating an Excel file?

I use the following code :

  Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet, xlWorkSheet2, xlWorkSheet3 As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        xlWorkSheet.Name = "BYLAE A"
        xlWorkSheet2 = xlWorkBook.Sheets.Add
        xlWorkSheet2.Name = "BYLAE B"
        xlWorkSheet3 = xlWorkBook.Sheets.Add
        xlWorkSheet3.Name = "BYLAE C"

And my imports are :

Imports System.ComponentModel
Imports System.IO
Imports ClosedXML.Excel
Imports HtmlAgilityPack
Imports Microsoft.Office.Interop
Imports MySql.Data.MySqlClient
Imports Z.Dapper.Plus

I would have thought xlWorkSheet.displaygridline = false would do the trick, but it is not recognized as a command

[OptionBase1]

The below thread is for VBA, but it might shed light on what object you need to reference to turn them off.

https://stackoverflow.com/questions/...g-activewindow

[si_the_geek]

When it comes to working out how to do things inside Excel via code, let Excel itself help you out... open Excel, and record a Macro of you performing the task manually, then view the code of the macro.

You should be able to spot which line(s) of the code matter, and with some minor modifications you can use the relevant part(s) of that code in your VB.Net code.

[GideonE]

Thank you for that tip Si

However I have tried :

xlWorkSheet.DisplayGridlines = False

I get the following error when I run (not in the editing mode of Visual studio)

System.MissingMemberException: 'Public member 'DisplayGridlines' on type 'Worksheet' not found.'


I also tried :

xlWorkSheet.Activate()
xlWorkSheet.DisplayGridlines = False

I get the same error message.

Regards

[Arnoutdv]

It should work as you tried, see this MS page:
https://learn.microsoft.com/en-us/of...splaygridlines

But others seem to encounter problems too:
https://stackoverflow.com/questions/...-excel-interop

[ChrisE]

@GideonE
don't try and hide the Gridlines, just define your range and color the Borders to White
I did need this in the past so here a sample

Option Strict On

Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop

Public Class Form1

    Public Enum Excel_Colors
        rgbAliceBlue = 16775408          'Alice Blue
        rgbAntiqueWhite = 14150650       'Antique White
        rgbAqua = 16776960               'Aqua
        rgbAquamarine = 13959039         'Aquamarine
        rgbAzure = 16777200              'Azure
        rgbBeige = 14480885              'Beige
        rgbBisque = 12903679             'Bisque
        rgbBlack = 0                     'Black
        rgbBlanchedAlmond = 13495295     'Blanched Almond
        rgbBlue = 16711680               'Blue
        rgbBlueViolet = 14822282         'Blue Violet
        rgbBrown = 2763429               'Brown
        rgbBurlyWood = 8894686           'Burly Wood
        rgbCadetBlue = 10526303          'Cadet Blue
        rgbChartreuse = 65407            'Chartreuse
        rgbCoral = 5275647               'Coral
        rgbCornflowerBlue = 15570276     'Cornflower Blue
        rgbCornsilk = 14481663           'Cornsilk
        rgbCrimson = 3937500             'Crimson
        rgbDarkBlue = 9109504            'Dark Blue
        rgbDarkCyan = 9145088            'Dark Cyan
        rgbDarkGoldenrod = 755384        'Dark Goldenrod
        rgbDarkGray = 11119017           'Dark Gray
        rgbDarkGreen = 25600             'Dark Green
        rgbDarkGrey = 11119017           'Dark Grey
        rgbDarkKhaki = 7059389           'Dark Khaki
        rgbDarkMagenta = 9109643         'Dark Magenta
        rgbDarkOliveGreen = 3107669      'Dark Olive Green
        rgbDarkOrange = 36095            'Dark Orange
        rgbDarkOrchid = 13382297         'Dark Orchid
        rgbDarkRed = 139                 'Dark Red
        rgbDarkSalmon = 8034025          'Dark Salmon
        rgbDarkSeaGreen = 9419919        'Dark Sea Green
        rgbDarkSlateBlue = 9125192       'Dark Slate Blue
        rgbDarkSlateGray = 5197615       'Dark Slate Gray
        rgbDarkSlateGrey = 5197615       'Dark Slate Grey
        rgbDarkTurquoise = 13749760      'Dark Turquoise
        rgbDarkViolet = 13828244         'Dark Violet
        rgbDeepPink = 9639167            'Deep Pink
        rgbDeepSkyBlue = 16760576        'Deep Sky Blue
        rgbDimGray = 6908265             'Dim Gray
        rgbDimGrey = 6908265             'Dim Grey
        rgbDodgerBlue = 16748574         'Dodger Blue
        rgbFireBrick = 2237106           'Fire Brick
        rgbFloralWhite = 15792895        'Floral White
        rgbForestGreen = 2263842         'Forest Green
        rgbFuchsia = 16711935            'Fuchsia
        rgbGainsboro = 14474460          'Gainsboro
        rgbGhostWhite = 16775416         'Ghost White
        rgbGold = 55295                  'Gold
        rgbGoldenrod = 2139610           'Goldenrod
        rgbGray = 8421504                'Gray
        rgbGreen = 32768                 'Green
        rgbGreenYellow = 3145645         'Green Yellow
        rgbGrey = 8421504                'Grey
        rgbHoneydew = 15794160           'Honeydew
        rgbHotPink = 11823615            'Hot Pink
        rgbIndianRed = 6053069           'Indian Red
        rgbIndigo = 8519755              'Indigo
        rgbIvory = 15794175              'Ivory
        rgbKhaki = 9234160               'Khaki
        rgbLavender = 16443110           'Lavender
        rgbLavenderBlush = 16118015      'Lavender Blush
        rgbLawnGreen = 64636             'Lawn Green
        rgbLemonChiffon = 13499135       'Lemon Chiffon
        rgbLightBlue = 15128749          'Light Blue
        rgbLightCoral = 8421616          'Light Coral
        rgbLightCyan = 9145088           'Light Cyan
        rgbLightGoldenrodYellow = 13826810   'LightGoldenrodYellow
        rgbLightGray = 13882323          'Light Gray
        rgbLightGreen = 9498256          'Light Green
        rgbLightGrey = 13882323          'Light Grey
        rgbLightPink = 12695295          'Light Pink
        rgbLightSalmon = 8036607         'Light Salmon
        rgbLightSeaGreen = 11186720      'Light Sea Green
        rgbLightSkyBlue = 16436871       'Light Sky Blue
        rgbLightSlateGray = 10061943     'Light Slate Gray
        rgbLightSteelBlue = 14599344     'Light Steel Blue
        rgbLightYellow = 14745599        'Light Yellow
        rgbLime = 65280                  'Lime
        rgbLimeGreen = 3329330           'Lime Green
        rgbLinen = 15134970              'Linen
        rgbMaroon = 128                  'Maroon
        rgbMediumAquamarine = 11206502   'Medium Aquamarine
        rgbMediumBlue = 13434880         'Medium Blue
        rgbMediumOrchid = 13850042       'Medium Orchid
        rgbMediumPurple = 14381203       'Medium Purple
        rgbMediumSeaGreen = 7451452      'Medium Sea Green
        rgbMediumSlateBlue = 15624315    'Medium Slate Blue
        rgbMediumSpringGreen = 10156544  'Medium Spring Green
        rgbMediumTurquoise = 13422920    'Medium Turquoise
        rgbMediumVioletRed = 8721863     'Medium Violet Red
        rgbMidnightBlue = 7346457        'Midnight Blue
        rgbMintCream = 16449525          'Mint Cream
        rgbMistyRose = 14804223          'Misty Rose
        rgbMoccasin = 11920639           'Moccasin
        rgbNavajoWhite = 11394815        'Navajo White
        rgbNavy = 8388608                'Navy
        rgbNavyBlue = 8388608            'Navy Blue
        rgbOldLace = 15136253            'Old Lace
        rgbOlive = 32896                 'Olive
        rgbOliveDrab = 2330219           'Olive Drab
        rgbOrange = 42495                'Orange
        rgbOrangeRed = 17919             'Orange Red
        rgbOrchid = 14053594             'Orchid
        rgbPaleGoldenrod = 7071982       'Pale Goldenrod
        rgbPaleGreen = 10025880          'Pale Green
        rgbPaleTurquoise = 15658671      'Pale Turquoise
        rgbPaleVioletRed = 9662683       'Pale Violet Red
        rgbPapayaWhip = 14020607         'Papaya Whip
        rgbPeachPuff = 12180223          'Peach Puff
        rgbPeru = 4163021                'Peru
        rgbPink = 13353215               'Pink
        rgbPlum = 14524637               'Plum
        rgbPowderBlue = 15130800         'Powder Blue
        rgbPurple = 8388736              'Purple
        rgbRed = 255                     'Red
        rgbRosyBrown = 9408444           'Rosy Brown
        rgbRoyalBlue = 14772545          'Royal Blue
        rgbSalmon = 7504122              'Salmon
        rgbSandyBrown = 6333684          'Sandy Brown
        rgbSeaGreen = 5737262            'Sea Green
        rgbSeashell = 15660543           'Seashell
        rgbSienna = 2970272              'Sienna
        rgbSilver = 12632256             'Silver
        rgbSkyBlue = 15453831            'Sky Blue
        rgbSlateBlue = 13458026          'Slate Blue
        rgbSlateGray = 9470064           'Slate Gray
        rgbSnow = 16448255               'Snow
        rgbSpringGreen = 8388352         'Spring Green
        rgbSteelBlue = 11829830          'Steel Blue
        rgbTan = 9221330                 'Tan
        rgbTeal = 8421376                'Teal
        rgbThistle = 14204888            'Thistle
        rgbTomato = 4678655              'Tomato
        rgbTurquoise = 13688896          'Turquoise
        rgbViolet = 15631086             'Violet
        rgbWheat = 11788021              'Wheat
        rgbWhite = 16777215              'White
        rgbWhiteSmoke = 16119285         'White Smoke
        rgbYellow = 65535                'Yellow
        rgbYellowGreen = 3329434         'Yellow Green
    End Enum

    Public Enum Excel_ColorIndex
        Automatic = -4105
        Aqua = 42
        Black = 1
        Blue = 5
        BlueGray = 47
        BrightGreen = 4
        Brown = 53
        Cream = 19
        DarkBlue = 11
        DarkGreen = 51
        DarkPurple = 21
        DarkRed = 9
        DarkTeal = 49
        DarkYellow = 12
        Gold = 44
        Gray25 = 15
        Gray40 = 48
        Gray50 = 16
        Gray80 = 56
        Green = 10
        Indigo = 55
        Lavender = 39
        LightBlue = 41
        LightGreen = 35
        LightLavender = 24
        LightOrange = 45
        LightTurquoise = 20
        LightYellow = 36
        Lime = 43
        NavyBlue = 23
        OliveGreen = 52
        Orange = 46
        PaleBlue = 37
        Pink = 7
        Plum = 18
        PowderBlue = 17
        Red = 3
        Rose = 38
        Salmon = 22
        SeaGreen = 50
        SkyBlue = 33
        Tan = 40
        Teal = 14
        Turquoise = 8
        Violet = 13
        White = 2
        Yellow = 6
        '    Blue = 32 'duplicate of 5
        '    DarkBlue = 25 'duplicate of 11
        '    DarkRed = 30 'duplicate of 9
        '    LightTurquoise = 34 'duplicate of 20
        '    Pink = 26 'duplicate of 7
        '    Plum = 54 'duplicate of 18
        '    Teal = 31 'duplicate of 14
        '    Turquoise = 28 'duplicate of 8
        '    Violet = 29 'duplicate of 13
        '    Yellow = 27 'duplicate of 6
    End Enum


    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Try
            Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
            Dim xlWb As Microsoft.Office.Interop.Excel.Workbook
            xlWb = xlApp.Workbooks.Open("D:\TestFolder\vbexcel.xlsx")
            Dim xlSt As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWb.Worksheets("Tabelle4"), Worksheet)

            'Select Range and Color
            With xlSt.Range("A2", "F10")
                .Font.Bold = True
                .Borders.Color = Excel_Colors.rgbWhite
            End With

            xlWb.Save()
            xlApp.Quit()
            xlApp = Nothing
        Catch g As Exception
            MessageBox.Show(g.ToString)
        End Try
    End Sub
End Class

[.paul.]

Thank you for that tip Si

However I have tried :

xlWorkSheet.DisplayGridlines = False

I get the following error when I run (not in the editing mode of Visual studio)

System.MissingMemberException: 'Public member 'DisplayGridlines' on type 'Worksheet' not found.'


I also tried :

xlWorkSheet.Activate()
xlWorkSheet.DisplayGridlines = False

I get the same error message.

Regards

I think you’ll find DisplayGridlines is a member of Range, rather than WorkSheet.Try this…

Dim x as Range = Worksheets("Sheet name").Cells
x.DisplayGridlines = False

[.paul.]

Actually, after some more research, it appears displaygridlines is a member of window...

https://learn.microsoft.com/en-us/of...splaygridlines

[dbasnett]

Actually, after some more research, it appears displaygridlines is a member of window...

https://learn.microsoft.com/en-us/of...splaygridlines

Agreed.

xlApp.ActiveWindow.DisplayGridlines = True / False

Where xlApp is your instance of the Excel application.

[GideonE]

Thank you dbasnett that did it for me.

[dbasnett]

Thank you dbasnett that did it for me.

It was actually .paul.