Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2010
    Posts
    8

    Unanswered: Copy and paste from Ecxel

    I have a application for a client. I have a command button that allows them to select a Excel Spreadsheet file and import the data into a Access table. What the user wants to do now is open the spreadsheet and be able to copy data from the sheet and paste it directly into a MS Access form with a datasheet. Is this possible?

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I would try to convince the client to enter the data another way. There are too many conversion issues that scare me with Excel data being pasted into Access. Text that Excel interprets as a number. Hidden characters like the apostrophe in Excel. Formatting in Excel that the user might expect to get copied into Access (currency, date formats, etc.)

    But in case you are not successful convincing your client, it can be done. I just tried it and it does work. But again, there is no data validation other than some rudimentary type checking.

    The other thing I jsut tried is pasting bad data into a table. An inexperienced user might not know what to do with the error messages that pop up. And it the user is pasting 20 or so records and one fails, how does he know which one?

    As far as I can tell the paste will on work if the Excel cells are in the same order as Access is expecting.

  3. #3
    Join Date
    Jul 2010
    Posts
    8
    When you say you just tried it and it works, can you tell me what and how. I have tried drag and drop and nothing happens. Are there settings I need to change?

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    No, I didn't drag and drop. I cut and pasted. If you select the cells you want (in my case I selected coumns A and B from row 1 to 4) and then press Ctrl-C, Excel will highlight those cells. They are effectively copied to the Clipboard. Then in Access highlight the New record in the table or datasheet (the last record with a * next to it) and then press Ctrl-V.

    You need to highlight the whole record to make it work. Click on the * and the record will highlight.

  5. #5
    Join Date
    Jul 2010
    Posts
    8
    Sorry I did mean to say cut and paste. What is happening for me is I highlight my range from the spreadsheet do a CTL-C, go over to my Access datasheet and do a CTRL-V. I get an errorr "The text is too long to be edited." All my fields in my table are Text fields with a Field size of 255. The largest text field on my spreadsheet is 43 characters.

    I also tried to paste onto a form in form view and it does paste all the data onto the form but it does it as an OLE object.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so identify what column/cell it is that is causing the problem and analyse why it isn't working

    I have sucessfuly imported data from Excel into Access and vice versa
    the down side is that no one must tinker with the Excel files, columns must be in the same format/order
    the data types must be the same... as DCK says if there are ni numbers int he first few rows of an Excel range Excel interprets that column as a string / text cell and may well throw up the errorts you are seeing.

    if this is for a client and they are not especailly computer savvy then you may well have to either use the import function as part of Access OR write your own custom import function in VB
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jul 2010
    Posts
    8
    Ok I think I have found out what is happening. If I try to cut multiple rows from the spreadsheet and paste it onto my Access datasheet it is trying to paste all the data into the first field thus the error. If if cut and paste 1 row off the spreadsheet it works but again the whole data stream is pasted into the 1st field on my datasheet.

  8. #8
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    It sounds to me like the Default View is Single Form. Is this correct? I was only able to paste data into a Continuous Forms or a Datasheet view. If you right click the form and switch it to Datasheet does it allow you to paste?

  9. #9
    Join Date
    Jul 2010
    Posts
    8
    Ok I got it to work, yes my form is set to datasheet view, there were 2 problems 1) my datatypes did not match and second and this was probably my main problem, 2)when I was trying to paste the data into the datasheet I was not selecting all the fields before I did the paste.


    Thanks for your help

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by rjjhome View Post
    Ok I got it to work, yes my form is set to datasheet view, there were 2 problems 1) my datatypes did not match and second and this was probably my main problem, 2)when I was trying to paste the data into the datasheet I was not selecting all the fields before I did the paste.


    Thanks for your help
    Why not just run an append query versus doing a cut/paste?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •