************************************************ FUNCTION CreateExcel && creates nicely formatted excel based on current cursor ************************************************ *** parts courtesy a great guy named Alex Feldstein ******************************************************* LPARAMETERS parDefaultFileName, parSheetTitle, parFirstTitle, parSecTitle LOCAL oxl as "Excel.Application" LOCAL xlsFileName *** let's get the file name *** xlsFileName = GETFILE( "Excel Files:XLS" ) IF EMPTY( xlsFileName ) xlsFileName = parDefaultFileName ENDIF WAIT WINDOW "Moving to Excel, please wait" nowait COPY TO (xlsFileName) TYPE xls oxl = CreateObject("Excel.Application") if vartype(oxl) != "O" * could not instantiate Excel object return .F. endif * make excel visible during development *oExcel.visible = .T. * open the workbook oxl.SheetsInNewWorkBook = 1 oWorkbook = oxl.Workbooks.Open(xlsFileName) * rename the Sheet oActiveSheet = oxl.ActiveSheet oActiveSheet.Name = parSheetTitle oExcelApp = oxl.Application oExcelApp.WindowState = -4137 && maximized * add a row to insert some stuff oxl.Range("A1").Select oxl.Selection.Insert( 3 ) oxl.Cells(1,1).Value = parFirstTitle oxl.Cells(1,5).Value = parSecTitle * find address of last occupied cell lcLastCell = oxl.ActiveCell.SpecialCells(11).Address() * resize all columns lnMarker1 = at("$",lcLastCell,1) lnMarker2 = at("$",lcLastCell,2) lnStartPos = lnMarker1 + 1 lnStrLen = lnMarker2 - lnStartPos oxl.Columns("A:" + substr(lcLastCell,lnStartPos,lnStrLen)).EntireColumn.AutoFit * add a nice autoformat oxl.Range("A2:" + lcLastCell).Select oxl.Selection.AutoFormat(2,.t.,.t.,.t.,.t.,.t.,.t.) * set Excel Print Area oActiveSheet.PageSetup.PrintArea = "$A$1:" + lcLastCell * define printed page footer With oActiveSheet.PageSetup *.LeftHeader = "" *.CenterHeader = "" *.RightHeader = "" .LeftFooter = "&Producido por Carlos Torres&B" .CenterFooter = "&D" .RightFooter = "Pagina &P" *.PrintHeadings = .F. .PrintGridlines = .F. .CenterHorizontally = .T. .CenterVertically = .F. .Orientation = 2 && landscape o 1 para portrait endwith * save Excel oWorkbook.Save() * display finished product oxl.visible = .T. RELEASE oxl RETURN