Best way to import data from Excel

[RCurtin]

Hi everyone,
I would just like to get your opinions on this - I've been reading alot about importing from Excel and there seems to be several different options with it. Here is what I have found and the pros and cons with each method.

I have an Excel spreadsheet that come in a specific format - I would like to import them without having to manually add/change column headers etc. The thing is that some of the columns in the spreadsheet do not have names. Also there will be three extra columns in the access table.

Method 1:
Import to an existing table using the Import Wizard. The following code invokes the Import Wizard:

DoCmd.RunCommand acCmdImport

• When the wizard runs I have to tick the box that says 'First row contains column headings'. Otherwise it doesn't give me the option to add the data to an existing table on the next screen. When I try to import the original spreadsheet I get "an error occured trying to import file 'original spreadsheet.xls'. The file was not imported."
• If I manually change the headings in the excel sheet it works.

Method 2
Import to an existing table through VBA:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "Drawings register", spreadsheetFile, False

• This works if I manually change the headings in the spreadsheet to the same ones I have in my Access table.
• But if I dont do that I get this error:
  2391 - Field 'F1' doesn't exist in destination table...

Method 3

1. Import the spreadsheet into a new temporary table using the Import Wizard.
2. Use an append query to append the data to my original table.

Ok I just tried doing that using the Import Wizard. It worked fine (except that it imported the top 3 rows which I don't want - I guess I could sort that out in the append query.) The only problem with this method is that it is too many steps for the user - you have to specify each column to be imported and the field name for each one in Access. Also this will be exactly the same for each spreadsheet.

Method 4

1. Import the spreadsheet into a new temporary table using VBA.
2. Use an append query to append the data to my original table.

Here is my attempt at automating the process of importing the data into a new table:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "temp", spreadsheetFile, True

I have found that it works better to set 'Has Field Names' to True. That way where the fields names are the same they are matched up in the query. Access uses its own names for the other fields but seemsto match them propperly to the corresponding fields in the spreadsheet. If I set it to False and run the query it brings up a parameter box and asks me to insert the field name for each column.


Those are my findings on it anyways! Am I missing anything? It seems to me that method 4 is the best way to go - in my case anyways. I have seen code that creates a database object using DAO. What is the advantage of doing this? Anyone have any extra information/ opinions on the best approach.

 

[reclusivemonkey]

Link, don't import.

 

[RCurtin]

I know you've probally answered this before but why is it better to link?

I think that I would rather to have all the drawings listed in one table rather than linking to numerous different spreadsheets. Also I need to add 3 extra fields e.g. the status of each drawing to the table - it seems simpler if I just import the data and then I can add the extra data to each record.

Thanks,
RCurtin.