Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
How to Append Data from XLS from Excel 2007/10/13
Message
 
À
Tous
Information générale
Forum:
Visual FoxPro
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Titre:
How to Append Data from XLS from Excel 2007/10/13
Versions des environnements
Visual FoxPro:
VFP 9 SP2
OS:
Windows 7
Network:
Windows 2008 Server
Database:
Visual FoxPro
Application:
Desktop
Divers
Thread ID:
01592018
Message ID:
01592018
Vues:
66
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
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform