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

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?
0
Buehner (1)
11/3/2004 10:08:07 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
566 Views

Similar Articles

[PageSpeed] 44

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.

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"James Buehner" <James Buehner@discussions.microsoft.com> wrote in message 
news:5EF2D516-76A8-47B0-9E96-E72169E0FE87@microsoft.com...
> 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?


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.788 / Virus Database: 533 - Release Date: 01/11/2004 


0
ken.wright (2489)
11/3/2004 10:19:33 PM
You may use  MSQuery  see here http://oraclevsmicrosoft.blogspot.com

"James Buehner" <James Buehner@discussions.microsoft.com> wrote in message news:<5EF2D516-76A8-47B0-9E96-E72169E0FE87@microsoft.com>...
> 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?
0
mboizeau1 (1)
11/4/2004 11:59:03 AM
I'm not sure if this is the problem--but you may want to look at Debra
Dalgleish's site:

http://www.contextures.com/xlDataEntry03.html

Especially:  http://www.contextures.com/xlDataEntry03.html#SP3

James Buehner wrote:
> 
> 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?

-- 

Dave Peterson
ec35720@netscape.com
0
ec357208423 (183)
11/4/2004 11:50:02 PM
Reply:

Similar Artilces:

Subform combobox won't populate from linked table
My Access 2003 database is shared across a network using a front end for forms and a back end for data. After a recent upgrade of hardware I am having problems with one desktop pc that will load the subform except that one combo box (out of many) remains blank. The combo box like all the others in the form is using data from linked tables. I have refreshed the link for that table for that machine but have been unable to get the combo to populate with data. I have also tried it with different user templates using full Access install and Access runtime without results. Does an...

excel emailed as the body of email loses formating
Now when I send an excel spreadsheet as the body of the email instead of as an attachement it losses the formating ie. lines font alignment. If anyone has a solution for this please help............ Thank you, Earl p.s. I believe it started after a auto update from MS. It has affected all the computers at my work even though they have diffrent versions of windows, excel, and outlook. ...

Paste special treats cells as a picture
I just got a new computer, and a newer version of excel, which is great, but whenever I try to copy from one file to another, my paste special function treats the copied cells as a picture. Instead of the normal paste special popup, where it asks me how I want the data to come out, I get a different popup that shows the source as a "Microsoft Office Excel Worksheet Object." It asks me what format I want the picture to come out as, and gives me a list of different file formats. If I try to just paste a link, it puts a picture of the other file on the new one. I talked to so...

Cannot access more than 250 items in a folder
My company has a custom Outlook form for handling vacation and sick time requests. The form has fields for start date/time, end date/time, name, etc. I'm trying to write some code that will process a bunch of saved requests and display some totals, but I'm having trouble. The code simply stops working after about the 250th item in a folder. Any attempt to get at the custom properties of any item after the 250th is a blank. Here's some very simple code I wrote to illustrate the problem. It loops through each saved item and checks if the "requestor" property contains ...

how do i plot this kind of data using excel
need a help in plotting data presented in class form, e.g; variables for A in condition 1 ranges from 67-89, in condition 2they range from 52-67, in condition 3; 65-87, in conditon 4, 62-83. Variables for B in condition 1 60-77,in condition 2, 50-64, in condition 3, 54-73, in condition 4 51-70 Tamm, Try setting your data up like this: Low High A1 67 89 B1 60 77 A2 52 67 B2 50 64 A3 65 87 B3 54 73 A4 62 83 B4 51 70 Then, plot it using either a clustered column chart or a stacked column chart. ---- Regards, John Mansfield http://www.pdbook.com "TAMM" wrote: > need a he...

Copy and paste formula; Value pastes instead
Excel 2002 I've tried starting several worksheets, checking formats, pasting special (formula only). For some reason the formula actually copies OK by looking at the formula bar, but the value in the cell is identical to the value of the copied cell.Incorrect. Tried even the simplest formulas with no joy. BK ...

Will excel 2003 format a cell to the year?
It seems like it should be obvious, but I am finding it impossible to get excel to do a date format that recognizes a year. Is there some way? I typed a date in A1 (09/28/2006) and used format|Cells|number tab|custom category|yyyy with my USA settings. kateofmd@msn.com wrote: > > It seems like it should be obvious, but I am finding it impossible to > get excel to do a date format that recognizes a year. Is there some > way? -- Dave Peterson ...

