Error appending from new MS Excel 2007 1

[dbMark]

We have some users who we believe have caused append errors submitting Excel 2007 spreadsheets created in Office 2007. The error we got was during an APPEND FROM {filename} TYPE XLS - Error 1661 - Microsoft Excel file format is invalid.

If VFP 9.0 sp1 cannot import Excel spreadsheets created from Excel 2007 or Office 2007, has this been reported? This could break a lot of existing code...

 

[Mike Lewis]

Tamar,

You're using XL5 for the "Excel 95" file and XL8 for the "Excel 97" file, right?

Yes, that's correct.

I reported this as a bug to the VFP team. But, as you say, it's really the Excel team that need to do something about it.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[dbMark]

Mike and Tamar, Thanks for your help since I did not have access to Excel 2007. Personally, I feel it would certainly be difficult (read: look like a fool) to tell clients to save their spreadsheets into a 12 year old format in order to submit. A 2003 format, fine. A 1995 pre-Y2K format, not good enough!

It looks like this is more than a gentle nudge away from using APPEND or IMPORT and into using object-based solutions.

ilyad, You're right. There are long threads over at UT on this subject and even some alternative sample code using ODBC or OLEDB. Looks like Jet 4.0 has promise.

 

[Mike Lewis]

Mark,

I feel it would certainly be difficult (read: look like a fool) to tell clients to save their spreadsheets into a 12 year old format in order to submit.

As it happens, I was speaking to a client this afternoon who will have this problem. His (tentative) solution is to ask them to send the data in CSV format.

Not very satisfactory, is it?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Imaginecorp]

dbMark, certainly opened a major can of worms,

After warming the programmer’s behind, the one who assured me, we would not have a problem, am working on this myself.

Have been looking at opening the excel app behind the scenes (Visible = .f.), converting the file to CSV format, (Save as) and then appending.

One major problem I have found is if the Cells are formatted to Currency with a Separator, only the leading digits are brought in. i.e. 1,000.00 = 1.00. (Though only been working on this in the last hour)

The only advantage I have is Truckers are cheap and none of our clients have indicated an interest in moving up to 2007

Will keep you guys posted

 

[dbMark]

We just started getting COM+ crashes on our web servers that called VFP COM+ applications which with our workaround code. We had decided, since there were so few Excel 2007 users at this point, to attempt to use the older APPEND FROM first and then if that failed, trap it and then use ADO to load in the spreadsheet. Well, starting yesterday, the apps started crashing, when the APPEND FROM failed, rather than trapping the error and using the ADO code instead, it just crashed, since MS subsystems intercepted something it didn't like.

The only thing that might have happened is maybe Microsoft released a security patch? Just a guess. The apps sure didn't change.

Well, after a lot of fiddling, we decided to reverse the processing order, try ADO first then use APPEND FROM ... TYPE XLS if necessary.

Popups and Event Viewer mention "Data Execution Prevention" from COM Surrogate.

 

[dbMark]

FYI: The specific new error I noted as encountered and resolved on May 4th was this message on the web page:

Microsoft VBScript runtime error '800a01ce'

The remote server machine does not exist or is unavailable

/ASPfolder/ASPpage.asp, line 50

Still haven't proven whether it was caused by an April 2007 MS security patch. Just another one of life's mysteries...

 

[dbMark]

Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 File Formats

Brief Description:
Open, edit, and save documents, workbooks, and presentations in the file formats new to Microsoft Office Word, Excel, and PowerPoint 2007.

Filename: FileFormatConverters.exe
Release: 18 June 2007
Download Size: 27.5 MB

Required applications:
2000 versions (sp3)*
2002 versions (sp3)*
2003 versions (sp1)*
* Note: be sure to first install all high-priority and required updates.

How to use earlier versions of Excel, PowerPoint, and Word to open and save files from 2007 Office programs:

http://support.microsoft.com/kb/924074

While this may not help this VFP APPEND/IMPORT FROM issue (unless it updates some common DLLs that VFP uses) I thought it might be helpful for you to know about this new conversion update.

 

[Mike Lewis]

dbMark,

Thanks for posting that. It will be useful to Office users, but I doubt if it will solve the problem in VFP. It would be OK if Office 2007 users saved their files in the old format (so-called compatibility mode), but whether or not they do that is outside our control.

Still, you might be right about the DLLs. It would be worth experimenting.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk