Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

1 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

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.