Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Cairns, Queensland, Australia
    Posts
    885
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cut and paste from Access to Excel change format (XP (2002) 10.2614.2625)

    I've searched here and MS KB without success so hope I haven't missed something.

    I have a user who uses Access to enter the daily revenue then cuts and pastes the data to Excel. Excel tells us that the information is text, the cells are formatted as numeric. If I edit the contents of any cell, delete a character and re-enter the same character, the problem disappears for that cell.

    THe totalling of these cells and the COUNT show as zero until I do the edit trick.

    This used to work in Office 97. We are using the original database in Access.

    I just want the information to stay as numeric when we do the cut and paste.

    TIA.
    Granville

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cut and paste from Access to Excel change format (XP (2002) 10.2614.26

    This problem also occurs in previous versions of Excel. I don't know how to avoid it.

    There are extensive discussions on this subject in the Excel forum, see for instance the threads beginning with posts <!post=71410, 71410>71410<!/post> and <!post=114831, 114831>114831<!/post>.

    The methods to convert text to numeric mentioned most are:
    <UL><LI>Enter 1 in an empty cell
    Copy this cell to the clipboard
    Select the range with "text" cells
    Edit/Paste special...
    Select Multiply
    Click OK

    <LI>Select the range with "text" cells
    Data/Text to columns...
    Click Finish without selecting anything

    <LI>Create a macro

    Sub Text2Values()
    Dim xCell As Range
    With Selection
    .NumberFormat = "General" 'because text formatting still causes anomalous results
    .Replace What:=" ", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False
    End With
    For Each xCell In Selection
    xCell.Value = xCell.Value
    Next xCell
    End Sub

    and assign it to a toolbar button or keyboard shortcut.[/list]Neither of these methods will prevent the problem, but they make coping with it easier - selecting and editing each cell is not inviting.

  3. #3
    Lounger
    Join Date
    Apr 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cut and paste from Access to Excel change format (XP (2002) 10.2614.26

    We are just in the process of upgrading from Office 97 to Office XP and one of our users has reported the same problem to me. He doesn't believe this problem existed in prior versions of Excel and Access. I sent him the suggestions in this post and he was most unhappy at the prospect of using these workarounds. I did some further investigations and it appears that if you use the new Office Clipboard to do the paste (rather than CTL-V or Edit Paste), then it seems to retain the cell formats as numbers, currency and dates etc. To bring up the clipboard in Excel 2002 you use Edit Clipboard off the menu or press Ctl-C twice.

    Hope this helps you!!

  4. #4
    Lounger
    Join Date
    Apr 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cut and paste from Access to Excel change format (XP (2002) 10.2614.26

    I just received an alert of a new KB article that documents this and offers a fix. I haven't tried it out yet....

    Q328933 XL2002: Numbers That Are Copied From Access 2002 Paste As Text in Excel 2002

    web address is http://support.microsoft.com/default.aspx?...b;EN-US;Q328933

    Belinda

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Cut and paste from Access to Excel change format (XP (2002) 10.2614.26

    If pasting to excel won't work, have the Access user create an export file as a .csv to be imported into excel.

    Even better, another option you might try is create a report based on the dataset and then export the report data to excel. I believe this is an easy way to fix the problem indicated. We currently do this when someone needs the report results in excel and have not had any conversion problems.

    HTH
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •