# Tuesday, September 30, 2008

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

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

VFPExcel2007Functions.zip (19.14 KB)

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.

Tuesday, September 30, 2008 10:08:09 AM (GMT Daylight Time, UTC+01:00)  #    Comments [5]
Tuesday, September 30, 2008 10:09:32 PM (GMT Daylight Time, UTC+01:00)
Hi Craig, soooooo glad so read something yours again, thanks. I convert it to a class, and sent it to your mail.

Thanks for sharing it.
Wednesday, October 01, 2008 6:15:22 PM (GMT Daylight Time, UTC+01:00)
Thanks a lot..!

Encountered this a couple of months ago and I was satisfying
myself with a workaround that involves copy-and-pasting data
in between Excel 2007 and Excel 2003.

I was about to fret ('VFP future'-related) knowing how Excel is much loved by
most 'office' users/employees. (Almost everyone of them
demands us to import/export data to Excel.)

Though back then I'm pretty sure some Fox coder would eventually
share a solution. Like to most of the previous Fox-related issues
even if it means that Fox coder has to code things in machine language.

Thanks to you and Bo once again..!



Thursday, October 02, 2008 1:00:23 PM (GMT Daylight Time, UTC+01:00)
Kool with a capital 'K'!

Thanks
Stephen Wileman
Monday, October 13, 2008 9:34:44 PM (GMT Daylight Time, UTC+01:00)
Whoa, who knew it could be done?
Mike
Thursday, March 05, 2009 9:00:09 AM (GMT Standard Time, UTC+00:00)
I think it is not correct (CopyToExcel):

IF INLIST(m.lcFieldType,"T","D")
m.lcConversionFunc = IIF(!EMPTY(m.lcConversionFunc), ", ", "") + m.lcConversionFunc + m.lcVFPFieldName + " EmptyFieldToNull"
ENDIF

->

IF INLIST(m.lcFieldType,"T","D")
m.lcConversionFunc = m.lcConversionFunc+IIF(!EMPTY(m.lcConversionFunc), ", ", "") + m.lcVFPFieldName + " EmptyFieldToNull"
ENDIF

Bohous
bohous
All comments require the approval of the site owner before being displayed.
Name
E-mail
(will show your gravatar icon)
Home page

Comment (Some html is allowed: a@href@title, b, blockquote@cite, em, i, strike, strong, sub, sup, u) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

Enter the code shown (prevents robots):

Live Comment Preview

 

Archive

<July 2014>
SunMonTueWedThuFriSat
293012345
6789101112
13141516171819
20212223242526
272829303112
3456789