Excel automation

[jrajesh]

I hope:
1. I'm posting in the right forum.
2. this is not a repeat.

The following method can be used to format an Excel output:
Copy the output file to Excel. Open the Excel file and record as many macros you want in Excel to do the required formatting.
Create a table (xlStuff.dbf) with a memo field in your app.
Copy and paste the module containing all your macros from the Visual Basic editor into the memo field.

In this case, I've created two macros ('FormatExcelData' and 'DotAndDash').
Assuming your output dbf file or cursor is called 'RptFile'.

Select Rptfile
* Copy the dbf in Excel format
Copy To c:\reports\xlOutput Xl5
oexcel=Createobject("Excel.application")
oexcel.workbooks.Open('C:\reports\xlOutput')
With oexcel
   * Create a text file with .bas extension from the memo. 
   basfile = "C:\FormatExcel.bas"
   SELECT xlStuff
   GO top
   =STRTOFILE(xlStuff.memoFld,basfile)
   * Add the .bas module to the Excel file.
   .Modules.Add.InsertFile(basfile)
   * Begin the formatting by running the macros from within Excel
   .Run('FormatExcelData')
   .Run('DotAndDash')
   * Formatting completed.
   
   * Delete the macro from the Excel file.
   oexcel.Modules.Delete
endwith
* Delete the text file.
Erase "C:\FormatExcel.BAS"

This way, we do not need to recompile the VFP app. in case of any formatting changes. All we need to do is, record the macro with appropriate changes and overwrite the memo field with the new module or overwrite select macros in the memo field.

Cheers,
Rajesh