Paste from Excel (2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Ian

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote