Excel 2007 with 250k records, in Excel 2003?

[ryan.gillies]

Hi all

Another completely different problem I've had thrust at me by my superiors that you may be able to help me with!

We have in our posession an Excel 2007 file with approximately 250k records in it. Here at work we only have Office 2003, with the compatibility pack installed. I can open the file but can only view the first 65536 rows.

We won't be able to get a compatible version until the end of the week I'm told, and I'm also told it will be too late by then.

I've tried sticking together the following piece of VBA:

Sub ImportFile()
Dim DBPath As String
Dim db As Database
Dim rs As Recordset
DBPath = "string here"
Set db = OpenDatabase(DBPath, False, True, "Excel 9.0;HDR=Yes;")
Set rs = db.OpenRecordset("SELECT * FROM Policies", dbReadOnly)
    While Not rs.EOF
        Sheets.Add
        ActiveSheet.Range("A1").CopyFromRecordset rs, 65536
    Wend
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

But it hangs at the first hurdle with "Run-time error 3170 - Could not find installable ISAM"

I'm guessing this is perhaps something the compatibility pack alone can't solve, but I'm hopeful I can proved wrong...?