×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Error appending from new MS Excel 2007

Error appending from new MS Excel 2007

Error appending from new MS Excel 2007

(OP)
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...

RE: Error appending from new MS Excel 2007

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

RE: Error appending from new MS Excel 2007

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

can NOT read it directly

winky smile

RE: Error appending from new MS Excel 2007

Tamar,

Quote:

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

RE: Error appending from new MS Excel 2007

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

Tamar

RE: Error appending from new MS Excel 2007

Tamar,

Quote:

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

RE: Error appending from new MS Excel 2007

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




RE: Error appending from new MS Excel 2007

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

RE: Error appending from new MS Excel 2007

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

RE: Error appending from new MS Excel 2007

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.

RE: Error appending from new MS Excel 2007

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

RE: Error appending from new MS Excel 2007

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

RE: Error appending from new MS Excel 2007

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

RE: Error appending from new MS Excel 2007

Mike,

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

RE: Error appending from new MS Excel 2007

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.

RE: Error appending from new MS Excel 2007

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

RE: Error appending from new MS Excel 2007

(OP)
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.

RE: Error appending from new MS Excel 2007

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

RE: Error appending from new MS Excel 2007

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

RE: Error appending from new MS Excel 2007

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

RE: Error appending from new MS Excel 2007

Tamar,

Quote:

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

RE: Error appending from new MS Excel 2007

(OP)
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.

RE: Error appending from new MS Excel 2007

Mark,

Quote:

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

RE: Error appending from new MS Excel 2007

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

RE: Error appending from new MS Excel 2007

(OP)
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.

RE: Error appending from new MS Excel 2007

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

Quote:

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

RE: Error appending from new MS Excel 2007

(OP)
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.

RE: Error appending from new MS Excel 2007

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close