Import then Append?

[Superock]

Ok, so I now know how to import data from a spreadsheet to access.

Private Sub Command0_Click()
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "Sheet1", "C:\Temp\Table1.xls", True, "A1:C11"
End Sub

1. Can I add to this code to append the data to a existing table?
2. Is there a coded method to skip the import process above and append directly from xls?
3. Do I import with the code above (Into temp table) and then using a append query, append to the correct table?

(If there is a code method could you please include a sample)

Thx

 

[FLabrecque]

Yes, you can use VBA to append data, instead of importing into a dummy table and than appending.

What you need to do, is create an Excel-object and open your xls file with it. This will enable you to see the value of the fields you want, launch a couple a append statement. If you have big excel files, the import/append-later method would be quicker.

Just search on "EXCEL" in VBA. You'll find a lot of stuff useful.

 

[mresann]

I agree with Pat and , ah, "Flab". However, ensure that the Excel formats for the data you are trying to import into Access matches the fields of the tables you are importing to. Access does an ok job in recognizing certain data types, but if you are trying to import a number from an Excel cell that is formatted as "Text", into an Access field whose datatype is "Number", you may have an error message pop up.

In general, if you are doing a one time import from Excel, I would go ahead with the two step route. That way, the first step of importing the data would create the Access table, which can then be modified so the datatypes are more easily corresponded with the target import tables.

If the import process is to be done over a series of Excel sheets, then the design of the Excel sheets should be consistant with the datatypes of the Access sheets. Whichever way works for you is best.