I have a bit of VBA that I am trying to make my export from Access carry out at the export stage. I am wanting columns A, B, C, D, E and H to be formatted with horizontal alignment = center and vertical alignment = center.
My code is giving me the error at:
The full code is:
I also have another line for the vertical alignment which I am guessing will need to follow the same solution method as with the horizontal.
My code is giving me the error at:
Code:
.Columns("A:A,B:B,C:C,D:D,E:E,H:H").HorizontalAlignment = xlCenter
The full code is:
Code:
Set objApp = CreateObject("Excel.Application")
objApp.Visible = True
Set wb = objApp.workbooks.Open(FileName, True, False)
'select all worksheets & cells In turn
For Each WS In wb.worksheets
With WS
.Cells.Font.Name = "Arial"
lastrow = .Range("A1").currentregion.Rows.Count
lastCol = .Range("A1").currentregion.Columns.Count
.Columns("D").Font.Bold = True
.Columns("D").Font.Italic = True
.Range("H:H").NumberFormat = "[hh]:mm"
.Columns("H:H").Replace ":", ":"
.Columns("A:A").NumberFormat = "d-mmm-yy"
.Columns("A:A,B:B,C:C,D:D,E:E,H:H").HorizontalAlignment = xlCenter
.Columns("A:A,B:B,C:C,D:D,E:E,H:H").VerticalAlignment = "xlCenter"
.Columns("A:A,B:B,C:C,D:D,E:E,H:H").WrapText = False
.Columns("A:A,B:B,C:C,D:D,E:E,H:H").Orientation = 0
.Columns("A:A,B:B,C:C,D:D,E:E,H:H").AddIndent = False
.Columns("A:A,B:B,C:C,D:D,E:E,H:H").IndentLevel = 0
.Columns("A:A,B:B,C:C,D:D,E:E,H:H").ShrinkToFit = False
.Columns("A:A,B:B,C:C,D:D,E:E,H:H").ReadingOrder = "xlContext"
.Columns("A:A,B:B,C:C,D:D,E:E,H:H").MergeCells = False
.Rows(1).HorizontalAlignment = xlCenter
.Rows(1).VerticalAlignment = xlCenter
.Rows(1).Font.Bold = True
.Rows(1).Font.Italic = False
.Rows(1).Interior.Color = RGB(200, 200, 200)
.Rows(1).Font.Color = RGB(0, 0, 0)
.Cells.EntireColumn.AutoFit
End With
Next 'next worksheet
objApp.sheets(1).Activate
Set objApp = Nothing
I also have another line for the vertical alignment which I am guessing will need to follow the same solution method as with the horizontal.