SPREAD 8 ISSUES EXPORTING TO EXCEL 365

Posted by: abrahampetit on 27 January 2020, 5:53 am EST

  • Posted 27 January 2020, 5:53 am EST

    Hi,

    I'm using Excel 365 version 1912. On this version and on previous ones I have noticed a bad behavior when exporting a FP Spread8 grid, specifically using the method .SaveExcel2007File(). For example I set calibri 9 (Could be Arial too) to my spread, but when exporting excel shows calibri 14, and not only that, Excel shows the data locked and I cannot change font size or anything, rows and cols in excel are locked too and I cannot set even its col with.

    This situation does not happen with the method .ExportToExcel(), which behaves fine, but this one does not export formatting.

    Is there some property Im missing?


    This is my VB6 code:


    Function SpreadExportaAExcel(ByRef VieneSpread As fpSpread, ByRef FileName As String) As Boolean


    SpreadExportaAExcel = False


    If MsgBox("Do you want to export to Excel?", vbQuestion + vbYesNoCancel) = vbYes Then

    On Error Resume Next

    FrmAyuda.CmDialog1.CancelError = True
    FrmAyuda.CmDialog1.InitDir = ElEstudio.Dir_Personal
    FrmAyuda.CmDialog1.FilterIndex = 1
    FrmAyuda.CmDialog1.Filter = Msg_Res(1146) & " (*.xls)|*.xls|" & Msg_Res(1147) & " (*.xlsx)|*.xlsx"
    FrmAyuda.CmDialog1.Flags = &H4&
    FrmAyuda.CmDialog1.Flags = &H2&
    FrmAyuda.CmDialog1.FileName = FileName

    FrmAyuda.CmDialog1.DialogTitle = "Export to Excel"
    FrmAyuda.CmDialog1.ShowSave

    Dim GuardoExcel As Boolean
    Dim ExtensionDeExcel As String

    GuardoExcel = False
    ExtensionDeExcel = UCase$(right$(FrmAyuda.CmDialog1.FileName, 3))

    Select Case ExtensionDeExcel
    Case "XLS"
    GuardoExcel = VieneSpread.ExportToExcel(FrmAyuda.CmDialog1.FileName, ElTiempo(tiempo_Sel).Dsc, ElEstudio.Dir_Tmp & "LOGFILE.TXT")
    Case "LSX"
    GuardoExcel = VieneSpread.SaveExcel2007File(FrmAyuda.CmDialog1.FileName, vbNullString, None, ElEstudio.Dir_Tmp & "LOGFILE.TXT")
    Case Else ' 10 04 2015 ya
    Exit Function
    End Select

    If GuardoExcel = True Then
    Call Utilitario_EjecutaShellDeArchivo(FrmAyuda.CmDialog1.FileName)
    SpreadExportaAExcel = True
    Else
    Call MsgBox("Error", vbExclamation)
    End If

    End If


    End Function



  • Replied 30 January 2020, 3:09 pm EST

    Hi,

    Thank you for sharing the details. I could partially replicate the issue at my end. I could observe the locking issue in the exported excel file. However, the font size remains same as in the Spread. Please find the exported excel file and the sample application attached.

    Can you please share a small stripped down application demonstrating the font related problem at your end along with the exported excel file? This would help me in investigating further on the issue.

    ~Pragati
  • Marked as Answer

    Replied 30 January 2020, 5:26 pm EST

    Files attached.
    Sample.zip

    ExcelFile.zip
  • Replied 1 March 2022, 9:16 pm EST

    Symptoms
    Access encounters Import/Export errors when working with Excel *.xls files that have cells, which contain more than 8224 bytes of data.

    Cause
    Changes that were released beginning with Access 2010 Service Pack 1 prevent Access from opening an existing Excel *.xls file if the contents of a cell are greater than 8224 bytes.

    Resolution
    You may use one of the following methods to prevent the issue.

    -Use the *.xlsx format instead
    -Restrict any memo/long text fields to a length less than 8224 bytes
    -When exporting, delete the existing .xls file before performing the export using the same file name
    -Open the .xls file in Excel before performing the Import/Export from Access

    Greeting,
    Rachel Gomez
Need extra support?

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

Learn More

Forum Channels