Using Outlook 2003 Lotus Connector
I use Outlook XP and have it configured to use the Lotus Server connector and it works great. Recently I tried to config Outlook 2003 to use the Lotus Connector that is built into Outlook2003 and it fails everytime. Outlook loads but then closes and says the connector failed. Anyone have any ideas? That connector that looks built in is leftover cruft from your Outlook 2002 isntall. Ignore that. You need to wait for the Outlook 2003 connector, which is supposedly in beta now. Until then, IMAP/LDAP is your buddy -- sans calendar, of course. Cheers. Sander wrote: > I use Outlook XP ...

access
��� ����� access? ��� ���� ������������ access? __________ Information from ESET NOD32 Antivirus, version of virus signature database 5167 (20100602) __________ The message was checked by ESET NOD32 Antivirus. http://www.esetnod32.ru/.ml ...

Is there an Excel formula to round a date to the end of the month
User enters a specific date, I'd like to round that entry to the end of the month With a date in A1: =DATE(YEAR(A1),MONTH(A1)+1,0) step into the next month and then back one day. -- Gary''s Student - gsnu201003 "Siralec" wrote: > User enters a specific date, I'd like to round that entry to the end of the > month Another way... This requires the Analysis ToolPak add-in be installed for Excel versions prior to Excel 2007. A1 = some date =EOMONTH(A1,0) Format as Date -- Biff Microsoft Excel MVP "Siralec" &...

PLEASE HELP: automate process to import tables from notepad to acc
Hi All, Is there a way to import tables that are in text file (notepad) into access automatically? at a specified time? How can I do this? For eg: I have a location where I receive 10-15 tables in text format (notepad) and I want to import all those tables in access (access.2007) at a specified time. Can this be automated? Thanks in advance. On Thu, 29 Apr 2010 10:15:01 -0700, sam <sam@discussions.microsoft.com> wrote: >Hi All, > >Is there a way to import tables that are in text file (notepad) into access >automatically? at a specified time? &g...

Cannot access CRM
We have just migrated from Groupwise to Exchange 2003 as part of our move to CRM. The migration had some issues but those have been resolved. There is one user whose mailbox was deleted and recreated as part of the troubleshooting. His Exchange account appears to be functioning correctly now. The problem is the user is now unable to access the CRM website, whereas he could before the migration. I discovered the problem when trying to install the CRM Sales For Outlook client on his workstation. When I was prompted to enter the URL of the CRM website, I received an error message saying &q...

Unable to upgrade to Access 2000 from Access 97 and preserver Access 97
Hello, I have a workstation running W2K Service pack 4. On the machine is Access 97. When I load Access 2000 to a unique directory it skips the part on asking me to preserve Access 97. After the install 97 is removed. I removed Access 2000 and installed Access 97 again. I tried to install Access 2000 into the same directory as 97 and it still skips that step of asking me to preserve. Anyone know if this is a registry setting that is having me skip this? I tried both an upgrade version of Access 2000 and a full version of Access 2000. Thanks, Michael Paniak Hi Michael If you do a *...

RegisterBluetoothComPort function question in bluetooth test
Hi: we have a MIPSII BSP and board with UART 0,1,2,3 BSP uart driver define(nxc26uart.reg) and suport physical com port 1,2,3,4 i take reference from : http://vinoth-vinothblog.blogspot.com/2008/10/enabling-bluetooth-serial-port-profile.html and AP sample c:\Wince600\PUBLIC\COMMON\SDK\SAMPLES\BLUETOOTH\COMTEST\comtest.cxx then write an AP to simulate blooth manager. at the call h=RegisterBluetoothComPort(L"COM",index,&pp));or h=RegisterDevice(L"COM",index,L"btd.dll",(DWORD)&pp); h return NULL ,index=1,2,3,4 h return handle, if index...

Excel
Can someone please help me. I have been trying to figure this one out all day. It seems so simple, I guess I'm brain dead. I'm trying to write a formula that will return results for the folowing example: aaa A A B B A bbb C A B B C ccc A A B B B aaa C C A A B bbb C A B B C ccc A A C C A I need a formula that will count the total number of times each of the colum results (A,B and C) appear for e...

Searching against disabled users using Advanced Find
I am trying to search for leads using Advanced Find where owner=disabled user. We need to reassign all of the leads owned by a terminated user to a new user. When selecting owner as a search parameter, disabled users do not show in the lookup. This is also the case when trying to search against the created by and modified by fields. Is this simply a limitation of advanced find or is there a selection option i am missing? If so, any workarounds anyone can think of other than temporarily enabling the user? Thanks in advance. Hi Try using the FilteredViews to build a report to show the l...

