Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

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

share|improve this answer
    
Thanks a lot, jdh. I didn't think about explicitly including the Excel library. The included libraries are now: VBA, MS-Word 12.0, OLE Automation, MS-Office 12.0, MS-Excel 12.0. I tried then to implement point 3) of your suggestion but I'm told now: "Runtime Error 438 - Object doesn't support this attribute or method" (translation from German). Any idea? – Marcel Mar 23 '13 at 7:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .