Visual FoxPro 9.0’s APPEND FROM and COPY TO commands are incompatible with the new Excel 2007 file formats (XLS, XLSX, XLSB, and XLSM). You may have noticed that I included XLS which you would think is supported, but just try saving your Excel 97-2003 XLS workbook in compatibility mode in Excel 2007 and then using the APPEND FROM command on it. VFP will complain about the validity of the file – in short, it doesn’t work. And, the COPY TO command is simply stuck in the dark ages with XLS and XL5 available. OK, so all of that is the first problem.
The second problem with the APPEND FROM and COPY TO commands is that they don’t allow you to specify the columns/fields, a where/for clause, or specify a target range/printable region. We’re pretty much stuck with specifying a workbook. APPEND FROM does allow for an optional SHEET parameter, but that’s merely adequate not super cool.
The third problem is that the usual workaround suggested for the problems above is to use automation, and automation is super slow due to the COM overhead incurred. It also introduces an additional dependency on having Excel 2007 installed on the machine on which the code is executing. The ‘slow’ is never acceptable (VFP applications can be a number of things, but slow is not one of them) and the additional dependency, while acceptable in certain instances, is a show-stopper if you are running these types of operations on say a web server that doesn’t have Excel 2007 installed on it.
I was working on a solution for a client of mine that required that I consume and create XLS, XLSX, XLSB, and XLSM files without the user needing to have Excel 2007 installed. After throwing a proof-of-concept together, I enlisted the help of my friend Bo Durban. The code provided in a zip at the end of this post is what we came up with after a couple intense coding sessions. Special thanks to my client for allowing me to share it.
The code includes 2 main functions: AppendFromExcel() and CopyToExcel(). There are also 4 helper functions: AWorkSheets(), AWorkSheetColumns(), CreateExcelTemplate(), and EmptyFieldToNull() that you might find useful or interesting. The code:
- Does not require Office 2007
- Can append from xls, xlsx, xlsm, and xlsb file formats
- Can create and copy to xls, xlsx, xlsm, and xlsb file formats
- Supports all Excel tables (worksheets, ranges, and printable regions)
- Allows target worksheet columns and table fields to be specified
- Allows header row in worksheet to have spaces in the names of the columns by enclosing them in standard brackets, for instance [My Column #1]
- Provides support for SQL Where clause or VFP For clause expressions
- Is super fast
- Probably does a few other things I’m not thinking of right now 🙂
In any event, download/extract the prg, uncomment/modify the Sample Usage code at the top of the prg to suit, and have fun! I orginally included the code at the bottom of this post, but the code made this blog entry crazy wide because of some very long lines of binary in it. So, here’s the download link for the prg file that contains the stuff you need…
Note: Additional refactoring is still needed and this definitely cries out for a class to be built, but the code works fully as-is. If you have any problems running the code (especially if a Select Data Source dialog pops up) you might want to check the OLEDB provider comments and alternatives provided in the code.