Sorting an excel worsheet from foxpro

[pelwood]

I need to be able to sort data in an excel worksheet from visual foxpro 7. I have created the worksheet inserted the data from various sources, i can select the range of cells to be sorted the problem is the syntax for the sort.
Can anyone please help

 

[baltman]

Did you try recording a macro in Excel and seeing what it does?

Brian

 

[Mike Gagnon]

pelwood

Although baltman's is an excellent one, sometimes the macro answer is a little more difficult to translate into VFP. You can record a macro in Excel to do what you need and just "play" the macro from within VFP. Or automate the whole process. But you have to understand what VFP and Excel need to make it work, and until you do, its pretty much trial and error. For example here is the VB code that I recorded to sort column A (Range A1 to A7) :

Range("A1:A7").Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

And translated into VFP it would look like this (Althought I didn't use all the settings)

oExcel=CREATEOBJECT("Excel.application")
oWorkBook=oExcel.Workbooks.Open("c:\book1.xls")
oSheet=oWorkBook.Sheets("sheet1")
oSheet.Range("A1").Sort(oSheet.Range("A1,A7"))
oExcel.Visible=.t.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[pelwood]

Thanks for the info, I have a three column spreadsheet. the code provided helped me but it did not work , I changed it as follows to get it working. I wanted to sort on column "C" the range will be calculated later when all is working ok.

oSheet.Range("A1:C147&quot.Sort(oSheet.Range("C1&quot)

I have tried but failed to work out how to sort in descending order

 

[Mike Gagnon]

I have tried but failed to work out how to sort in descending order

oSheet.Range("A1:C147&quot.Sort(oSheet.Range("C1&quot,1) && Ascending
oSheet.Range("A1:C147&quot.Sort(oSheet.Range("C1&quot,2) && Descending



Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.