title: Format Excel Worksheet From Access VBA
body: <p>I am outputting query results to Excel and want to format through my Access VBA syntax.  Everytime I try to add in the formatting, I get errors.  The one I am stuck on is</p>
  
  <blockquote>
    <p>Unable to set the HorizontalAlignment property of the Range class</p>
  </blockquote>
  
  <p>This is my syntax - what should be altered so that the syntax can modify Excel as needed?</p>
  
  <pre><code>Private Sub ToExcel_Click()
  Dim lngColumn As Long
  Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim strPathFileName As String, strWorksheetName As String
  Dim strRecordsetDataSource As String
  Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
  
      blnEXCEL = False
  
      strPathFileName = "Z:\House\Data.xlsx"
      strRecordsetDataSource = "Fa"
      blnHeaderRow = True
      On Error Resume Next
      Set xlx = GetObject(, "Excel.Application")
      If Err.Number &lt;&gt; 0 Then
            Set xlx = CreateObject("Excel.Application")
            blnEXCEL = True
      End If
      Err.Clear
      On Error GoTo 0
      xlx.Visible = True
      Set xlw = xlx.Workbooks.Add
      Set xls = xlw.Worksheets(1)
      xls.Name = "Cu"
      Set xlc = xls.Range("A1")
      Set dbs = CurrentDb()
      Set rst = dbs.OpenRecordset(strRecordsetDataSource, dbOpenDynaset, dbReadOnly)
      If rst.EOF = False And rst.BOF = False Then
            If blnHeaderRow = True Then
                  For lngColumn = 0 To rst.Fields.Count - 1
                        xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Name
                  Next lngColumn
                  Set xlc = xlc.Offset(1, 0)
            End If
            xlc.CopyFromRecordset rst
      End If
  
      rst.Close
      Set rst = Nothing
      dbs.Close
      Set dbs = Nothing
  
      With xls
          .Range("A1:N1").Select
          .Columns("A:N").HorizontalAlignment = xlCenter
          .Columns("A:N").VerticalAlignment = xlBottom
          .Columns("A:N").WrapText = True
          .Columns("A:N").Orientation = 0
          .Columns("A:N").AddIndent = False
          .Columns("A:N").IndentLevel = 0
          .Columns("A:N").ShrinkToFit = False
          .Columns("A:N").ReadingOrder = xlContext
          .Columns("A:N").MergeCells = False
          .Columns("A:N").Selection.Font.Bold = True
          .Columns("N:N").ColumnWidth = 8.86
          .Columns("I:I").ColumnWidth = 8.86
          .Columns("C:G").Select
          .Selection.NumberFormat = "$#,##0"
          .Columns("J:J").Select
          .Selection.NumberFormat = "$#,##0"
          .Columns("K:M").Select
          .Selection.NumberFormat = "0%"
          .Range("P18").Select
          .Columns("A:A").EntireColumn.AutoFit
          .Columns("B:B").EntireColumn.AutoFit
          .Range("A1").Select
      End With
  
      Set xlc = Nothing
      Set xls = Nothing
      xlw.SaveAs strPathFileName
      xlw.Close False
      Set xlw = Nothing
      If blnEXCEL = True Then xlx.Quit
      Set xlx = Nothing
  End Sub
  </code></pre>
  
  <p><strong>EDIT</strong><br>
  I tried the below syntax, and I get an error of </p>
  
  <blockquote>
    <p>object required</p>
  </blockquote>
  
  <p>on the line <code>Selection.Font.Bold = True</code></p>
  
  <p>this is full modified syntax</p>
  
  <pre><code>    With xls
          '.Range("A1:N1").Select
          .Columns("A:N").Select
          Selection.Font.Bold = True
          .Columns("A:N").HorizontalAlignment = xlCenter
          .Columns("A:N").VerticalAlignment = xlBottom
          .Columns("A:N").WrapText = True
          .Columns("A:N").Orientation = 0
          .Columns("A:N").AddIndent = False
          .Columns("A:N").IndentLevel = 0
          .Columns("A:N").ShrinkToFit = False
          .Columns("A:N").ReadingOrder = xlContext
          .Columns("A:N").MergeCells = False
          '.Columns("A:N").Selection.Font.Bold = True
          .Columns("N:N").ColumnWidth = 8.86
          .Columns("I:I").ColumnWidth = 8.86
          .Columns("C:G").Select
          Selection.NumberFormat = "$#,##0"
          .Columns("J:J").Select
          Selection.NumberFormat = "$#,##0"
          .Columns("K:M").Select
          Selection.NumberFormat = "0%"
          .Range("P18").Select
          .Columns("A:A").EntireColumn.AutoFit
          .Columns("B:B").EntireColumn.AutoFit
          .Range("A1").Select
      End With
  </code></pre>