Office 2007: How to sort an Excel Table from within a Word macro

Question

I have written a Macro in WinWord2007 which lists styles and their properties in an Excel table. The table is made accessible by the following 3 lines:

Dim Oxl_Sheet As Object
Set Oxl_Sheet = CreateObject("Excel.Sheet")
Oxl_Sheet.Application.Visible = True

I can without problems write into individual cells of the table, for example:

Oxl_Sheet.Application.Cells(iRow, 2).Value = ActiveDocument.Styles.(iStyle).NameLocal

I would now like to sort the completed table from within the same Word macro, but I have found myself utterly unable to do so. In the following line, for example:

Oxl_Sheet.Application.Sort.SortFields.Add Key:=Range("I2:I356"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

the compiler complains that the Sub/Function "Range" has not been defined.

Can anybody give me a hint?

Answer 1

A few changes should get you running:

1) If you want to maintain the Excel sheet beyond the macro's run, move the line:

Dim Oxl_Sheet As Object

to the top of the macro page, outside of any function.

2) To fix the compile error, include a reference to the Excel Library to define the range function:

• In the VBA editor, 'Tools' -> 'References...'
• Select 'Microsoft Excel <12.0 or 14.0> Object Library

3) Change the syntax of the sort line to qualify which object the range refers to:

Oxl_Sheet.Application.Sort.SortFields.Add Key:=Oxl_Sheet.Application.Range("I2:I356"), SortOn:=xlSortOnValues