Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Problem w/ importing numbers (2002)

    I noticed that since we upgraded at work, many times when I paste numbers from access into excel, excel doesn't read them as numbers. I have to open the dialog to tell excel to convert them to numbers. Old versions of Excel were smart enough to know that a number is a number. How can I get it to accept numbers properly in this version?

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Problem w/ importing numbers (2002)

    Could you post an example? Two things to consider:

    default Style may be Text Format, General or Number would be better.

    create an empty Workbook with all the sheets' Format set as above, to save it as Book.xlt in ...templates.
    -John ... I float in liquid gardens
    UTC -7DS

  3. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Problem w/ importing numbers (2002)

    I tried changing the format to number, but for some reason, when the data is pasted from Access it changes the format to text. It works fine if I do a paste special and paste values only, but that's an extra mouse click or two. I used to be able to paste numbers from access without excel changing the format to text.
    I attached a file that shows the numbers as they come straight from access.
    Attached Files Attached Files

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem w/ importing numbers (2002)

    Is the CPProfile field in Access a text field or a number ?
    Francois

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem w/ importing numbers (2002)

    I know this is not the ideal solution, but, you can correct your data quickly if you make the following thing:
    In an empty cell you can put the number "1".
    Copy this cell.
    Highlight all your range of data, and make a Special Paste, selecting "multiply ".
    This procedure, will solve your problem

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Problem w/ importing numbers (2002)

    Those cells have been pasted as text. On any date or number, press F2 (edit mode), then press enter and the cell contents will be converted to numeric. To further confuse me, Excel 97 will not enter the format menu on any of those cells, and I can't see why.

    Have you installed the Paste Values button on your toolbar? It avoids that annoying menu click-through for Paste Special values. Go into View, Toolbars, Customize, Commands tab to install the button.

    There was an extensive thread about this problem some months back, and based on work from a number of other Loungers and a similar problem I have with a Crystal Reports import, I plagiarized the macro below:

    Sub Text2Values()
    Application.ScreenUpdating = False
    Dim rngNumberCells As Range
    Dim rng1stCol As Range
    Dim rngCell As Range
    With Selection
    .NumberFormat = "General"
    .Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByColumns
    End With
    On Error Resume Next 'errors if no numbers in selection
    Set rngNumberCells = _
    Selection.SpecialCells(xlCellTypeConstants, xlNumbers)
    If Not Err Then
    For Each rngCell In rngNumberCells
    rngCell.Value = rngCell.Value
    Next rngCell
    End If
    Err.Clear
    ' There is a bug in Selection.SpecialCells(xlCellTypeConstants, xlNumbers)
    ' where the first column is skipped
    Set rng1stCol = Selection.Columns(1).Address
    On Error Resume Next
    Set rngNumberCells = _
    rng1stCol.SpecialCells(xlCellTypeConstants, xlNumbers)
    If Not Err Then
    For Each rngCell In rngNumberCells
    rngCell.Value = rngCell.Value
    Next rngCell
    End If
    ActiveCell.Select
    Application.ScreenUpdating = True
    End Sub
    -John ... I float in liquid gardens
    UTC -7DS

  7. #7
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Problem w/ importing numbers (2002)

    Francois, yes the CPProfile is a number in access

    Servando, thanks but thats way more work than other solutions

    John, yes I have paste values on the toolbar. I wouldn't dream of running excel w/o that little icon on the toolbar. Now, call me an idiot, but just what am I supposed to do with that bit of code? I've used excel for years and years but never used a macro. Now I feel stupid.

  8. #8
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem w/ importing numbers (2002)

    <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

    If you are copying the same amount of data each time you could use an array formula to add the column
    {=SUM(VALUE(E6:E10))}

    HTH

    Peter

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Problem w/ importing numbers (2002)

    No reason to berate yourself, it's time you learned to create macros (and user defined functions), it's not too hard. Legare neatly lays out instructions for setting up a macro in personal.xls in this, this thread all have some thoughts on places to start. Feel free to ask questions on the Lounge, that's why it exists.
    -John ... I float in liquid gardens
    UTC -7DS

  10. #10
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Problem w/ importing numbers (2002)

    Thanks for not giving up on me John. And thanks for those links. It looks like I have lots to learn.
    I have questions on the code you posted above.
    How/where would I enter it?
    Will it solve the problem permanently to prevent excel from storing the numbers as text?

    I'm still checking out the other links, but I have a question on the first one.

    Legare's thread starts by saying:
    If you already have a Personal.xls, then do the following to add a macro to it:

    I don't have a personal.xls and he doesn't say what to do if you don't have that file already.

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem w/ importing numbers (2002)

    Hi catzbelize

    There's a fairly good description of how to set up a personal.xls file in steps 1 to 4 of <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=7333&Search=t rue&Forum=xl&Words=personal.xls&Match=And&Searchpa ge=1&Limit=99&Old=allposts&Main=6844>this</A> post.

    Hope this helps
    Gre

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem w/ importing numbers (2002)

    I explain the best way to create a Personal.xls <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=109343&page=& view=&sb=&o=&vc=1#Post109343>Here</A>.
    Legare Coleman

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Problem w/ importing numbers (2002)

    Hi again, and I assume you have read Unkamaunka's post, which is helpful except the referenced thread is pretty specific to XL97. You are using XL2002, and I don't know where 2002 normally keeps it's .xlstart directory, there's a further discussion in <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=73136>this</A> thread.

    But before you do so, and pardon me if you already know this, many folks run with personal.xls hidden and don't know that it already exists. So check Window, Unhide to be sure you don't have one. Then look for an .XLStart directory. If you have two, let's guess that the latest one is appropriate, and use the information in the above referenced threads to creat a personal.xls.

    Also, note that the code will not automatically fix your problem. You'll have to run it -each time- after selecting the cells which are not converting from text.

    Tell us what you find.
    -John ... I float in liquid gardens
    UTC -7DS

  14. #14
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Problem w/ importing numbers (2002)

    Wow, I can't believe how incredibly helpful you guys are. I'll see what I can do with your suggestions. I'll be reading up about VBA this week. I really feel that it's bug in Office. If the field is a number in Access, it should remain a number when you copy it to Excel.

  15. #15
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Problem w/ importing numbers (2002)

    OK, I now have a personal.xls file. It was extremely simple using Legare's last post. I tried putting John's plagerized code from above, but nothing happened to the cells when I executed the macro. I think I did it correctly, so I don't know why it didn't work.

    By far, the easiest solution to my problem is to use Paste Values. Period.

    I downloaded docs from the vbatutor site in one of the posts referenced from John and had fun playing around with VBA code last night. I had no idea how much fun it is. It brings back memories of programming Pascal back in college.

    I also tried adding Legare's Public Function GetFilename in the personal.xls macro workbk using the following code:
    Public Function GetFilename(iType As Integer) As String
    Select Case iType
    Case 1
    GetFilename = Trim(Left(ActiveWorkbook.Name, _
    InStr(ActiveWorkbook.Name, ".") - 1))
    Case 2
    GetFilename = ActiveWorkbook.Name
    Case 3
    GetFilename = ActiveWorkbook.FullName
    Case 4
    GetFilename = Trim(Left(ActiveWorkbook.FullName, _
    InStr(ActiveWorkbook.FullName, ".") - 1))
    End Select
    End Function

    But when I tried to use it, I just get #NAME? in the cell where I type the formula:
    =getfilename(1)
    Am I doing something wrong?


    However, the formula:
    =MID(CELL("Filename"),FIND("[",CELL("Filename"))+1,FIND(".",CELL("Filename" ))-FIND("[",CELL("Filename"))-1)
    works fine and looks like it could be handy, but I'd rather get the GetFilename function to work.

Page 1 of 2 12 LastLast

Posting Permissions

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