General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
How to Append Data from XLS from Excel 2007/10/13
Environment versions
Network:
Windows 2008 Server
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
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only