excel copy and paste

[BRP250]

Using MS Excel 2k. I have a data link to an sql DB on one sheet and when I copy and paste the data to another sheet to be read by a graph the data is not recognised by the graph. It all looks good, the cells have the correct values. If I type the numbers in the graph works fine.

Anybody any ideas? I've tried special paste features with no luck.

Bruce

 

[macropod]

Hi Bruce,

Are you sure the db results are being imported as values and not as text?

Cheers

[MS MVP - Word]

 

[BRP250]

Sorry they are text and that is something I have to fix as some of the numbers include text.

I've upgraded to 2003. If I use the convert to number option when pasting I've discovered, the numbers are recognised. It would be good if I could format the data columns on import.

Thanks,

Bruce

 

[xlbo]

Rather than copy/pasting, you should try using Data>Get External Data and running the query directly into Excel. This should sort out any data type issues as they mainly occur in the paste process...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[BRP250]

Geoff, I'm using the get external data query engine to access the data. At the moment I have to copy and paste to transpose the data as I do it.
If I can get that going satisfactorily I'll work on manipulating the data in the sql DB before it gets to excel. Ideally excel would format etc. when reading the data.

Thanks, Bruce

 

[SkipVought]

"I copy and paste the data to another sheet to be read by a graph"

"I have to copy and paste to transpose the data as I do it."

Hmmmmm. The orientation of the data has nothing to do with data for a chart. You can plot from data that is in EITHER rows or columns.

More than often, however, data must be SUMMARIZED to plot correctly. Perhaps you ought to describe what you are attempting to do.




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BRP250]

ok here's the story:
The user has a spreadsheet (4 actually) that he's been using for years, it has data from 1995. He has every day in column A i.e. in date format and then about 20 columns of readings he's been receiving on hardcopy from other programs for that long. Not every day has readings but he likes it like that.
Hanging off this 'DATA' sheet are a number of graphs calculated sheets and other spreadsheets.
Along comes the IT geek thinking I can give him some relief from typing data in (he is used to typo's) that already exists in other formats throughout the organisation and started by setting up a view in a sql DB to access most of the data. One of his critical reporting times comes up and we haven't put the data in a compatable format... you know the story from there. He has to combine the new data into his old. On his graphs he changes the dates all the time for different reports. How am I doing? This is the start of a long process, but at the moment he's thrilled, he's saved a weeks work each time he has to do this just with the small changes that have been made.
He's good with excel so I think we keep that as the front end but it looks a bit limited when you query the data.
Thanks, Bruce

 

[SkipVought]

OK, so you're querying to return necessary data and then massaging the data to fit it into the existing format.

You're generally on the right track. You might want to continue to figure out how the manual process works. Then turn on your macro recorder and do a manual process to generate code. Then customize. If you do, post in Forum707.

I've been there. What typically happens is, you can crutch the existing manual system for so long. Then you begin to realize that to do it right, you must scrap and redesign.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BRP250]

One step at a time. I've cut out some of the manual work. I'm jumping to the 'design new system' stage and trying to get the excel data into a sql db, updating from other sources and creating a crosstab sql view to read and graph/report in excel. I see a lot of little gotchas on the way. This's not the last you'll see of me.
Time and resources prevent me doing much else with this unfortunately.

Thanks for the help.
Bruce