Excel formatting

[chendersonCRKPU]

I'd like to tidy up an excel worksheet using the API.

So far I've only used AutoFit on the column widths, but I'd like to center align certain columns, apply a border around the full range of used cells, bold the headings and apply a filter to the first row.

Can someone point me to where I can find the API for these things? Or a similar example

This is what I've got so far:

excelApp = CreateObject("Excel.Application")
excelApp.Visible = True
excelApp.DisplayAlerts = False

wb = excelApp.Workbooks.Open(oPATH + "BOM-PartsOnly.xlsx")
xlws = wb.Worksheets(1)

excelApp.Columns.AutoFit
excelApp = Nothing 

[bradeneuropeArthur]

Use excel macros to read what the coding is for your options. I mean record your macro in excel and copy this to your code.

You understand?

[MechMachineMan]

Yo check the links in my signature. MSDN is great.

[chendersonCRKPU]

Thanks for your suggestions. I've tried both the record macro and the resources in your links. Both produced something which looks like it should work but I keep receiving this error on the red line below:

Unable to set the HorizontalAlignment property of the Range class

wb = excelApp.Workbooks.Open(oPATH + "BOM-PartsOnly.xlsx")
xlws = wb.Worksheets(1)

Call ReorderXLBOM()

excelApp.Columns.AutoFit
excelApp.Rows("1:1").Select
excelApp.Selection.Font.Bold = True
excelApp.Selection.AutoFilter

xlws.Columns("A:A").HorizontalAlignment = xlCenter

I have also tried this (as per recorded excel macro) instead of that line but get the same error:

excelApp.Columns("A:A").Select
With excelApp.Selection
	.HorizontalAlignment = xlCenter
End With

I'm running this from iLogic environment directly after using BOMView.Export

[MechMachineMan]

Google: "horizontalalignment enum" 

Why? You are setting a property using text - ergo, using an enum.

What the results of google returns:

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlhalign-enumeration-excel

What this means for your code:

xlws.Columns("A:A").HorizontalAlignment = xlHAlignCenter

OR

xlws.Columns("A:A").HorizontalAlignment = xlHAlign.xlHAlignCenter

OR

xlws.Columns("A:A").HorizontalAlignment = -4108

If none of those lines work, either:

1. I found the wrong enum

2. Some spelling is wrong in the line

3. One of the members is not valid for the object it's called on, or

4. The Columns range is not a valid object to run the horizontalalignment command on. 

[chendersonCRKPU · Accepted solution]

Imports Microsoft.Office.Interop.Excel.Constants 

 Adding this statement solved it.

I only had

Imports Microsoft.Office.Interop.Excel