Excel 2007 Automation

[mbh27]

My VFP 9 app uses automation successfully with Excel in versions prior to Office 2007 (though any input and output sheets are in 95/5.0 format). Users are now reporting an error (1420) when using Excel 2007 - at the point where we use the 'SaveAs' function (with parameters <filename>,43) after the formatting has been done.

Is this a known problem and if so does anyone know of a workaround?

 

[cbasoz]

It is better you quickly forget 95/5.0 formats. Their support is diminishing and current Excel even doesn't install a file converter for 5.0/95. 43 is 95/97 Excel format and it is dangerous at this time trying to save in that format. Also why in the world you would want to save in a format that's too old. Save in latest format (xlWorkbooknormal = -4143 if I remember right. You can check using object browser).

PS: Many old formats are even blocked to be opened by defaultin current Excel (Excel 2010).

Cetin Basoz
MS Foxpro MVP, MCP

 

[mbh27]

Thanks for your input. I was under the impression, however, that VFP could only read in XLS sheets in 95/5.0 format (type 43). This is why, when I've exported data to, and imported from, XL I've always used this format. In short, I use automation to write a type 43 spreadsheet, and let the user modify this in XL. They then save this in type 43 format (which is where the error now occurs) before being read by VFP to update the DBF files.

Clearly, XL is developing but VFP remains static. Have you any thoughts on how I might overcome this?

 

[Mike Lewis]

Mbh27,

You are right. By saving the sheet in 95/5.0 format, you will ensure that it can be imported into VFP (using IMPORT or APPEND FROM).

However, given that you are using Automation anyway, why not use Automation to copy the data directly into VFP? In other words, don't bother to save a file. Just loop through the rows, placing the contents into a cursor or array or whatever. This will avoid any issue with file formats (but it could be much slower).

If you must save a file, consider saving it in CSV format. Both Excel 2007 and VFP can handle CSVs with no problems.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[mbh27]

Mike,

How nice to hear from you - it's over 5 years since you guided my first faltering steps in VFP.

In the app in question I save the data in XLS (43) format rather than CSV because I want to format the spreadsheet with things like bold, borders, background shading, centering, locking columns, etc. - not possible with a CSV file?. After the app has output the XLS sheets, these are then generally emailed to various other people. The recipients then use Excel to amend and add data to these formatted sheets before saving them (in '43' format) and emailing them back. The user of the app can then update from these sheets - coded very simply as APPEND FROM <fname> TYPE XLS. In the VFP app I trap specifically against error 1661 (invalid XLS format - i.e. not type 43).

I'm not sure the 'CSV' route will allow me the same functionality unless, perhaps, I firstly save to a later XL format (having used Automation to 'pretty it up') and then get the XL users to amend and save as a CSV file, from which the VFP app can import and update its data.

Is this the sort of thing you were thinking of?

Regards, Mike Herrick

 

[GriffMG]

My only tip, after Mikes help, would be to watch out for date columns.

Excel is a bit of a stickler for assuming that dates are probably imported in US format, regardless of regional settings, so it tends to accept some dates as dates
(where they are unambiguously 'good' if assumed to be US format) and some as strings (if in US format they would be invalid). So, you can end up with some inconsistent data.

One other thing, not sure if it is an excel or vfp limitation, watch out for exports with more than 16384 rows - anything over that may not end up in the sheet!

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

Mike,

Yes, it was 2005 when we were last in touch. Good to hear that you're still working with VFP.

What you say makes perfect sense. I can see that CSVs wouldn't be any good for the files that you send to other users, since they need to preserve the formatting.

But when you finally bring the files into VFP, you are going to lose the formatting anyway (given that you're using APPEND FROM at the point).

So why not save the files in the normal default format (Excel 2007, or possibly 97/2003) for sending to other users. When they come back, you open them in Excel (via Automation) as usual. At the point where you want to transfer the data to VFP, you then save as CSV.

If, for any reason, that won't work, you have two other choices:

First, forget about using APPEND FROM. Instead, to get the data from Excel to VFP, opan an Automation session, loop through the worksheet, and copy the contents of the cells into your application. This will be much slower, but is independent of file formats.

Alternatively, install the ODBC driver for Office 2007, and use SQL pass-through to get a worksheet into VFP. I've taken that route myself recently, in an application where I had no guarantee that Excel was installed (and therefore I couldn't use Automation). It works well, and is reasonably fast, but it was tricky to code. If you want further information on this approach, let me know.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[cbasoz]

To import the data from Excel you don't need to save in excel 95/97 format nor do you need any automation.

1) To import you don't need any intermediate mediums
2) You can't simply use VFP's import or append from to get data from Excel because those commands do not know newer Excel versions.

Instead simply get the data directly from Excel itself using ADO or ODBC driver. For a sample check this:

http://www.foxite.com/archives/0000153776.htm

Cetin Basoz
MS Foxpro MVP, MCP

 

[JRB-Bldr]

"I save the data in XLS (43) format rather than CSV "

To send data TO Excel you could save the VFP data initially in CSV and then use Automation to open Excel, Import the CSV and format it as needed.

For acquiring data FROM Excel, as Mike and others have said above, you could use Automation to cause Excel to either:
* Within Excel - Save As CSV
* Then Import the CSV into VFP (probably the most long-term solution)
or
* Within Excel - Save As 95/5.0
* Then APPEND into VFP using XL5
or
* Within VFP Automation scan through the Excel cells and harvest the data
* And write data into VFP dta tables (next most long-term solution)

Good Luck,
JRB-Bldr

 

[mbh27]

Thanks to all who have contributed to this thread.

At first I thought I had quite a rewrite on my hands, but in the end I merely saved the output XL file, via automation, through 'SaveAs(<fname>,-4143)' i.e. current format. Though Automation with XL 2007 wouldn't cope with saving type 43 from within VFP, 2007 still allows the edited file to be resaved in 95/5.0 format. I can then use 'APPEND FROM ... TYPE XL5' in VFP, as before.

The net result is changing one parameter in a single line of code. This solution may not be long lasting - once Excel 2010 becomes more popular - but it certainly buys me more time at the moment.