Problem with VBA macro running inside VB

[David Travers]

I have created a VB6 program which uses some Excel automation to generate an
Excel formatted document from a CSV file.

Inside Excel VBA running in Excel 2003, I have the following

Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:=Range("F2") _
, Order2:=xlAscending, Key3:=Range("A2"), Order3:=xlAscending,
Header:=xlYes, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom

however in the VB applciation i have

Dim xl as Excel.Application
Set xl = New Excel.Application

.....

xl.Selection.Sort xl.Range("D2"), xlAscending, xl.Range("F2"), xlAscending,
xl.Range("A2"), xlAscending, xlYes, 1, False, xlTopToBottom

but this doesn't work (get Sort Method of Range class failed)

Anyone got any ideas why not.

It's the only line of Excel code within the program that does not work.

 

[Bob Phillips]

Range is not a property of the Excel application, but of a worksheet. You
will need to step all the way down the object model

xl.workbook_object.worksheet_object.Range("D2")

for example, or better setup object variables for each part and build an
xlRange1 object that you can use, etc.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)