Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Paste from Excel (2000)

    As far as I can tell, with version 2000 and beyond you are supposed to be able to copy a block of Excel cells to the clipboard then paste them into Access, where they become an Access table. This sometimes works, but more often than not I get a message like:

    The Microsoft Jet Database Engine could not find the object '[FY 2003-4$R1C2:R7C10]. Make sure the object exists and that you spell its name and path name correctly.

    Does anyone know why? This happens whether the Excel file is new and unsaved or an existing file that I have not edited. I often move things from Excel to Access and would find the ability to paste cells into a new table a great benefit.

    Ian

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Paste from Excel (2000)

    Hi Ian
    I tend toy use the DoCmd.TransferSpreadsheet command in VBA to import excel into access.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste from Excel (2000)

    Using A97 and Excel2000 I have no problems copying tables from Excel to Access. It even asks if there is a header row and uses it for field names.

    HTH

    Peter

  4. #4
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste from Excel (2000)

    I get the same header column question, then I get the no can do message.

    Ian

  5. #5
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste from Excel (2000)

    Do the Excel spreadsheets have a header row?

    This may be a 'stupid' question but it's the first thing to check. Second thing to check, from personal experience, is that you are not using any 'odd' characters in the headers.

    If neither of the above help, try linking the Excel sheet to the Access database, you can link tables that Access will not allow you to import, more personal experience...... Once linked you could use a query to transfer the data to where you want it.

    Hope this helps.

    Ian

  6. #6
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste from Excel (2000)

    May be a dumb question, but why not use the import function?

  7. #7
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste from Excel (2000)

    It's not a dumb question. I would do the whole thing in code if what I was doing was going to be done regularly. However, when I'm doing one-offs, say someone sends me an Excel spreadsheet with data that they need combining with data from several other sources, I do the whole thing in the GUI. It's one of the things that Access is great at. When you're working in the GUI it is so much quicker to copy and paste than it is to go through the import function. If some of the data comes from somewhere buried deep within a complex spreadsheet then the import function is clunky or unusable anyway. You need to see the data to make sure that you are selecting the correct chunk.

    Also, this is supposed to work.

    Ian

Posting Permissions

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