file in different format than specified by file extension 2

[KellyK]

Hi all,

I am working on an Access database for a customer. There is a main form where the user selects a location of a file, clicks a button and the file is imported into a table in the database.

This file has extension .xls. So, I used the following code to import it:

DoCmd.TransferSpreadsheet acImport, , "JIT", txtJITReportFile, True, "A:M"

Easy enough except I'm getting the Run-time error '3274': External table is not in the expected format.

So I attempt to open the Excel doc myself and I get a warning message "The file you are trying to open,'filename', 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?" I click "Yes" and voila, the file opens and looks like a typical Excel file.

This file is provided weekly by a third party via email. The user saves it to a network location and then uses the Access database to work with the file. Is there any way I can import this into Access using VBA short of going to the third party and requesting they "fix" their file? No clue what is causing that error to begin with.

Thanks in advance for any suggestions!


Kelly

 

[KellyK]

Sorry guys, should have done a little more research before posting. I found this:

"You can also set a reference to excel in Access, and then use the application object to turn off the warning in excel before doing the import.

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Set objXL = New Excel.Application
'turn off excel warnings
objXL.DisplayAlerts = False
'Open the Workbook
Set objWkb = objXL.Workbooks.Open(fpath)
'import excel object
DoCmd.TransferSpreadsheet acImport, 8, "applicantImport", fpath, True, "A1:V10000"
"

http://www.grinn.net/blog/dev/2008/06/file-you-are-trying-to-open-is-in.html

If nothing else, hopefully I've helped someone who is running into this same issue.

Kelly

 

[fumei]

Kelly, thank you for be so thoughtful of others. That is what can make Tek-Tips so great.

Not only did you post how you resolved your own question, but supplied a reference. Good on you. Have a star.

Gerry

 

[SkipVought]

I'm right behind you, Gerry. Thanks, Kelly!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}