Could not find installable ISAM when importing Access table to Excel

Question

I have a module that imports an Access table to MS Office Excel. This module has been implemented for almost 6 months and has worked well, but suddenly yesterday the module started giving an error message "External table is not in expected format"

I've searched the answer through this site and found that for Excel 2007 I must use syntax "Excel 12.0" not "Excel 8.0". I changed my script and got a new error message: "Could not find installable ISAM" (code below)

Others information: I try to open the excel file created and got warning message:
"The file you are trying to open, 'bppdan.xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

So... I decided to modify the code (shown below) and I'm not getting the error message "External table is not in expected format" nor "Could not find installable ISAM" I really want to know what cause that two error message

I really need your help

(In VB 6 Application) In menu: Project -> References, I checked the option "Microsoft Excel 12.0 Object Library"

This code generate "External table is not in expected format"


    Dim dbDest As Database

    FName = ExcelApp.GetSaveAsFilename(, "Excel (*.xls),*.xls", , "Save to Excel")
    If FName = False Then Exit Sub
    Set dbDest = OpenDatabase(FName, False, False, "Excel 8.0;HDR=NO;")

This code generate "Could not find installable ISAM"


    Dim dbDest As Database

    FName = ExcelApp.GetSaveAsFilename(, "Excel (*.xls),*.xls", , "Save to Excel")
    If FName = False Then Exit Sub
    Set dbDest = OpenDatabase(FName, False, False, "Excel 12.0;HDR=NO;")

I've modified the code:


    Dim dbDest As Database

    FName = ExcelApp.GetSaveAsFilename(, "Excel (*.xls),*.xls", , "Save to Excel")
    If FName = False Then Exit Sub
    Set dbDest = OpenDatabase("C:\Users\administrator\Desktop\my.xls", False, False, "Excel 8.0;HDR=NO;")

Dear Remou... thanks for your advice, I've post the code and the connection.. Hope that you can help me — Amy Widjaja, Dec 23 '11 at 01:56
I am afraid this is not enough information. You are running this code in Access, yes? So you are exporting from Access to Excel? Are you opening an existing Excel file? If you are running from Access, is there any reason you are not use TransferSpreadsheet? Why are you opening (?) Excel as a database? — Fionnuala, Dec 23 '11 at 12:53
Dear Remou... No, I running the code in Visual Basic. I'm not opening an existing Excel file, I ask user the file name that system will create. — Amy Widjaja, Dec 27 '11 at 02:05
Which Visual Basic? You say VBA in your tags, VBA is associated with an application, which Application are you using? — Fionnuala, Dec 27 '11 at 09:27
Dear Remou... Visual Basic 6.0 and Ms. Access 2003. I'm really sorry if I've tag a wrong category — Amy Widjaja, Dec 28 '11 at 03:12

score 0 · Accepted Answer · answered Jan 06 '12 at 08:36

Finally, our senior developer has the solution (yippie!) This is what she does:

She modified the code for preparing the worksheet, she add file format
So the code changes from

Set ExcelWkb = ExcelApp.Workbooks.Add ExcelWkb.SaveAs FName

becomes

Set ExcelWkb = ExcelApp.Workbooks.Add ExcelWkb.SaveAs FName, xlExcel5

She modified the code for connecting So the code changes from

Set dbDest = OpenDatabase(FName, False, False, "Excel 8.0;HDR=NO;")

becomes

Set dbDest = OpenDatabase(FName, False, False, "Excel 5.0;")

Could not find installable ISAM when importing Access table to Excel

1 Answers1

Linked