Subject Re: Copying a file opened in a form
From Tim Ward <tim@goldengrovenurserydotcodotuk>
Date Mon, 12 Jun 2023 12:48:14 +0100
Newsgroups dbase.getting-started

On 11/06/2023 15:42, Milind Nighojkar wrote:
> In a form dabase   is opened in grid mode with certain fields and with a filter condition . Field headings are changed. Is there any way to export this data set excel ?
 Hi Milind,

I addition to Akshat's response, it is possible to create an Excel file
directly if that is what you require. Excel needs to be installed on the
computer running the code though.
An extract of code I use is below. It deals with the creation of an
Excel sheet, populating some cells and formatting before closing the file.

regards Tim


** Open Excel and set up sheet and headers

oExcel = new oleAutoclient("Excel.Application")
oExcel.Workbooks.Add()
oCell = oExcel.ActiveSheet.cells( 1, 1 )
oCell.formula = "Item"
oCell = oExcel.ActiveSheet.cells( 1 , 2 )
oCell.formula = "Stock Code"
oCell = oExcel.ActiveSheet.cells( 1 , 3 )
oCell.formula = "Description"
oCell = oExcel.ActiveSheet.cells( 1 , 4 )

** Create a loop

form.labels1.rowset.first()
nNumber = 1
Do while not form.labels1.rowset.endOfSet

        oCell = oExcel.ActiveSheet.cells( nNumber+1, 1 )
        oCell.formula = nNumber
        oCell = oExcel.ActiveSheet.cells( nNumber+1, 2 )
        oCell.formula = trim(form.labels1.rowset.fields["Stock_code"].value)
        oCell = oExcel.ActiveSheet.cells( nNumber+1, 3 )
        oCell.formula = trim(form.labels1.rowset.fields["Description"].value)
        oCell = oExcel.ActiveSheet.cells( nNumber+1, 4 )
        nNumber = nNumber+1
         form.labels1.rowset.next()
Enddo
                
** Formatting of Excel and closing

       oExcel.Rows("1:1").Select()
       oExcel.Selection.Font.Underline = True
       oExcel.Selection.Font.Bold = True
       oExcel.Columns("B:B").Select()
       oExcel.Selection.ColumnWidth = 15
       oExcel.Columns("C:E").Select()
       oExcel.Selection.ColumnWidth = 40
       oExcel.Columns("F:F").Select()
       oExcel.Selection.ColumnWidth = 10
       oExcel.Columns("G:G").Select()
       oExcel.Selection.ColumnWidth = 20
       oExcel.Selection.NumberFormat = "0"
       oExcel.Columns("H:I").Select()
       oExcel.Selection.ColumnWidth = 10
       oExcel.Selection.NumberFormat = "£#,##0.00"
       oExcel.Columns("J:J").Select()
       oExcel.Selection.ColumnWidth = 10
       oExcel.Selection.NumberFormat = "#,##0.00"
       oExcel.Columns("K:K").Select()
       oExcel.Selection.ColumnWidth = 30
       oExcel.Columns("A:K").Select()
       oExcel.ActiveSheet.PageSetup.PrintArea = "$A:$K"
       oExcel.ActiveSheet.PageSetup.FitToPagesWide = 1
       oExcel.ActiveSheet.PageSetup.FitToPagesTall = 20
       oExcel.Range("A1:A1").Select()
       oExcel.ActiveWorkbook.SaveAs( cXLS ) // a variable with the path
and filename to save
       oExcel.quit()