"File error: data may have been lost", Windows Vista and Office 20

[Guest]

I have an application which creates an Excel file from a Crystal Report.

This application works with no problems in the following environments:
1. Windows XP with Office 2000
2. Windows Vista with Office 2000

However, when running Windows Vista and Office 2007 the Excel file gets
generated identically, but when the user opens the file they get a Microsoft
Office Excel application error " File error: data may have been lost."

The same error is received if the file was generated under XP with Office
2000, but opened under Windows Vista with Office 2007.

I have not tested Windows XP with Office 2007.

The contents of the spreadsheet has no formulas, no references to other
spreadsheets (or other sheets), and only one sheet.

Any clues what to look for or remove in the spreadsheet to make it behave
properly?

If the user saves the spreadsheet to a new spreadsheet using Office 2007,
the new spreadsheet can be opened with no errors. The original XLS file is
4,096 bytes, and the new 2007 XLS file is 15,872 bytes.

 

[Guest]

Hi Paul,

I have the same message, although exporting from Crystal Reports 10 into
Excel 2007. I haven't found the answer yet unfortunately.

Regards,

Paul

 

[Guest]

I have a work-around. Basically the idea is to have the Excel application
re-save the spreadsheet (using its own methodology). After Crystal Reports
creates the XLS file I call the procedure below. Excel 2007 enforces the
sheet name size limit of 31 characters; Crystal Reports was using the title
of the report to create the spreadsheet name, which caused some spreadsheets
in our application to exceed this limit.

I'm not sure if this was the only " File error: data may have been lost.",
but I stopped getting the error after this procedure was done. If someone
has a better solution I would still be interested in knowing it.

Hope this helps,
Paul.
-----------------------------------------------------------------------------
Private Sub UpdateExcelFile(ByVal sFileName As String)

Dim xl As Excel.Application
Dim xWorkbook As Variant
Dim sCurDir As String
Dim sTempFilename As String

On Error GoTo Cleanup
sCurDir = CurDir
Set xl = CreateObject("Excel.Application")

If (xl Is Nothing) Then
Exit Sub
End If

xl.DisplayAlerts = False
xl.UserControl = False
xl.Visible = False
Call xl.Workbooks.Open(sFileName)

'make the first sheet active
xl.Workbooks(1).Sheets(1).Activate

If (Len(xl.Workbooks(1).Sheets(1).Name) > 31) Then
xl.Workbooks(1).Sheets(1).Name =
Left(xl.Workbooks(1).Sheets(1).Name, 31)
End If

Err.Clear
sTempFilename = sFileName & "temp.xls"
Call xl.Workbooks(1).SaveAs(sTempFilename, xlWorkbookNormal)
'change directory back to where we started.
Call ChDrive(sCurDir)
Call ChDir(sCurDir)

Cleanup:
On Error Resume Next
If (Not xl Is Nothing) Then
If (CurDir <> sCurDir) Then
'change directory back to where we started.
Call ChDrive(sCurDir)
Call ChDir(sCurDir)
End If

xl.DisplayAlerts = True
xl.UserControl = True

For Each xWorkbook In xl.Workbooks
Call xWorkbook.Close(False)
Set xWorkbook = Nothing
Next xWorkbook
Set xWorkbook = Nothing

Set xWorkbook = Nothing
If (Not xl Is Nothing) Then
xl.Application.Quit
Set xl = Nothing
End If

End If

If (Dir(sTempFilename) <> vbNullString) Then
On Error Resume Next
Call Kill(sFileName)
Call FileCopy(sTempFilename, sFileName)
Call Kill(sTempFilename)
End If

End Sub

 

[jen miller]

Did you ever find a quick fix for this? I have the exact situation with Crystal Reports/Excel and while data doesn't actually seem to be lost- it has my users VERY concerned.
thanks
jennifer