Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
How to Append Data from XLS from Excel 2007/10/13
Message
 
To
All
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
How to Append Data from XLS from Excel 2007/10/13
Environment versions
Visual FoxPro:
VFP 9 SP2
OS:
Windows 7
Network:
Windows 2008 Server
Database:
Visual FoxPro
Application:
Desktop
Miscellaneous
Thread ID:
01592018
Message ID:
01592018
Views:
63
As everyone here knows, VFP can't APPEND from an XLSX file, nor from an XLS 97-2003 that was created in Excel 2007 or later. We are forced to save Excel files in Excel 5.0/95 (which limits us to 16384 rows) or CSV. We can also use Excel automation, which involves a decent amount of coding.
I have figured out a simple way to save a spreadsheet as XLS 97-2003 (with up to 65536 rows) with Office 2007/10/13, and still have Visual FoxPro be able to open it. If you have a recent version of Office installed, you can use this Visual FoxPro code to convert an unreadable (to FoxPro) XLS or XLSX file to a readable 97-2003 XLS file:

LPARAMETERS tXLFilename

strDB = "C:\Jerry\Work\Temp1.accdb"
objAccess = CreateObject("Access.Application")

objAccess.NewCurrentDatabase(strDB)
objAccess.DoCmd.TransferSpreadsheet(0, 9, "Table1", tXLFilename, .T.)
objAccess.DoCmd.TransferSpreadsheet(1, 8, "Table1", STRTRAN(UPPER(tXLFilename), ".XLSX", ".Xls"), .T.)

objAccess.Quit()
objAccess = NULL
RELEASE objAccess
DELETE FILE "C:\Jerry\Work\Temp1.accdb"

RETURN

Apparently, Microsoft didn't really need to corrupt the 97-2003 XLS files for 2007/10/13.

Jerry
Next
Reply
Map
View

Click here to load this message in the networking platform