Formatting Excel Cells from Access Not Working-CodePudding

I am trying to simply create an Excel sheet with lots of formatting in it from Access via VBA.

With this example I'm just trying to select A1 to E1 and turn the color of the cells to the color blue with a left border to each cell yet nothing happens, the Excel file opens but it just sits there and does nothing. What am I missing?

Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim xlWorkbook As Excel.Workbook

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWorkbook = xlApp.Workbooks.Add
Set xlSheet = xlWorkbook.Sheets(1)

With xlSheet.Range("A1:E1").Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = 5
End With

CodePudding user response：

With xlSheet.Range("A1:E1").Borders(xlEdgeLeft)

This only applies a border to the left of A1.

Specify each cell:

With xlSheet.Range("A1,B1,C1,D1,E1").Borders(xlEdgeLeft)

or use a loop:

Dim cell As Excel.Range
For Each cell in xlSheet.Range("A1:E1")
   With cell.Borders(xlEdgeLeft)
       .LineStyle = xlContinuous
       .Weight = xlThin
       .ColorIndex = 5       
   End With
Next

Note that .ColorIndex is an index into a color palette, not an absolute color. Try the following instead:

.Color = vbBlue