Copy/Paste Excel data into Access

**jay567** · 12-05-2011, 06:08 PM

Hi,

How do I copy/paste data from my Excel spreadsheet into Access? When I copy a range of cells from Excel then paste it into Access, the pasted data is shifted by 1 column to the left so that my first column (dates) from Excel is not imported and all my newly pasted data is under the wrong heading. Do I need to add an extra first column to my excel spreadsheet as an "ID" column?

Historically I've just been using the import function on Access but it seems to take a long time so I'm just trying this method.

Thanks,

Jason

**JieJenn** · 12-05-2011, 06:11 PM

It is better to import data to MS Access than copy/paste

**jay567** · 12-05-2011, 06:16 PM

Thanks for the reply.

Is there anything I can do to make an import faster? It will take me a few minutes to open data from the spreadsheet in Access, then another few minutes to import the data into Access... I have to do this every day for a few spreadsheets so it drives me nuts just waiting so long for a spreadsheet that may have up to 70 rows? It just seems to me like it shouldn't take so long!

Jason

**davegugg** · 12-06-2011, 12:06 PM

If you automate the import using VBA it should take no longer than 5 seconds at the very most.

Here is some generic code that clears a table, then imports new data into it from Excel:

Private Sub btnImportLngExtract_Click()
'The purpose of this procedure is to import the Long Extract that was run out of UniFi.

    'Declare local variables.
    Dim strFileName As String: strFileName = "C:\Temp\yourfile.xls"
    
    'Turn off warnings so the user isn't asked about record changes.
    DoCmd.SetWarnings False
    'First clear out the table of all the old data.
    DoCmd.RunSQL "DELETE * FROM tbl_LongExtract_Data"
    'Import the new data.
    DoCmd.TransferText acImportDelim, "Longextract Import Specification", "tbl_LongExtract_Data", strFileName, True

    'Turn warnings back on.
    DoCmd.SetWarnings True

End Sub

You should be able to modify this for your purposes.

**Whizbang** · 12-06-2011, 12:25 PM

This is because you are probably also selecting the ID field (either because you selected the entire row in Access, or because you selected the ID field manually).

In access, on the "New" record row, hover your mouse on left-hand side of the first field after ID. It should turn into a fat, white plus sign. Click and drag to the right the same number of columns you are copying from excel.

Then paste. It should put the first copied column from excel into the first selected column in Access.

**jay567** · 12-07-2011, 12:31 AM

Thanks Dave, just a quick question regarding the VBA code. Why would using the VBA code only take 5 seconds to import but if I manually do it, it will take minutes?

Thanks for the suggestion Whizbang, put your way works if I am pasting 1 row but I often have many rows to paste.

**jay567** · 12-07-2011, 12:40 AM

I think I figured out my problem. I was trying to import a spreadsheet that contained formulas with cell references and was part of a workbook with lots of other formulas and numbers in it. I just copied and pasted the values that I wanted into a new Excel workbook and imported that into Access and it was super fast.

Thanks for all your help!