Problem with importing an excel file!

[amb3r]

Hi,
I'm having problems with importing an excel file into my database. I had a similar problem a few months ago but some how i kind of got around it at the time. Unfortunately, its come back to haunt me again!

Basically , ive created a database for a team of novice users, who will need to import an excel file every week into the database.Ive tried to keep it as simple as possible. Therefore, Ive created a command button which should import this Excel file (which is provided every week by another department and contains over 12,000 records!) into the database. Below is the code i am using to import this file:

Dim importExtractFileLocation As String

    importExtractFileLocation = "y:\Extract\Extract2.xls"
    
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "Extracts", importExtractFileLocation, True
    
MsgBox "Importing Extracts File complete.", vbOKOnly + vbInformation, "Import"

I do not get any errors when i run this code. In the status bar, it shows the progress of the file being imported, but unfortunately, part way through it just stops all of a sudden without doing anything. It doesnt import a single record and doesnt show any error message or anything.

I tried changing the formatting of the file, tried moving location of the file etc, but nothing is making a difference at the moment. Has anyone else come across this problem or know why this may be occur? Is there anyway of getting around this without converting it to a text file etc.

I need to get this sorted out ASAP, as im already running late on the project. Therefore, i would really really appreciate it, if anyone can please help me out with this.

Many thanks,
Kind regards,
Amber

 

[ideefixe]

Try importing a smaller chunk of the spreadsheet to see if you get the same error. The size of the file may be too large for Access to deal with, especially as there may be a lot of rows.

Alternatively check and see that the data in the excel spreadsheet is clean. Often what happens is that a formula in a row that is there in error, or a strange character, will not import into Access. There is a clean worksheet function in excel which you might want to employ.

Finally even if it takes longer it might be worth your while if you're familiar with excel to automate the excel object and use an ADO recordset to insert each row. ADO can trap the error and tell you where you are failing whereas DoCmd cannot trap such an error.

A quick suggestion. Have you tried pasting in rows into a table and seeing what happens?

Good luck.

 

[amb3r]

Thank you very much for your reply

At first i also though it may be because of the size of the file. I tried to import it in smaller chunks but had the same problem

I spent hours checking all of the cells in the spreadsheet (i know im sad) but could not find anything.

I think i'm going to try your suggestion about 'automating excel object and using ADO recordset to insert each row' - (whatever that means lol). I think it would be very useful to be able to import row at a time to ensure the data is correct.

I dont think 've come across this before. If you dont mind, can you please give me abit more info on this of how i can achieve this.

Thanks alot,
Regards,
Amber

 

[ideefixe]

Try

http://www.microsoft.com/technet/scriptcenter/resources/officetips/jun05/tips0607.mspx