Problem w/ importing numbers (2002)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Hi catzbelize

There’s a fairly good description of how to set up a personal.xls file in steps 1 to 4 of this post.

Hope this helps

Reply | Quote

I explain the best way to create a Personal.xls Here.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

The macro I posted was waaaay too specific to the data I have problems with. This simpler version should work. See also the attachment.

Sub Txt2Val()
Application.ScreenUpdating = False
Dim rngCell As Range
With Selection
.NumberFormat = “General”
End With
For Each rngCell In Selection
rngCell.Value = rngCell.Value
Next rngCell
ActiveCell.Select
Application.ScreenUpdating = True
End Sub

Reply | Quote

To use a function from Personal.xls you must specify that this is where the function is located. Like this:

=PERSONAL.XLS!GetFilename(2)

Reply | Quote

While we are trying to drag you into VBA land, it occurs to me that there may be a better way to solve your problem. I’m no Access guru, but I have written a number of Access queries, and I use those to export data from Access to Excel File format for further analysis. So you may want to look at writing a query and exporting from Access to Excel, rather than cut-&-paste. After you design the query in Access, you can export by either (Office 97 menus, your mileage may vary):

1. Clicking the Access Toolbar Drop-Down Item which includes the options of
– Merge it with MSWord
– Publish it with MSWord
– Analyse it with MSExcel
… and select appropriate options, but for unknown reasons that process runs very slowly on my machine, so I usually:

2. Right click the specific Query Icon, select Save As/Export, To External Database, Save As File Type Excel, etc.

Once you get this up and running it’s very quick.

Reply | Quote

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

Reply | Quote

There was an extensive thread about this problem some months back…

And I remember it – since I asked the question and got a lot of great responses – the link is here.

Reply | Quote

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

Reply | Quote