Subject Re: ExportData.wfm problem From Pieter van Heerden <psvh@mweb.co.za> Date Mon, 14 Jul 2014 05:50:58 -0400 Newsgroups dbase.getting-started Thanks for the advice, see my comments inserted in your text Mervyn Bick Wrote: > On Tue, 08 Jul 2014 20:50:09 +0200, Pieter van Heerden <psvh@mweb.co.za>   > wrote: > > > This is dBase 2.8 on Windows 8. > > > > I have a problem with the exportdata form in that it does everything   > > expected, up to the point where selected fields have to be "filled" with   > > data.  Column headings show, but the contents remain empty. > > > > Removing a column from the list is successful, trying to add a column   > > that shows is unsuccessful, with an error message to that effect.  This   > > is behavior as it should be. > > > > The query from which export is done is: > >       d = new database("SAPWAT") > >       d.active = true > >       q = new query() > >       q.database = d > >       q.sql = "@exportwuasub.sql" > >       q.active = true > >       do ExportData.wfm with q > > > > The number of columns to be exported is 72 and the number of rows about   > > 700.  I have reduced columns to 20, made no difference so there does not   > > seem to be an overload of data. > > > > The end result is that although the query is successful, no export can   > > take place. > > > > Is there an alternative that I could build into my application, or does   > > someone have an answer to my problem. > > I take it you've already checked that your .sql file produces the rowset   > you need.  (Double click in the .sql file in the Navigator to open the   > rowset in a browser for inspection.) This has been checked and the sql query produces what is expected > > If your .sql file is producing a rowset then the problem lies with the   > interaction between dBASE 2.8 and Windows 8.  (You should be using Windows   > 8.1 anyway as apparently Microsoft no longer supports Windows 8 with   > security updates.)  Exportdata.wfm works perfectly with joined tables in a   > .sql file under Windows XP Pro, SP3. It is Windows 8.1, sorry for the misnoker.  I also have had experience with Exportdata.wfm working well with XP SP3 and also withWindows 7.  I suspect some incompatibility between 8.1 and the Exportdata.wfm. > > Problems opening the OLE Autoclient object also suggest the problem is   > with the interaction between dBASE 2.8 and Windows 8.  If this happens it   > would be better to export as a .csv file rather than as a .xls file.  But   > first, of course, you've got to get data into that rowset. <g> > > If your .sql file is producing a rowset you may need to DIY to create a   > .csv file.  In this event it will probably be easier to select the   > required fields in the .sql file.  That way your code needn't check for   > required fields and will, therefore, be simpler and you can simply export   > the lot. Creating a CSV file is easy and works fine, problem is that I also need column headings, therefore the preference for Excel. > > If your .sql file is producing a rowset you could also try creating a   > similar .sql file that only creates a rowset with a few fields and rows.     > If this works it points to exportdata.wfm not being able to handle large   > rowsets.   Once again, DIY beckons. <g> The problem appears irrespective of rowset size. > > Mervyn. > I went ahead and with the help of the dBase Book and Gary White on Knowledgbase built an export program, which, surprisingly enough, actually works.  However, it takes a long time to export large rowsets, but Gary mentioned an approach to speed up things.  I'll try that next and see what happens. Is there any way of getting past the warning that Excel gives the user when opening the resultant spreadsheet?  It is to the effect that the spreadsheet is of older version than indicated by the name and asks the user if it is safe to open the file. The resultant program is: // This program exports data to an Excel worksheet. // Excel 2010 warns and asks if it is save to open - Respond with "Yes" // Based on an example in the dBase Book by Ken Mayer // Placing of column heaadings based on Gary White in dBase Knowldgebase advanced // Adapted and tested by Pieter van Heerden // 14 July 2014 // A query is defined in a calling program, made active and then // this program is called with "Do ExportToExcel with <queryname>" // Get query name as parameter passed from calling program parameters q // instantiate Excel object oExcel = new oleAutoclient("excel.application") // create new workbook oWorkBook = oExcel.workbooks.add() // Loop through the rows of the table: nRows = 1 // First, let's put in column headings for i = 1 to q.rowset.fields.size    // select a cell    oExcel.ActiveSheet.cells( nRows, i ).select()    with ( oExcel.ActiveCell )       // set the width to match the field length       ColumnWidth := q.rowset.fields[i].length       // set the content to the field name       formula := q.rowset.fields[i].fieldName    endwith endfor // Follow this by exporting the data do while not q.rowset.endOfSet    nRows++ // increment row counter    // Loop through the fields (columns) of the table,    // but not the image field which is the last one:    for nCols = 1 to q.rowset.fields.size - 1       // get the value:       xValue = q.rowset.fields[ nCols ].value       // get the new cell to add data to:       oCell = oExcel.ActiveSheet.cells( nRows, nCols )       // assign value to the cell:       oCell.formula = xValue       // from Gary White’s Xldef.h       // set the vertical alignment to the top:       oCell.verticalAlignment := -4160    next // field    // Next row:    q.rowset.next() enddo // size the columns to fit oExcel.ActiveSheet.Columns.AutoFit() // save to disk oWorkBook.SaveAs(putfile("Save data as Excel file","","",false,"*.xls")) // close Excel oExcel.quit() // cleanup: oExcel = null