Paste from Access

When pasting an Access query results into an Excel 
worksheet the numbers are converted to text.  The best 
work around seems to paste special as CSV.  Is there a 
better solution?
0
anonymous (74717)
12/1/2003 8:32:57 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
399 Views

Similar Articles

[PageSpeed] 39

Debra Dalgleish posted a manual fix and a macro (from Jon Peltier):

http://groups.google.com/groups?threadm=3F4D3D5B.3020101%40contextures.com

Michael Wulk wrote:
> 
> When pasting an Access query results into an Excel
> worksheet the numbers are converted to text.  The best
> work around seems to paste special as CSV.  Is there a
> better solution?

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
12/2/2003 1:41:28 AM
You can continue to paste special as csv, or, do a normal paste, and 
then change the data back to numbers:

1. Select an empty cell on the worksheet
2. Choose Edit>Copy
3. Select the cells that you pasted from Access
4. Choose Edit>Paste Special
5. Select Add, click OK

If you do this frequently, you can use a macro to paste as csv:
'=======================
Sub PasteCSV()
        ActiveSheet.PasteSpecial Format:="Csv", _
          Link:=False, DisplayAsIcon:=False
End Sub
'========================

Or to convert the numbers:
'=============================
Sub ConvertToNumbers()
'adapted from code by Jon Peltier
   Cells(65535, 255).Copy
   Selection.PasteSpecial Paste:=xlPasteValues, _
       Operation:=xlPasteSpecialOperationAdd
   Selection.WrapText = False
   Selection.EntireColumn.AutoFit
End Sub
'=============================


Michael Wulk wrote:
> When pasting an Access query results into an Excel 
> worksheet the numbers are converted to text.  The best 
> work around seems to paste special as CSV.  Is there a 
> better solution?


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
12/2/2003 1:46:34 AM
Reply:

Similar Artilces:

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 ...

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 ...

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 ...

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 *...

"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...

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 ...

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...

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...

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 ...

Is there Access command to allow opening from specific location?
Is there a command or command stream in Access that will allow a database to be opened only from a specific file location? I would like to prevent people from opening a database from a network drive and limit opening only from the hard drive of the PC they are logged on to. This might be too much of a kludge ... It seems like it would be possible to add in some startup code in the back-end (on "a network drive") that looks for an indication that it's being started from a proper front-end, and shut it down right away if not. Then you'd add in some code in th...

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...

How Do I Overcome "Access Denied" Message
During a recent troubleshooting session with Microsoft, they fixed a problem regarding errors in the . NET Framework Programs. However, after the session was over, I noticed that Microsoft had left a number of directories on my hard drive with very long numbers, all related to reinstalling .NET or removing .NET. I was able to change the names of these directories, and thus determined that they were not used for anything, but were part of the debugging session when Microsoft took over control of my PC. When I tried to delete them, most of them were easily disposed of. However, in ...

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...

outlook pst file "access denide error"
I am trying to load the pst file from one computer to a new one. each time i use the export/import function I get to the point of importing the file to the new computer and I get an access denied message. since I own both machines how do I get my contacts and email to the new computer Thanks Did you move it to a CD? If so, then you need to Save it to your computer somewhere and then Right click on the saved file to bring up the properties. and remove the "Read Only" property attribute. PST files on a CD are READ ONLY. -- Nikki Peterson [MVP - Outlook] <anonymous@discussi...

When I copy a picture and paste it into Publisher it disappears
I have tried to copy a picture from my documents and paste it into Publisher and the picture disappears. What version of Publisher? What happens if you go to... Insert / Picture / From File? -- John Inzer "Cathy" <Cathy@discussions.microsoft.com> wrote in message news:EF2CA50C-78D9-47DB-8799-E9C3C9171038@microsoft.com... >I have tried to copy a picture from my documents and paste it into >Publisher > and the picture disappears. I am using Office Profession Edition 2003. I tried another picture and so far it is still there. Thanks. "John Inzer"...

