Pasting Access table into Excel; can't use SUM function

J

James Buehner

We have 200+ Oracle database tables that we use to create queries and make
tables. We create select queries on a daily basis that are comprised of
approx. 8,000 to 14,000 records. We usually take this information and paste
it into Excel for analysis. Recently, we upgraded from NT 4.0 WS to XP Pro.
We noticed that when we now paste our information into excel, the formatting
is changed. Numbers that we could easily SUM before, no longer respond to
the sum function. When we use the SUM function, the function does not
recognize the cell as a number and responds with '0'. When we attempt to
reformat the cells, they do not take the new number atribute. We have found
two ways to work around the issue, neither of which are appropriate
resolutions. First, we can highlight each cell separately and then press
enter, which seems to format the cell as a number. It is almost like Excel
does not recognize it until it is 'enterd'. Second we can use the analyze
function in excel to export the table to excel, which seems to send all
formats. We are currently unable to use either solution since we run a
number of automatic programs that rely on the information being copied and
pasted into excel then analyzed unattended. Does anyone have a possible
solution?
 
K

Ken Wright

Trimall:-

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

When you bring your data across it becomes Text and will not be recognised as a
value. Depending on whether you have leading/trailing spaces or other
characters in your data you may well be able to get away with just formatting an
empty cell as number, copying it, selecting all your data and doing Edit / paste
special / Add, but personally I wouldn't be without Dave's code now for this
very reason. Saves a lot of work.
 
Top