Copy and Paste from Excel not working

[HansBades]

I am trying to copy three columns of data from excel into an access table. The table has a primary key id field and then it has three fields which I am trying to populate from excel. When I try to copy and paste or append them, only the second column pastes into the access table. Any ideas of how I can fix this? Thanks.

[CJ_London]

not without seeing some example data and what your table looks like. Excel is not strongly datatyped whereas databases are. So if Access cannot identify the excel data as the correct datatype, it will be ignored. Or it may be you need to highlight the 3 columns in the table before pasting.

Either way, not a good way on which to base an import process if this is going to be ongoing

[HansBades]

The data import comes from an excel spreadsheet. Currently this is the only way the data is available. If cutting and pasting is not a good way, what other way would you suggest? Perhaps a vba program to extract from excel and insert into the access table? Thanks for your help.

[Welshgasman]

Link to the Excel sheet ?

[CJ_London]

depends on the requirements of the app but one way is to use transferspreadsheet to append the data to your table - see this link https://docs.microsoft.com/en-us/off...ferspreadsheet

another, create a linked table either through the linked table manager or in code using transferspreadsheet but to link to the excel file rather than importing - perhaps then you don't need to import it? Either way you can then use an append query to append data from your linked table to your destination table.

Another is to just use a query. Basis would be this which is the equivalent of a linked table

SELECT XL.*
FROM (SELECT * FROM [sheet1$] AS xlData IN 'C:\path\filename.XLSX'[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes])  AS XL;

to specify a range you might use this

[sheet1$]E:G

for data in columns E,F and G


or

[sheet1$]E25:X

which will import columns E to X starting at row 25

you get the idea

[HansBades]

Many thanks to all for great suggestions!

[RE_]

I had the same problem in the past. Try to format the data from excel to TEXT.