Formatting Excel Cells from Access Not Working

user_18061803 0
Viewed 30

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
export-to-excel ms-access vba
1 Answers
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
user_92458530
Related