MSACCESS VBA：向 Excel 工作表添加边框

MSACCESS VBA: Add borders to Excel Sheet

提问by Bruno

The below does not work. How do I add borders? Thanks!

以下不起作用。如何添加边框？谢谢！

            Set objApp = CreateObject("Excel.Application")
            objApp.Visible = True
            Set wb = objApp.Workbooks.Open("aFile.xls", True, False)
            objApp.Cells.Select
            objApp.Selection.Borders(xlDiagonalDown).LineStyle = xlNone
            objApp.Selection.Borders(xlDiagonalUp).LineStyle = xlNone
            With objApp.Selection.Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThin
            End With
            With objApp.Selection.Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThin
            End With
            With objApp.Selection.Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThin
            End With
            With objApp.Selection.Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThin
            End With
            With objApp.Selection.Borders(xlInsideVertical)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThin
            End With
            With objApp.Selection.Borders(xlInsideHorizontal)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThin
            End With

            Set objApp = Nothing

采纳答案by Bruno

The VBA code did not work for me so I discovered a workaround. Since I was using an Excel template to create the Excel sheet. I modified the Excel template to print Gridlines.

VBA 代码对我不起作用，所以我发现了一个解决方法。因为我使用 Excel 模板来创建 Excel 工作表。我修改了 Excel 模板来打印网格线。

To enable Gridlines to Print:

要启用网格线打印：

Excel > Print Preview > Page Setup > Sheet > Check mark gridlines under Print.

Excel > 打印预览 > 页面设置 > 工作表 > 选中打印下的网格线。

回答by Tim Williams

Set wb = objApp.Workbooks.Open("aFile.xls", True, False)
wb.Sheets(1).UsedRange.Borders.Weight=xlThin

Better to avoid formatting the entire sheet needlessly.

最好避免不必要地格式化整个工作表。

回答by Octopus Prime

I had the same issue and the "borders.weight" construction works for me and there's no need to use ".cells" to refer to a range either. Example:

我有同样的问题，“borders.weight”结构对我有用，也不需要使用“.cells”来指代一个范围。例子：

.Range("A11:H11").Borders.Weight = 2

回答by Banjoe

Set objApp = CreateObject("Excel.Application")
objApp.Visible = True
Set wb = objApp.Workbooks.Open("aFile.xls", True, False)
With objApp.Cells.Borders
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = 0
End With

This should give you a border around all cells on the ACTIVE sheet of the workbook you opened. Diaganols are off by default.

这应该为您打开的工作簿的 ACTIVE 工作表上的所有单元格提供边框。默认情况下，Diaganols 处于关闭状态。

回答by Daniele Calanchi

The problem is that Access doesn't know the Excel enumerations, try this:

问题是 Access 不知道 Excel 枚举，试试这个：

Function CreateBorders(Range As Object)
    With Range.Borders(7)
        .LineStyle = 1
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = 2
    End With
    With Range.Borders(8)
        .LineStyle = 1
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = 2
    End With
    With Range.Borders(9)
        .LineStyle = 1
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = 2
    End With
    With Range.Borders(10)
        .LineStyle = 1
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = 2
    End With
    With Range.Borders(11)
        .LineStyle = 1
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = 2
    End With
    With Range.Borders(12)
        .LineStyle = 1
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = 2
    End With
End Function

回答by Max Ooi

I have been working on this pretty long time, after reading this dicussion it gave me some ideas and I changed my codes accordingly and it works for me.. Here is the code to share with you..

我一直在研究这个相当长的时间，在阅读了这个讨论后，它给了我一些想法，我相应地更改了我的代码，它对我有用..这是与您分享的代码..

To format all borders to xlMedium:

要将所有边框格式化为 xlMedium：

With objApp.Worksheets("AR Tab").Range(.cells(4, 5), .cells(4, 8))
  '.LineStyle = xlMedium    
  .Borders.Weight = 3
End With

To format only left border to xlMedium:

仅将左边框格式化为 xlMedium：

With objApp.Worksheets("AR Tab").Range(.cells(4, 5), .cells(4, 8))
  '.LineStyle = xlMedium
  .Borders("1").Weight = 3
End With

If you would like to have different linestyle:

如果您想要不同的线条样式：

.Borders("1").LineStyle = 1 'Line Style xlContinues
.Borders("1").LineStyle = 2 'Line Style xlDash

Hope it helps

希望能帮助到你

回答by Bill Riendeau

Get the workbook and worksheet as variables, then just set the borders for used range.

获取工作簿和工作表作为变量，然后只需设置使用范围的边框。

Set objXLBook = objXLApp.Workbooks.Open(strXLSFile)
Set objXLSheet = objXLBook.Sheets("Name of Sheet")

objXLSheet.UsedRange.Borders.LineStyle = xlContinuous