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...

 

[TamarGranor]

It's not a bug. The default file formats in Office 2007 are different from earlier versions (zipped XML). Not surprisingly, VFP 9, which predates those versions, can read it directly.

Elsewhere, Aleksey Tsingauz of Microsoft has made it clear that VFP will not be updated to read these version with APPEND FROM or IMPORT. Instead, use Automation or OLE DB to read the files, or tell users to store in the earlier formats. (You can set the older format as the default.)

Tamar

 

[Dan Freeman]

>Not surprisingly, VFP 9, which predates those versions, can read it directly.

can NOT read it directly

;-)

 

[Mike Lewis]

Tamar,

The default file formats in Office 2007 are different from earlier versions (zipped XML). Not surprisingly, VFP 9, which predates those versions, can read it directly.

True, but it appears that VFP cannot even read files saved in Excel 2007 in compatibility mode. I just tried a quick test of that, and saw the same error that Mark referred to.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[TamarGranor]

Oh, one more thing. Don't use TYPE XLS--that's Excel 2 format. Try TYPE XL5 or TYPE XL8.

Tamar

 

[Mike Lewis]

Tamar,

Don't use TYPE XLS--that's Excel 2 format. Try TYPE XL5 or TYPE XL8.

It doesn't make any difference. The point is that VFP does not recognise files saved by Excel 2007, even in compatibility mode. Using TYPE XL8 still gives rise to an "invalid format" error.

It looks like Mark was right when he said it would break existing code.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Imaginecorp]

Here is another way: We ran across this problem too and this is a simplistic way. We do a lot more...

select customer
COPY TO c:\imaginecorp\customer.xls TYPE XL5
*creates it as Excel 2007 if Excel 2007 is the only version *on the computer otherwise saves it as 2003. You have to *resave once open in the new format when prompted

*Rather than an append, use a 2 step:
IMPORT FROM c:\imaginecorp\customer1.xls TYPE XL8 &&& Temp file.
*Then:
select customer
append from customer1

 

[Imaginecorp]

Sorry:
COPY TO c:\imaginecorp\customer1.xls TYPE XL5

 

[Mike Lewis]

Imaginecorp,

Actually, I'm finding the COPY TO works properly. When I export to Excel and open the exported workbook in 2007, it opens it in compatibility mode, which is what you would expect.

But the APPEND FROM and IMPORT definitely look to be broken. I have reported it to Microsoft.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Imaginecorp]

Sorry:
Disregard, does not work if document is Originally created in 2007.
It works for us as we do a copy to xls for our reports (some clients need them for further massaging) then if needed reconvert them back to a dbf.

 

[Dan Freeman]

Mike, I'm not familiar with this "compatibility mode". What is it?

I've seen many reports that SAVE AS, changing the file format to Excel 2003, works just fine. You're the only person I've seen refer to a "compatibility mode".

 

[Mike Lewis]

Dan,

In Office 2007, compatibility mode simply means that you are saving the file in "Office 97 - 2003" format, rather than the new compressed XML format.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[TamarGranor]

Mike - I don't have Office 2007 installed (and have no great desire to install it), so I'm working blind here. But have you actually looked at the files Excel is saving to see whether they are really old format. It's really easy to identify any of the XML formats (at least based on what I've seen with other apps) because you can open them with WinZip.

Tamar

 

[ilyad]

Mike,

Did you try with automation
loExcel.ActiveWorkbook.saveas(m.tcDbfFile, 11) ?

 

[Dan Freeman]

Mike,

The reason I asked is I suspect we're comparing apples with tangelos here.

Can you confirm that "compatibility mode" is the same as "Save As"?

Like Tamar, I don't have Office 2007 installed anywhere and I'm not likely to have it installed anywhere for the next several years. My office only recently upgraded to Office 2003, and at that there was much grumbling.

You're saying that SAVE AS doesn't work? I just want to know what to tell folks, and what you're telling me doesn't match what I'm hearing elsewhere.

 

[Mike Lewis]

Tamar and Dan,

Yes, I've looked at these files closely. I feel sure that what I'm saying is correct.

I created a Workbook in 2007, and saved it using "Save As / Excel 97 - 2003 Workbook". (At that point, the window title changes to Compatibility Mode).

I also tool a workbook created in Excel 2000, opened it in 2007, and saved it. The window title still says Compatibility Mode.

I also saved the workbooks as normal 2007 files (xlsx files). To confirm that the xls files are different from the xlsx versions, I noted that the xlsx files are about half the size of the xls, which is what I would expect.

I was able to open both xls files in Excel 2000, which confirms that they are backward compatible. But, in both cases, when I tried to import them into VFP, I got the error mentioned earier in the thread.

Just to eliminate the possibility that I was doing something wrong in VFP, I imported similar files that were created in Excel 2000, with no problem.

Ilyad: You asked if I had tested this using Automation. The answer is No -- because I have no reason to. My concern here is that IMPORT and APPEND FROM won't work with these files.

This might not be a huge problem for most developers (it probably won't be for me), but it's always important to be aware of things that worked before that don't work now.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[dbMark]

Try saving the spreadsheet in an even earlier version such as Excel 95? To me it seems ridiculous to save back that far in the past simply for compatibility, as doing so will likely put even greater restrictions on the data structures or size limits (16,383 rows) that can be preserved in the spreadsheet.

 

[Mike Lewis]

Mark,

Saving it as "Excel 5.0/95 Workbook" worked OK. I was able to import the file into VFP.

The problem is, of course, that you might not have any control over the way the user saves the workbook.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[ilyad]

Interesting. This problem was discussed in UT recently <B>Office 2007 compatibility issues</B> Thread #1191826 Message #1191826

 

[TamarGranor]

Just one last double-click, Mike. You're using XL5 for the "Excel 95" file and XL8 for the "Excel 97" file, right?

If that's not working (which as Dan said, doesn't match what I've heard elsewhere), it sounds like the Excel team broke something.

Tamar