formulas do not export with spread

Posted by: flyrod1 on 8 September 2017, 1:48 pm EST

  • Posted 8 September 2017, 1:48 pm EST

    <FONT face=Helv size=2><FONT face=Helv size=2>
    <P dir=ltr>I have a spread using spread 7 that I am exporting to an excel formatted file. In the spread I have 2 columns (g and h)  that are defined as date. I have another column that is defined as a number with the formula  sum(g#-h#)  which gives me the result of the number of days difference between the to columns defined as date. The formula works perfect in the spread. However when I export the spread to Excel, the formulas do not export. I have listed the code that I use to export the spread at the bottom. </P>
    <P dir=ltr>Am I doing something wrong code wise, or is this a problem with spread 7?</P>
    <P dir=ltr> </P>
    <P dir=ltr><FONT face=Helv size=2><FONT face=Helv size=2> </P>
    <P dir=ltr>Code used to export the spread to excel formatted file:</P>
    <P dir=ltr> </P>
    <P dir=ltr>Public Sub ExportFileToExcel(objfpspread1 As fpSpread)</P>
    <P dir=ltr>objfpspread1.ExportExcelBook strfilename & ".XLS"</P>
    <P dir=ltr>Dim objExcel As Excel.Application</P>
    <P dir=ltr>Dim objBook As Excel.Workbook</P>
    <P dir=ltr>Dim objsheet As Excel.Worksheet</P>
    <P dir=ltr>Dim objrange As Excel.Range</P>
    <P dir=ltr>Set objExcel = New Excel.Application</P>
    <P dir=ltr>Set objBook = objExcel.Workbooks.Open(strfilename & ".XLS")</P>
    <P dir=ltr>Set objsheet = objBook.Sheets(1)</P>
    <P dir=ltr>objBook.Unprotect</P>
    <P dir=ltr>objsheet.Unprotect</P>
    <P dir=ltr>objsheet.Range("1:1").Insert</P>
    <P dir=ltr>objfpspread1.Col = 1</P>
    <P dir=ltr>objfpspread1.Row = 1</P>
    <P dir=ltr>Dim x As Integer</P>
    <P dir=ltr>For x = 1 To objfpspread1.MaxCols</P>
    <P dir=ltr>objfpspread1.Col = x</P>
    <P dir=ltr>objsheet.Range(Chr(64 + x) & "1").Value = objfpspread1.Text</P>
    <P dir=ltr>Next</P>
    <P dir=ltr>objsheet.Range("A1:" & Chr( _</P>
    <P dir=ltr>64 + objfpspread1.MaxCols) & objfpspread1.MaxRows + 1).Locked = False</P>
    <P dir=ltr>objsheet.Protect Contents:=False, UserInterfaceOnly:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True</P>
    <P dir=ltr>objBook.Save</P>
    <P dir=ltr>Set objsheet = Nothing</P>
    <P dir=ltr>objBook.Close</P>
    <P dir=ltr>Set objBook = Nothing</P>
    <P dir=ltr>objExcel.Quit</P>
    <P dir=ltr>Set objExcel = Nothing</P>
    <P dir=ltr> </P>
    <P dir=ltr> </P>
    <P dir=ltr>Any help is greatly appreciated.</P></FONT></FONT></FONT></FONT>
  • Replied 8 September 2017, 1:48 pm EST

    <P>Hello Flyrod,</P>
    <P>Spread exposes SaveExcel methods which provide various ways to save data to an Excel file.</P>
    <P>These methods save the contents of the sheets of the FarPoint Spread component to an Excel-compatible BIFF8 (Binary Interchange File Format, version 8 from Microsoft) file. This is the file format for several versions of Microsoft Excel (Excel 97, Excel 2000, and Excel XP). </P>
    <P>You may refer the help file to know the various overloaded SaveExcel methods along with supported ExcelSaveFlags options. </P>
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels