Why APPEND FROM TYPE XL8 Fails with Modern Excel — and How to Fix It with COM Objects

If you maintain a Visual FoxPro application that reads Excel files, there is a good chance you have used APPEND FROM (filename) TYPE XL8 at some point. It is clean, fast to write, and it worked reliably for years. Then one day a user upgrades to Microsoft 365, saves a spreadsheet the same way they always have, and your VFP program either silently produces an empty cursor, complains that the sheet is missing, or — worst of all — crashes the entire session outright.

This is not a bug in your code. It is a compatibility break between VFP 9's built-in Excel import driver and the way modern Excel saves .xls files. Here is what is happening and how to fix it for good.

What Is APPEND FROM TYPE XL8?

APPEND FROM is a classic VFP command that reads records from an external file directly into the currently selected table or cursor. The TYPE XL8 parameter tells VFP to use its built-in ISAM (Indexed Sequential Access Method) driver for Excel 97–2003 format .xls files. The optional SHEET clause lets you target a specific worksheet by name.

When it works, the pattern is elegant:

*-- Classic VFP pattern: import data from an Excel spreadsheet
SELECT 0
CREATE CURSOR IMPORTDATA (ROWTYPE C(3), ORDNO C(20), CUSTCD C(6), AMOUNT N(12,2))
COPY STRU TO (lcTempFile)
USE EXCL (lcTempFile) ALIAS IMPORTDATA

*-- This reads the Excel file row by row, maps columns to cursor fields
APPEND FROM (lcExcelFile) TYPE XL8 SHEET "Data"
IF RECCOUNT("IMPORTDATA") < 2
    =MESSAGEBOX("Could not read spreadsheet.")
    RETURN
ENDIF

VFP reads the spreadsheet row by row and populates the cursor, mapping columns by position to cursor fields. No COM overhead, no external dependencies, no extra libraries — it all runs inside the VFP runtime.

Why It Breaks with Modern Excel

The XL8 ISAM driver in VFP 9 was built for the Excel 97–2003 binary format — the original .xls file format. These files are OLE Compound Documents: a binary container format that Microsoft introduced in the early 1990s. You can identify a genuine OLE2 binary .xls file by its first four bytes: D0 CF 11 E0.

When Microsoft introduced the .xlsx format with Excel 2007, they also changed how newer versions of Excel generate .xls files. Excel 365 and later versions increasingly save .xls files in a format that the VFP 9 32-bit ISAM driver no longer correctly recognizes — even though the file extension is still .xls. The internal structure does not match what the aging driver expects.

The result depends on your exact VFP configuration, but you will typically see one of three behaviors:

• Silent empty cursor — APPEND FROM completes without an error, but RECCOUNT() returns 0 or 1, and your downstream logic breaks quietly.
• "Sheet not found" error — VFP can open the outer file container but cannot locate the named worksheet, even when the tab is clearly visible in Excel.
• Hard crash — VFP 9 terminates immediately, even when run from the command window. No error dialog, no recovery.

The crash scenario is the most damaging in a production ERP environment. It drops the user out of the system with no warning and no error message they can report.

Confirming the File Format

If you want to check whether a specific .xls file is the problem, you can verify its actual binary format from PowerShell without opening Excel:

$bytes = [System.IO.File]::ReadAllBytes("C:\path\to\file.xls") | Select-Object -First 4
$bytes | ForEach-Object { "{0:X2}" -f $_ }

• D0 CF 11 E0 — Genuine OLE2 binary .xls. The VFP XL8 driver can read this.
• 50 4B 03 04 — ZIP header. This is an OOXML-format file with an .xls extension. VFP will fail on this file.

Excel 365 produces the second result even when users choose "Excel 97-2003 Workbook (.xls)" from the Save As dialog. You cannot reliably force OLE2 output from Excel 365, and the FORMAT := 56 workaround in Excel COM automation does not consistently help. The correct fix is to stop relying on the VFP ISAM driver entirely.

The Fix: Excel COM Automation

The reliable, version-independent solution is to replace APPEND FROM TYPE XL8 with Excel's own COM automation interface. Instead of asking VFP's built-in driver to parse the file, you ask Excel itself to open the workbook, which always works, because Excel knows its own file formats.

VFP 9 has full COM support. The same Excel COM objects already used throughout VFP applications for writing spreadsheets work equally well for reading them.

Before — Broken with Excel 365

*-- VFP 9 original code — crashes or silently fails with Excel 365 output
APPEND FROM (lcExcelFile) TYPE XL8 SHEET "Data"
IF RECCOUNT("IMPORTDATA") < 7
    DEACTIVATE WINDOW DIALOG2
    USE IN IMPORTDATA
    =XTCMESS("Selected spreadsheet had less than 7 rows - Update CANCELED...", "SOK")
    RETURN
ENDIF

After — Works with Any Excel Version

*-- Step 1: Open the workbook via COM — compatible with all Excel versions
LOCAL loXL, loWB, loWS, lnLastRow, lnRow, loData
LOCAL lcRowtype, lcOrdno, lcCustcd

loXL = CREATEOBJECT("Excel.Application")
loXL.Visible       = .F.
loXL.DisplayAlerts = .F.

TRY
    loWB = loXL.Workbooks.Open(lcExcelFile)
CATCH TO loErr
    loXL.Quit
    loXL = .NULL.
    DEACTIVATE WINDOW DIALOG2
    USE IN IMPORTDATA
    =XTCMESS("Could not open spreadsheet: " + loErr.Message, "SOK")
    RETURN
ENDTRY

*-- Step 2: Find the target sheet by name (case-insensitive)
loWS = .NULL.
LOCAL lnSht
FOR lnSht = 1 TO loWB.Sheets.Count
    IF UPPER(ALLTRIM(loWB.Sheets(lnSht).Name)) = "DATA"
        loWS = loWB.Sheets(lnSht)
        EXIT
    ENDIF
ENDFOR

IF ISNULL(loWS)
    loWB.Close(.F.)
    loXL.Quit
    loXL = .NULL.
    DEACTIVATE WINDOW DIALOG2
    USE IN IMPORTDATA
    =XTCMESS("Data sheet not found in spreadsheet - Update CANCELED...", "SOK")
    RETURN
ENDIF

*-- Step 3: Validate row count
lnLastRow = loWS.UsedRange.Rows.Count
IF lnLastRow < 7
    loWB.Close(.F.)
    loXL.Quit
    loXL = .NULL.
    DEACTIVATE WINDOW DIALOG2
    USE IN IMPORTDATA
    =XTCMESS("Selected spreadsheet had less than 7 rows - Update CANCELED...", "SOK")
    RETURN
ENDIF

*-- Step 4: Read ALL data in one round-trip for speed (cols A-D = 1-4)
*-- This reads the entire range as a 2D array — much faster than cell-by-cell
loData = loWS.Range(loWS.Cells(1,1), loWS.Cells(lnLastRow, 4)).Value

*-- Step 5: Close Excel immediately — no need to keep it open
loWB.Close(.F.)
loXL.Quit
loXL = .NULL.

*-- Step 6: Loop through the array and populate the cursor
SELECT IMPORTDATA
FOR lnRow = 1 TO lnLastRow
    lcRowtype = SafeStr(loData(lnRow, 1))
    lcOrdno   = SafeStr(loData(lnRow, 2))
    lcCustcd  = SafeStr(loData(lnRow, 3))
    INSERT INTO IMPORTDATA (ROWTYPE, ORDNO, CUSTCD, AMOUNT) VALUES ( ;
        lcRowtype, lcOrdno, lcCustcd, ;
        IIF(VARTYPE(loData(lnRow,4))='N', loData(lnRow,4), 0) ;
    )
ENDFOR

Helper Function: SafeStr

Excel COM returns .NULL. for empty cells and may return a numeric type (N) for cells that look like numbers. This small helper converts any cell value to a clean, trimmed string:

*-- Safely convert a COM cell value to a trimmed string
FUNCTION SafeStr(tuVal)
    IF ISNULL(tuVal)
        RETURN ''
    ENDIF
    DO CASE
    CASE VARTYPE(tuVal) = 'C'
        RETURN ALLTRIM(tuVal)
    CASE VARTYPE(tuVal) = 'N'
        RETURN ALLTRIM(STR(tuVal, 15, 0))
    OTHERWISE
        RETURN ALLTRIM(TRANSFORM(tuVal))
    ENDCASE
