Run time Error 1004 (1 Viewer)

[ismailr]

Hi,
I wrote below code to export data from query to excel and then format the excel data. I am getting run time error ‘1004’ Unable to set the Horizontal Alignment Property of the Range Class. Below is the piece of code.

Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
Set rst = CurrentDb.OpenRecordset(sqltxt)
With xlApp
.Visible = True
.Workbooks.Add
.sheets("Sheet1").Select
.activesheet.Range("A5").Activate
colNo = 1
'below loop to show field headings
For i = 1 To rst.Fields.Count
If Me.cmbReportType <> "Summary" Then
If rst.Fields(i - 1).Name <> "EmployeeDesignation" Then
xlApp.activesheet.cells(5, colNo).Value = rst.Fields(i - 1).Name
colNo = colNo + 1
End If
Else
xlApp.activesheet.cells(5, colNo).Value = rst.Fields(i - 1).Name
colNo = colNo + 1
End If
Next i
rst.MoveFirst
rowNo = 6

Do Until rst.EOF
colNo = 1
For i = 1 To rst.Fields.Count
If Me.cmbReportType <> "Summary" Then
If rst.Fields(i - 1).Name <> "EmployeeDesignation" Then
xlApp.activesheet.cells(rowNo, colNo).Value = rst.Fields(i - 1)
colNo = colNo + 1
End If
Else
xlApp.activesheet.cells(rowNo, colNo).Value = rst.Fields(i - 1)
colNo = colNo + 1
End If
Next i
rowNo = rowNo + 1
rst.MoveNext
Loop
xlApp.cells.EntireColumn.AutoFit
.activesheet.Range("A1").Activate
.activesheet.Range("A1").Value = "Report: Banding Grading Algorithum"

'Format Data in Excel Sheet
'Format Heading
.activesheet.Range("A1").Activate
.Range("A1").Font.Name = "Times New Roman"
.Range("A1").Font.FontStyle = "Bold"
.Range("A1").Font.Size = 12

'Format Table Column Headings.
For i = 1 To colNo
xlApp.activesheet.cells(5, i).Font.Name = "Times New Roman"
xlApp.activesheet.cells(5, i).Font.FontStyle = "Bold"
xlApp.activesheet.cells(5, i).Font.Size = 9
xlApp.activesheet.Range("B5").Activate
xlApp.activesheet.Range("B5").horizontalAlignment = xlCenter
xlApp.activesheet.Range("B5").VerticalAlignment = xlCenter
Next i
'End of Format

End With

Kindly help me to have correct vba code to format Table Column Headings to -
Horizontal Alignment=center
vertical Alignment=center
fill the cell with blue color.

Thanking you.
Ismail

 

[CJ_London]

presumably you have not loaded the excel library - if so then xlcenter and xlhorizontal will be undefined, so you need to supply its value

For the future either temporarily load the excel library or go into excel itself - then in the vba immeditate window type ?xlcenter to display the value, or view the object browser (F2) to find the value there.

You can then remove the library and declare a constant based on the value - in this case

Const xlCentre =-4108
Const xlHorizontal = -4128

 

[ismailr]

Thanks CJ,
I added excel liab through preference and it work perfectly.
Now i added below code to further format the excel data

xlApp.activesheet.Pattern = xlSolid
xlApp.activesheet.PatternColorIndex = xlAutomatic
xlApp.activesheet.ThemeColor = xlThemeColorAccent5
xlApp.activesheet.TintAndShade = 0.399945066682943
xlApp.activesheet.PatternTintAndShade = 0

It is giving the below error.
Run time error 438 Object doesn’t support this property or method
Please help me to fix the same.
thanking you in anticipation.
Ismail

 

[CJ_London]

you should really start a new thread since this is a different question.

regret I can't help - suggest google 'excel PatternTintAndShade' and see what other links are out there