Creating Excel (2007) spread sheets using VFP 1

[johnod33]

We are thinking of upgrading to excel 2007.

I often import excel spread sheets programmatically using VFP (6.0).
IMPORT FROM (mfilename) XL8 SHEET

I also create Excel spread sheets programmatically.
COPY TO filename.xls XLS or FOX2X for those databases over 16,385 records.

I also do an extensive amount of formatting of the excel spread sheet using VFP.

I wonder if I am going to be able to continue to do this if we upgrade to Excel 2007.
You can't even save to a DBF anymore in Excel 2007.
I am in big trouble if VFP will not (for lack of a better word) interface with Excel 2007.

I have searched the Internet on this subject and have only found information on Excel 2007 itself.

Does anyone have any info on this?

John O'D

 

[Mike Lewis]

John,

Yes, you can continue to use your code if you go to Excel 2007.

Your COPY TO will continue to work. But you will only be able to use IMPORT (or APPEND FROM) on workbooks that are saved in "compatibility mode", that is, in Office 97 - 2003 format.

If a user saves a workbook in Office 2007 format (XLSX - which is the default), you will not be able to use IMPORT or APPEND FROM. There is no workaround for this.

You can, of course, also use Automation to import Excel data, but that's more difficult than a simpe import.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[johnod33]

Thanks Mike,

What do you mean:
"You can, of course, also use Automation to import Excel data"?
I use the IMPORT in a VFP program. Is that what you mean?
Maybe I will learn something new here.

John O'D

 

[Mike Gagnon]

I think Mike means, Excel Automation not straight append from.

For example:

ox = createobject("Excel.application")
ox.Workbooks.Open(getfile("xlsx"))

And startin parsing the sheet from there.



Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[Mike Lewis]

John,

No, Automation is not the same as Import.

Automation is a way for one application to control another. It is sometimes called ActiveX Automation or COM Automation.

In brief, the controlling application (in this case, Visual FoxPro) can control the Automation server (in this case, Excel). It does this by instantiating a COM object and accessing its properties and methods.

It's a big subject -- much more than I can explain here. I can only say that, if the whole area is new to you, it will not be a quick solution to your problem. That said, Automation can provide a lot of useful functionality (not just in Excel), and it might well be worth the effort of learning it.

There are plenty of articles and books on the subject, including some useful articles in the Tek Tips FAQs. If you want to explore it further, start by doing a search for something like "Visual FoxPro COM Automation Examples".

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[johnod33]

Thanks Mike L and Mike G,

I understand what you are talking about now. I use that all the time in my programming.
For some reason I didn't associate this process with the term you used AUTOMATION.

Thannks for the info!

John O'D

 

[Mike Lewis]

That's fine, John.

Just to summarise: You won't need to do anything special for getting data from VFP to Excel.

When going the other way, IMPORT and APPEND FROM will work fine, provided the workbook was saved in 97 - 2003 format.

Automation provides an alternative to either or both methods, and is not limited by any particular version of Excel.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[TamarGranor]

When going the other way, IMPORT and APPEND FROM will work fine, provided the workbook was saved in 97 - 2003 format.

Are you sure? I thought there's a bug in Excel 2007 that keeps this from working.

Tamar

 

[Dan Freeman]

I was just about to jump in with Tamar's "but...."

Excel needs to save as *Excel 95* (the previous version) for VFP to read the resulting XLS.

 

[Mike Lewis]

Tamar and Dan,

I thought we had thrashed all this out a year or so ago. If I remember right, I did some tests and we concluded that it was only the new XLSX format that couldn't be imported. But it was a long time ago, and I can't find the thread here in Tek Tips where we discussed it.

Whether it's Excel 95 or Excel 97 - 2003, the point is that it is possible to do it. I no longer have Excel 2007, but presumably John has, so it will only take him a moment to run a test and find the answser.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[johnod33]

Hi guys,

I don't have it yet. My boss wants me to do some testing on it before we get it for everyone in our department.
I would just as well keep using the older versions. I create data for the financial analysis people. I often teeter on 65,500 records. If I need to send more than that I insert the first 65,000 records in tab 1 and the rest in tab two.
The day I figured out how to do that programmatically I was ecstatic!

John O'D

 

[white605]

Can we not automate Excel 07 to open an xlsx formatted file,
the automate the saving of the file in 95-2003 format,
and import it directly into VFP with the new file?
wjwjr

This old world keeps spinning round - It's a wonder tall trees ain't layin' down

 

[Mike Lewis]

Can we not automate Excel 07 to open an xlsx formatted file, the automate the saving of the file in 95-2003 format,
and import it directly into VFP with the new file?

Don't see why that shouldn't work. Alternatively, we could use Automation to bring the data directly into VFP. Then again, if it's a large file, your suggestion would probably be faster.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[craigsboyd]

You may find the following solution on my blog useful...

Using Excel 2007 File Formats in VFP 9.0

...it allows you to work with different versions of Excel files (including Excel 2007) and even create Excel files without Excel being present on the user's computer. It is also orders of magnitude faster than automation and allows you to do things (select from the internal tables of an Excel document for instance) you can't do with native VFP functions.

SweetPotato Software Website
My Blog