Results 1 to 7 of 7

Thread: Copy from Excel

  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello,

    I'm building a new AP database. I have all the information I need in Excel spreadsheets. I'm using 2003.

    I seem to remember in previous versions of Access that I could copy and paste rows from Excel into a table without any problems.

    This is what I did today; I first highlighted the row in the table with AutoNumber, then pasted the information. I didn't think it used to effect the AutoNumber field but it did. The first column of my spreadsheet disappeared (as though it's now an AutoNumber) and everything else was shifted over by one. If I insert a blank column in the spreadsheet then it pastes ok.

    I'm going to be doing this on a regular basis since Corporate wants the Excel spreadsheet for the week only and I need to keep track of everything sent so far, hence a database. I'll be copying and pasting weekly so I don't want to mess things up. (I'm getting too old to remember to insert a row but..... ..) Am I doing it correctly?

    Thanks!
    Louise

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The behavior that you describe also occurs in Access 2002 (XP).

    Will you be copying all rows from the Excel worksheet or only some rows?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans!

    No, not all the rows. Just the ones that will have information entered in them. Not the column headers and the empty rows.

    Thanks,
    Louise

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='weese237' post='773457' date='03-May-2009 01:37']No, not all the rows. Just the ones that will have information entered in them. Not the column headers and the empty rows.[/quote]
    OK, so you want to copy all data rows.

    If you're going to do this repeatedly, you could speed it up as follows:
    1. In your Access database, create a link to the Excel sheet through File | Get external data | Link tables...
    2. Create an append query that adds the columns of the linked worksheet to the corresponding columns in your Access table.
    3. You can now run the append query each time you need it.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans,

    Not to be difficult, but I'm not sure I can do that. The filename changes every week as well. I think I might enter a "comment" on the spreadsheet or setup another spreadsheet that links the information and copy from that one.

    We'll see what happens.

    Thanks again!
    Louise

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='weese237' post='773460' date='02-May-2009 17:56']Not to be difficult, but I'm not sure I can do that. The filename changes every week as well. ....[/quote]
    If there is a scheme to the naming of the Excel file, such as "Week of 5/1/2009" it is fairly easy to infer that either in opening the workbook, or in editing it.

    I've read through several of your posts, and it seems to me you might want to consider a different strategy in the long term. Access will handle the the issues such as the vendor list very nicely, and you can create Excel workbooks from Access in several ways. I gather that you now do the data entry in Excel, so what I'm suggesting would be to do the data entry in Access, and then create the Excel workbook using either the export functions or with Automation. Not something to jump into lightly, but long term I think it offers some advantages, so some food for thought.
    Wendell

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Wendell, Thanks for your reply. It sounds intriging. I think I'll forgo that (at least for now ) I'm using a Corporate spreadsheet that they don't want altered. It's on a Wan that they control.

    Thanks again.
    Louise

Posting Permissions

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