Recover Passport Access code
I have a Microsoft Money file on my PC, but since then I have converted to a new PC and Cannot access my own file with the passport. I signed up for another passport ,but how can I gain access to my money file? So why didn't you use the old Passport? If you forgot the password go to the Passport customer service page and get it back. You file with a passport that no longer exists is like your house when you've lost all of the keys. You gotta break in. If this weren't the case, how protected would your file really be? "Dave" <dwalvers@comcast.net> wrote in mes...

Access or Excel?
Hi All, I've meddled with spreadsheets and databases for years, but mostly just for fun and curiosity. I now have a fairly large project at work, and I'm looking for advice regarding whether Access or Excel would be my best option. For starters, most of us at work, myself included, have Office 2007 installed, and have XP SP3. There are a few here still running 2003, so most of the work we do is in "compatibility mode". The nature of the work that we do is to collect biologic data (blood chemistries, hematology, body weights, food intake etc.) over periods ...

Data Access Page 12-07-09
I designed a Data Access Page. It works well at the office, but a user from the field said she got this error: "Data provider could not be initialized" and "Microsoft Jet database cannot open file. It is already open exclusively by another user or you need permission to view file". Can anyone help? I've seen a couple posts from you regarding DAP. I think you should consider gating away form that and take a look at this: http://www.hkvstore.com/aspreportmaker/ If you fee really motivated, try this: http://www.microsoft.com/express/vwd/ ASP Report Maker...

Paste and Paste Special
What are the differences between paste and paste special and are these two functions the same for XP software from different sources? e.g Word, Publisher, Ventura and Word Perfect? -- _ _________________________________________ / \._._ |_ _ _ /' Orpheus Internet Services \_/| |_)| |(/_|_|_> / 'Internet for Everyone' _______ | ___________./ http://www.orpheusinternet.co.uk ...

pasting the result of a search
TIA once again. I have a macro that will search a wide range of cells to find a particular piece of data. when it finds that data, i need the macro to copy that data exactly 5 cells to the right of where it finds it. i can find the data, copy the data, (so that the little ants are running around the cell) but i do not know how to paste it to a different location that is based upon the original location. i tried to use "offset" but it seems like it requires a starting range, and the starting range will be different each time the macro selects a different piece of data. Can I use...

OWA and Full Mailbox Access .... weird Send As
Hello i use Exchange 2003 SP2 I have 2 users : user1 and user2 user1 has got fullmailbox access on user2 (nothing more) If I use outlook : I connect with user1 and I send an email from user2 then it appears has : sent on behalf.... wich is ok If I use owa : I connect with user1 and then I add user2 just like this http://exchange/exchange/user2 I send an email .... and then it appears has : from user2 wich is a "bug" ... it should be the same behaviour. How can i solve this problem? In news:45337094$0$8606$426a74cc@news.free.fr, Stephane <steph@nospamamamamamam.net> ty...

Excel to Word : Paste special>Paste Link> Excel Chart Obj doesn't
When I copy a chart from an Excell 2007 file and paste it as: Paste Special > Paste link > Microsoft Office Excel Chart Object I get a blank white picture (with gridlines in middle and a red (x) in the top left corner). It does not show correctly. However, the link is working correctly. after troubleshooting: I noticed that if I paste the same chart with 2700 rows of data the chart shows properly. But when I go above the 2700 rows of data the chart does not display properly. when I pasted the chart with the original 47000 rows of data it was not diplaying proper...

Cannot access product key
I've got a problem in Outlook which I'm already reporting in the General section of the newsgroup. However, as we don't appear to be having much luck resolving it then I'm trying to contact Microsoft for online assistance. However, the support tool asks for the Product key from the Help, About screen. My problem is that I can't get into Outlook to get to the Help menu! The key they require isn't the one off the disk packaging, it's the converted number that Outlook uses once installed. Anyone got any suggestions? Well firstly if you cross posted, by ent...