"Paste Special" with the right click mouse
I would like to have the option of "Paste Special" with the right click mouse in all microsoft office suite. Thanks, ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=de847738-df56-4...

Delete row in table by code
I have a table named Uploaded Documents that is created by a code that was developed by someone that used to work here. I have no coding experience so need some help. I need to add something to the end of his code that would delete any row where in the field SVBrand it contains either ## or @@ anywhere in that field. Any help would be apprectiated. Put this in the code and it will delete those rows: strSQL = "DELETE * FROM [Uploaded Documents] WHERE svBrand = '##' OR svBrand '@@';" CurrentDb.Execute(strSQL), dbFailOnError -- Dave Hargis, Microsoft A...

Clipboard pasting
I copy something to the clipboard using Publisher 2007 and the computer tells me it is in the clipboard; however, when I go to paste that information into another document, Publisher acts like it is not there. I then need to close the document I wish to paste it into, reopen it, and then, like magic, there is the information in the clipboard and I am able to paste it. UGH!!!!!! -- Ginger Christenson Open the Office Clipboard in Publisher. Edit, Office Clipboard. What program are you pasting to? If it is an Office program, open the Office Clipboard in that program too. -- Mary Sauer ...

Access 2007 Disable Mode
When I try to run an update query or a make table query, the application wont run and gives me the following erro message. "The action or event has been blocked by disable mode." How can I disable the disable mode? This should help: http://www.btabdevelopment.com/main/QuickTutorials/A2K7Howtoenablecodeandmacros/tabid/57/Default.aspx -- Bob Larson Access World Forums Super Moderator Utter Access VIP Tutorials at http://www.btabdevelopment.com If my post was helpful to you, please rate the post. __________________________________ "FredM1" wrote: > When I try to r...

Copy/Paste loop
I have a form on one sheet in excel, on the next sheet i have mad different coloums with different headings. I want the data from th form to be copyed to each heading e.g. name in form is copyed to unde the coloum with title name. So each time a user fills in there details he/she clicks submit an there data is copyed to the next page, like a small database. Is there a macro that can do this as i keep getting different error each time. I want this to continue in a loop Please help!!:confused -- Message posted from http://www.ExcelForum.com This might get you started: Option Explicit Opti...

Cannot import send/receive dates for emails stored in Access (.mdb) file
I have some mdb (Access format) database files with thousands of emails extracted from couple of Yahoo groups forums. I'd like to import these into my Outlook 2003 PST so that I can search and read them offline. The mdb files contain all the fields necessary for a proper email import, including from_name from_email subject receive_date receive_time message (HTML format) When I use the Outlook "Import and Export Wizard", selecting Access file type, etc., all appears fine. However, after the import completes, in Outlook there is no date information populated under either sent ...

How do I remove links to external excel files
I need to remove the links to another excel file from my new workbook. How can I accomplish this without re- creating the spreadsheet from scratch? Thank you. TinaS Remove links means convert those formulas to values? If you can find them, just copy|paste special|values. But they can be difficult to find. I'd use Bill Manville's addin to help find the pesky ones. Findlink.zip from http://www.BMSLtd.co.uk/mvp (And it's easy to use. TinaS wrote: > > I need to remove the links to another excel file from my > new workbook. How can I accomplish this without re- >...

Pasting formulas advances cell reference
Hi When i paste special from one cell into another, it advances the cell reference and i just want an exact match i.e. A1 formula ='Feb 2010'!C18 but when i paste it into another cell it pastes it as ='Feb 2010'!D18 The C changes to D, how do i stop this happening? Thanks for any help in advance Derek Use absolute referencing. Below are the different reference styles. A1 Relative referencing. Both column and row will change if you copy or drag the formula. $A1 The column reference is fixed and will not change A$1 The row reference is fixed and wil...

Cannot e-mail an Access report using Excel
When I want to e-mail an Access report in Excel, that option is grayed out. It worked with 2003 but not with 2007. I searched everywhere for an answer but no solution. Does anyone know how to make this work? You are very much in the wrong newsgroup. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "mtnman" <mtnman@discussions.microsoft.com> wrote in message news:30B14FA1-EAAD-4032-9C21-50DB7E2CAB13@microsoft.com... > When I want to e-mail an Access report in Excel, that option is grayed out. > It worked with...