Pasting non-contiguous cells from Excel to Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Excel spreadsheet of employee time spent on projects. I want to
copy four rows from four colums (A, B, C, and I for instance) into an Access
table with four columns so I can use Access to do summaries across multiple
time periods, staff, etc.

When I highlight the text and try to copy and paste, Access treats it as a
single item, and gives me an error that the text is too long. (This occurs
even though the text in each column should fit in each cell in Access.) I
can't use Paste Special, because the only option I'm getting is to paste as
text. I tried dragging and dropping it, this works, except for the fact that
the first column is discarded and everything is shifted left on column.

Any thoughts?
 
Best method:
In your Access database, create a table that has a field for each column in
your spreadsheet. Define the data type for each field to match the data type
coming from the Excel spreadsheet. For explanation purposes, let's call it
XLtable.
Once you have this table designed, here is the sequence to get what you want:
1. Delete the data in XLtable
2. Import the Excel sheet into XLtable

Now, there are two possibilities here. You can do everything you want to do
using XLtable even though there are more than the four columns you want to
use. This would be my suggestion; however, if you already have reports,
queries, or forms that use the 4 column table, then you can transfer the data
into the 4 column table from XLTable with an Append query. If that is what
you want, then in addition to the 2 steps above, create an append query that
uses XLTable as the source and 4ColumnTable as the destination:
3. Delete the data in 4ColumnTable
4. Execute the append query
Now you will have the data in the correct format.
4.
 
To paste from Excel to Access like you want you have to highlight the
receiptant field in Access. In other words move you cursor over the upper
left corner of the first field until a plus sign outline symbol is displayed.
Press the left mouse button and while holding it move right and down to
highlight all the fields where data is to be pasted.
 
Back
Top