Excel Import Error

[scottian]

I have to import a number of excel spreadsheets each day, im trying to automate it but there is a problem with files. The format of the sheets have been designed by someone else and cannot be changed and they contain a few cells at the top which are there to guide the user who is filling the spreadsheet exactly what is required. when i try to import the sheets into access i get 'an error occured the file was not imported'. Ive wored out that its the wierd records at the because when i removed themand tried importing the file it wored perfectly. Im thinking i need to import the sheet into a recordset and delete the offending records before appending, but im useless at recordsets and im not even sure if it will work. Can someone please help me out.

"My God! It's full of stars...

 

[lars7]

Hi,

Have you tried putting in a data range:

DoCmd.TransferSpreadsheet acImport, 8, "Your Table", "Your File Path\Your Excel Sheet", True, "M856" <---- Data Range

 

[lewds]

HI....

This question has been entered many times!

Investigate the naming of RANGES in EXCEL

If the EXCEL sheets are preset without change, you can name the RANGE of the data you want and then use the Docmd.TransferSpreadsheet to get the data into a temp table for further manipulation.

IF the EXCEL Sheets are as templates and data is just overwritten on a periodic basis, then you could use Access to link the named RANGES and treat them as tables.

Hope this Helps.........

 

[scottian]

I thought of that, but im also no good with excel. Because i dont know how many records will be in the excel sheet, do you know how i specify a cell range that will encompas all cells with data in them? I cant use a link as the spreadseets are submitted and there are more than one on a daily basis.

"My God! It's full of stars...