ENDFUNC

Key Things to Watch For

Read the range in one call, not cell by cell. The most common performance mistake with Excel COM is accessing loWS.Cells(row, col).Value inside a nested loop. For a 500-row spreadsheet that would be thousands of individual COM calls. The loWS.Range(...).Value call reads the entire block into a VFP 2D array in a single round-trip, then you access loData(row, col) entirely from memory. The speed difference is dramatic.

Always check for .NULL. Excel COM returns .NULL. for empty cells. If you skip the ISNULL() check and try to use that value in a REPLACE or INSERT, VFP will throw a type mismatch error. The SafeStr helper handles this cleanly.

Numeric cells need type-aware conversion. Pack numbers, order numbers, or any column where Excel "sees" the content as a number will come back as type N rather than C. Using TRANSFORM() directly on a numeric double may give you decimal places you did not want (e.g., "12345.00000"). The STR(val, 15, 0) pattern truncates cleanly.

Close the COM objects before releasing them. loWB.Close(.F.) and loXL.Quit() must be called explicitly. Releasing the object reference without quitting first leaves an orphaned EXCEL.EXE process in Task Manager. In a busy environment where this code runs many times a day, those phantom processes accumulate.

Wrap Workbooks.Open in TRY/CATCH. File-not-found, network disconnects, and password-protected workbooks all throw at that line. Without the catch block, your VFP program will crash at Open rather than inside APPEND FROM — same outcome, different line.

FAQ

Does this fix require Excel to be installed on the machine? Yes. CREATEOBJECT("Excel.Application") requires a licensed copy of Excel on the same machine running the VFP program. If you are running a batch process on a server without Excel, you will need a different approach — such as pre-converting the file to CSV and using APPEND FROM TYPE DELIMITED, or using a third-party XLSX parsing library.

Can I still use APPEND FROM TYPE XL8 for files I know are in the old format? Yes, if you can guarantee the file came from Excel 2003 or earlier and was never re-saved by a newer version. In practice, that guarantee is hard to maintain as users upgrade. The COM approach is safer for any file you did not generate yourself, since it handles both old and new formats transparently.

What if I do not know the sheet name ahead of time? The FOR lnSht = 1 TO loWB.Sheets.Count loop lets you iterate all sheets and match by name. You can also use loWB.Sheets(1) to simply grab the first sheet regardless of name — useful when the workbook is always single-sheet.

Is the COM approach meaningfully slower than APPEND FROM? With the bulk range read (loWS.Range(...).Value), the practical difference is under a second for the typical ERP spreadsheet sizes of a few hundred to a few thousand rows. Cell-by-cell reading would be noticeably slow, but the range read approach is nearly equivalent to the old ISAM driver for any spreadsheet you would realistically process interactively.

Will this work with .xlsx files as well as .xls? Yes. loXL.Workbooks.Open() handles both formats transparently. Once you are using COM to read, the file format is Excel's problem to sort out — not yours.

We have dozens of places in the codebase that use APPEND FROM TYPE XL8. Do we need to replace all of them? Only the ones reading files that users are generating or saving with modern Excel. Files that VFP itself creates via COM (using oWorkbook.SaveAs(filename, 56)) are less likely to trigger the problem, but even those are not safe if a user later opens and resaves the file in Excel 365. The safest approach is to replace all occurrences over time and add the COM pattern to your internal development standard going forward.

Migrating away from APPEND FROM TYPE XL8 is one of those maintenance tasks that feels optional until the day it is not. If your VFP ERP application is still in active use — as many are — and your users are on Microsoft 365, this is a breaking change waiting to happen. The COM approach is not significantly more complex, and it is the same pattern you are already using everywhere in VFP code that writes to Excel. The fix fits naturally into the existing codebase.

If you are running a legacy FoxPro or VFP-based ERP system and need help assessing or updating your Excel integration code — or if you are evaluating a modern ERP upgrade path — contact PC Methods. We have been working with these systems for over 37 years.