Formatting Excel Spreadsheet within Access error

[purceld2]

I am getting the error below when I am running the code listed below. It is a the point where it is formating an Excel spreadsheet. I have copied the recorded excel macro into the VB Sub and referenced the Excel object.

and get

Error 1004: Unable to set the horizontal aligment property of the range class

Private Sub Command12_Click()
On Error GoTo LastLine
DoCmd.OutputTo acOutputQuery, "supplier gate call", acFormatXLS, _
"c:\documents and settings\802284714\My Documents\PC Data\Supplier Gate Call.xls", False

Dim xlObj
Set xlObj = CreateObject("excel.application")
xlObj.Visible = True
xlObj.Workbooks.Open "c:\documents and settings\802284714\My Documents\PC Data\Supplier Gate Call.xls"
xlObj.activeworkbook.saveas "c:\documents and settings\802284714\My Documents\PC Data\Supplier Gate Call 4.xls"
xlObj.Workbooks.Open "c:\documents and settings\802284714\My Documents\PC Data\Supplier Gate Call 4.xls"


xlObj.activesheet.Cells.Replace what:="(?????????)", replacement:=""
xlObj.activesheet.Range("O1").Select
xlObj.ActiveCell.FormulaR1C1 = "Customer Services" & Chr(10) & "Y/N"
xlObj.activesheet.Range("P1").Select
xlObj.ActiveCell.FormulaR1C1 = "BT Design" & Chr(10) & "Y/N"
xlObj.activesheet.Range("Q1").Select
xlObj.ActiveCell.FormulaR1C1 = "BT Design Area"
xlObj.activesheet.Range("R1").Select
xlObj.ActiveCell.FormulaR1C1 = "Billing" & Chr(10) & "Y/N"
xlObj.activesheet.Range("S1").Select
xlObj.ActiveCell.FormulaR1C1 = "Revenue Assurance" & Chr(10) & "Y/N"
xlObj.activesheet.Range("T1").Select
xlObj.ActiveCell.FormulaR1C1 = "Retail" & Chr(10) & "Y/N"

'Your recorded macro code begins here

xlObj.Range("O1:T1").Select
xlObj.Selection.NumberFormat = "@"
With xlObj.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With xlObj.Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With

xlObj.activeworkbook.Save
xlObj.activeworkbook.Close
xlObj.Quit

Exit Sub
LastLine:
MsgBox "Error " & Err.Number & ": " & Err.Description
End Sub

 

[KenHigg]

Not sure what the issue is exaclty but I would suggest tinkering with several things to help isolate the error. Fisrt, try setting it out side 'With xlObj.Selection...' thing. Something like:

xlObj.Selection.HorizontalAlignment = xlCenter

This would eliminate the entire 'With' being the problem.

Then just tinker with it using those kinds of methods...

???

 

[purceld2]

I have sorted the horizontal problem.

I now need to know The code which will put borders round a range of cells

 

[KenHigg]

Can you record a keystroke macro and the view the steps in code? You use to be able to do that (?)

edit: How did you fix the orther problem?