Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Exporting data from Access to Xcel

Aug 1, 2005 5:43PM PDT

Office XP Pro

I've been keeping records in an Access database of people who have booked on courses I teach, including details of their payments. I copied and pasted the data in Xcel in order to do some calculations on it but found to my surprise that Xcel does not recognise the contents of the cells as numerical data and so I cannot do anything with it. I have to replace each figure manually with the same figure before being able to crunch any numbers (i,e I have to delete the figure, say

Discussion is locked

- Collapse -
It works with me ..
Aug 1, 2005 8:12PM PDT

if I set the format in Excel to fixed with 2 decimals.

I made a simple table in a new database (one text field, one numeric field with that format), filled it with a few records, exported to xls, opened in Excel, and clicked on the SUM-icon in the taskbar.

Can you try the same?

Kees

- Collapse -
Thanks Kees
Aug 1, 2005 11:22PM PDT

Actually what did it was that I discovered that you can Export from the File menu! Whereas I was copying and pasting, and for some reason that did not work. Problem solved

Many thanks for your help

Tim

- Collapse -
Copy/paste.
Aug 1, 2005 11:47PM PDT

Copy/paste works also, but with an extra step.

After pasting my example table, the numeric cells get a little green triangle in the left upper corner. Select them and a message box with an exclamation mark pops up with a balloon text telling this is a number formatted as text. Right-click on the box, choose "convert to number", and it's numeric again.

I couldn't guess that you meant "copy/paste" saying "export", or I would have told you this immediately. For me, export is strictly File>Export.

Anyway, it's sorted now.


Kees

- Collapse -
I've seen these green triangles...
Aug 2, 2005 5:13AM PDT

...recently - some time today, in fact - whilst messing about trying to solve the problem, but I can't reproduce them. I've created a table exactly as you describe, but if I copy/paste the data, Excel still doesn't recognise them as numbers - and there are no green triangles.

It would help to be able to solve this because with the Export command I have to export the whole table, and I want to be able to copy just parts of it.

Tim

- Collapse -
Hey, I've just found a solution...
Aug 2, 2005 5:24AM PDT

...by looking at the Help file, would you believe? I can select whatever I want to transfer to Excel then click Tools/Office Links/Analyse with Excel, and it promptly opens Excel with the new data all ready to analyse!

Like to find those green triangles again, though!

Thanks for your help

Tim

- Collapse -
(NT) (NT) Good job. Glad to read this.
Aug 2, 2005 5:31AM PDT