Problems encountered when using "HorizontalAlignment"

[Doraemon]

I am trying to export data to an Excel file, which has a title "Application List" placed across cell A1 to cell E1. I want the title to be align in the center rather than in the left.

However, error is found in the indicated line, saying:
"Unable to set the HorizontalAlignment property of the Range class"

Notes: I have tried replacing "xlcenter" in the error line with "xlHAlignCenterAcrossSelection", "xlHAlignCenter", but still can't work.

Following is part of my code:

With xlobj
.Workbooks.Open FileName:=strExcelFile
.Worksheets("Sheet1&quot.Select

With .range("A1", "E1&quot
.Merge
.Value = "Application List"
.Font.Size = 14
.Font.Color = 5
.Font.Bold = True
.Interior.ColorIndex = 24
.HorizontalAlignment = xlcenter <--- Error
.Select
End With
End With

Thank for help in advance.

 

[VBslammer]

I think your problem is that you're referencing the Range of the Application rather than the sheet, i.e.

 xlObj.Range

Instead of

 xlObj.WorkBooks(1).Sheets(1).Range("A1","E1")

It's best to use explicit references when automating an app:

Sub OpenExcel(ByVal strExcelFile As String)
On Error GoTo ErrHandler

  Dim xl As Excel.Application
  Dim wb As Excel.Workbook
  Dim sht As Excel.Worksheet
  
  Set xl = New Excel.Application
  xl.Visible = True
  
  Set wb = xl.Workbooks.Open(strExcelFile)
  
  Set sht = wb.Sheets("Sheet1")
  
  With sht.Range("A1", "E1")
    .Merge
    .Value = "Application List"
    .Font.Size = 14
    .Font.Color = 5
    .Interior.ColorIndex = 6
    .Font.Bold = True
    .HorizontalAlignment = xlHAlignCenter
    .Select
  End With
  
ExitHere:
  On Error Resume Next
  Set sht = Nothing
  Set wb = Nothing
  Set xl = Nothing
  Exit Sub
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Sub

VBSlammer

Unemployed in Houston, Texas