>I am putting in code to reformat an Excel file using VFP 8 (I am attempting to sort the spreadsheet and add subtotals). I recorded a macro in Excel and got some code (which you will see below), but I cannot figure out how to translate it into something VFP will understand. I believe that the underscores are line continuation characters and I think I need to put the code in a "function" format. I have also defined "True" and "False" properly. I am sure that the "Array(5, 6)" line will not work but I have no idea what to put in its' place. My attempted translation is below, but of course, does not work. Any help will be greatly appreciated!
>
>---------------------
>Code from the macro:
>---------------------
>
> Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
> , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
> False, Orientation:=xlTopToBottom
>
> Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 6), _
> Replace:=True, PageBreaks:=False, SummaryBelowData:=True
>
>-----------------------------
>my attempt at a translation:
>-----------------------------
>
> .Selection.Sort(.Range("A2"), xlAscending, .Range("B2"), xlAscending, xlGuess, 1, False, xlTopToBottom)
>
> .Selection.Subtotal(1, xlSum, Array(5, 6), True, False, True)
>
>
>Thanks for your help!


Larry,
Message #608856 has explanation of how would you translate an Excel macro (and to your chance about subtotals:) and Message #544569 has few more links.

"I believe that the underscores are line continuation characters and I think I need to put the code in a "function" format. I have also defined "True" and "False" properly. I am sure that the "Array(5, 6)" line will not work but I have no idea what to put in its' place."

You're right underscores are continuation characters and you need to put it in 'function' format or namely use positional arguments. True and False simply translated as either .T./.F. or 1/0 (any positive number would do for .T.)
Array(5,6) doesn't work as is, right. However as name applies it's an array. Just initialize your array as usual and pass by reference.

And now code talks best :)

Select Order_id, Cust_ID, Emp_ID, ;
  Order_Date, Order_amt, Order_dsc/100 As Order_dsc,Freight ;
  from orders ;
  into Cursor crsTemp

lcXLSFile = Sys(5)+Curdir()+'myOrders.xls'
Copy To (lcXLSFile) Type Xls


#include "xlConstants.h"
Dimension laArray[3]
laArray[1] = 5
laArray[2] = 7
laArray[3] = Fcount()+1 && Will use later

Use In 'crsTemp'
Use In 'Orders'

oExcel = Createobject("excel.application")
With oExcel
  .Workbooks.Open(lcXLSFile)
  With .ActiveWorkbook.ActiveSheet
    lnRows = .UsedRange.Rows.Count && Get current row count
    lcFirstUnusedColumn = _GetChar(laArray[3]) && Get column in Excel A1 notation
    * Instead of orders order_net field use Excel calculation for net prices
    .Range(lcFirstUnusedColumn+'2:'+;
      lcFirstUnusedColumn+Transform(lnRows)).FormulaR1C1 = ;
      "=RC[-3]*(1-RC[-2])+RC[-1]"
    .Range(lcFirstUnusedColumn+'1').Value = 'Order Net' && Place header
    .Range('E:'+lcFirstUnusedColumn).NumberFormat = "$#,##0.0000" && Format columns
    .Range('F:F').NumberFormat = "0%"

    * Sort : Cust_Id asc, Emp_ID asc, Order_Amt descending
    .UsedRange.Sort(.Range('B2'),,.Range('C2'),,,;
      .Range('E2'),xlDescending,xlGuess,,.F.,xlTopToBottom)

    * Subtotal grouping by customer
    .UsedRange.Subtotal(2, xlSum, @laArray, 1, 0, 1)
    .UsedRange.Columns.Autofit && Autofit columns
  Endwith
  .Visible = .T.
Endwith

* Return A, AA, BC etc noation for nth column
Function _GetChar
  Lparameters tnColumn && Convert tnvalue to Excel alpha notation
  If tnColumn = 0
    Return ""
  Endif
  If tnColumn <= 26
    Return Chr(Asc("A")-1+tnColumn)
  Else
    Return _GetChar(Int(Iif(tnColumn % 26 = 0,tnColumn - 1, ;
      tnColumn) / 26)) + _GetChar((tnColumn-1)%26+1)
  Endif

PS: Some of code portions are longer than what that might be. Just for sampling.
Not using selection is my preference. Almost all Excel macros use 'Selection'. If you'd too don't forget to 'Select' first and 'Selection' is member of Application object, add it :
.Application.Selection...
(Selection and Range are both range objects)
Cetin