Excel Automation

[Auguy]

I'm about to start an update of an old program. In doing so I plan to automate the reading of a batch of Excel files to obtain employee time records. Each employee will have their own Excel file. I have seen posts (problems) in this forum and others regarding the latest versions of Excel using append from, etc. Are there any issues using Excel automation on these newer versions like speed, etc., that I should be aware of?

Auguy
Northwest Ohio

 

[Mike Lewis]

Hi again Auguy,

You seem to be very busy these days.

I have seen posts (problems) in this forum and others regarding the latest versions of Excel using append from, etc.

This applies to Excel 2007. Basically, the new native file format for that version cannot be imported into VFP using IMPORT or APPEND FROM. However, if the user saves the file in the previous format, there's no problem.

Note that this doesn't apply to automation. You can open and read workbooks in the new format when you automate Excel 2007 (but, of course, that requires that you have 2007 installed on the runtime system).

I haven't heard of any issues re performance.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Auguy]

Thanks Mike. It just seems I'm running into a lot of little problems lately. Guess I'll have to update my version of office.

Auguy
Northwest Ohio

 

[Mike Lewis]

Auguy,

Guess I'll have to update my version of office.

Let's me clarify this.

If you are importing or appending workbooks in Excel 2007 format, updating your own version of Office to 2007 won't help. The point is that VFP can't read the new format.

(On the other hand, if you do upgrade to 2007, you will be able to manually open the files in 2007 and save them in the old format, which you can then import into VFP.)

If you are using Automation to access the files, you will only need 2007 if you are programmatically opening the new format files. In general, you don't need any particular version of Office to do most Automation tasks.

Hope I'm not confusing the issue too much.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Auguy]

Sorry I wasn't more clear. I will only be using automation.

Auguy
Northwest Ohio

 

[JRB-Bldr]

Just another thing to consider....

Excel makes a lousy source for data.

It can be updated in too many ways by too many individuals with little or no validation on the cell content validity and/or its format.

If you use Excel Automation to extract cell content, you have to rely on the user having provided you with appropriately defined and spelled cell content to determine specifically which row and column from which to access the data. Even simple things like mis-spelling can throw this into disarray.

If you are redesigning things consider eliminating the Excel files as a data source and replacing it with a VFP data entry form. In that manner the data can be controlled much better.

The reason that I bring this up is that after having written numerous utilities to read data from Excel files and utilize that data within a VFP application, I seem to many, many times run into problems related to data itself - its validity and integrity due to the above issues.

Good Luck
JRB-Bldr

 

[Auguy]

Thanks. This client has used their Excel Employee/Time entry for years. I have tried to talk them into converting to exactly what you suggested with no luck. Currently they do double entry by having the employee enter the information into a spreadsheet and then the billing clerk opens these and retypes everything! The only good news is the spreadsheet is designed really well and has not changed much the last few years. At least the automation I am doing now will eliminate the double entry.

Auguy
Northwest Ohio

 

[Mike Lewis]

Auguy,

This client has used their Excel Employee/Time entry for years. I have tried to talk them into converting to exactly what you suggested with no luck.

A very common situation.

And have you ever spent hours carefully crafting a beautiful report from your expertly-designed database, only to have the client tell you that what they really want is to have the data dumped into Excel, where they can mangle it out of all recognition?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Auguy]

About as many times as I've finished the report only to have them say that's not what I need. And I say that's what you told me you wanted. And they say I know, but it's not what I need!!!

Auguy
Northwest Ohio