asked on

How do I set just the bottom line of an excel border?

How do I set just the bottom line of an excel border? I've found some examples but none work. I get the default(I think) of a border all around the merged cells.
Here is a bit of code I'm using for test:

Dim objApp As Excel.Application
        Dim objBook As Excel._Workbook
        Dim objBooks As Excel.Workbooks
        Dim objSheets As Excel.Sheets
        Dim objSheet As Excel._Worksheet
        Dim range As Excel.Range
        Dim uintRowIndex As UInt16 = 1
        Dim misValue As Object = Reflection.Missing.Value

        FrmMain.Cursor = Cursors.WaitCursor
        Try

            strtModualCrashPoint = "LoadExcelWithItemsFromSearchedDatasets 0" '<<<ERR LOCATION

            ' Create a new instance of Excel and start a new workbook.
            objApp = New Excel.Application()
            objBooks = objApp.Workbooks
            objBook = objBooks.Add(misValue)
            objSheets = objBook.Worksheets
            objSheet = objSheets("Sheet1")

            strtModualCrashPoint = "LoadExcelWithItemsFromSearchedDatasets 1" '<<<ERR LOCATION

            ' objSheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaper11x17
            objSheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperLetter
            objSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait


            With objSheet.Range("A2:I2")
                .Merge()
                .Borders(Excel.XlBordersIndex.xlEdgeBottom) '<<<<<<<<<<< not working.

                .Borders.LineStyle = Excel.XlLineStyle.xlContinuous
                .Borders.Weight = 2
                .Font.Bold = True
                .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                .Value = "DDS PN :" & dsPartItemTable.Tables("PartItem").Rows(uint16PartSearch).Item(PartItemField.DDS_PN)
            End With

Open in new window

Thanks in advance
Dave

Alan

Hi,

This is what I use:

With rMyRange.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
End With

Open in new window

In Excel VBA (at least) the merge method does not the brackets on the end.

The Borders line location (xlEdgeBottom) is an object itself, not a property of the cell, so it needs to be part of the with statement, so your code would become:

With objSheet.Range("A2:I2")

    .Merge

    With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
        .LineStyle = Excel.XlLineStyle.xlContinuous
        .Weight = 2
    End With 

    .Font.Bold = True
    .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
    .Value = "DDS PN :" & dsPartItemTable.Tables("PartItem").Rows(uint16PartSearch).Item(PartItemField.DDS_PN)

End With

Open in new window

Assuming that both:

objSheet

and

dsPartItemTable.Tables("PartItem").Rows(uint16PartSearch).Item(PartItemField.DDS_PN)

both return valid results, does that work for you?

If either of those might be causing a problem, temporarily hard code something in their place, and re-test, then you can work to resolve those.

Alan.

David Peruso

ASKER

It indicates that 'xlEdgeBottom' is not declared.

With objSheet.Range("A1:I1").Borders(xlEdgeBottom)

End With

David Peruso

ASKER

Ok, that worked. thanks,

Alan

Cool - Glad to help.

Thanks,

Alan.