vba 在excel/逗号和小数点分隔符中粘贴十进制数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28105731/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pasting decimal numbers in excel / comma and point decimal separator
提问by Kristof Mertens
I want to get data into excel using DDE. I get an new value every second from the DDE and use a macro to store every value in the next row. So for so good.
我想使用 DDE 将数据导入 excel。我每秒从 DDE 获取一个新值，并使用宏将每个值存储在下一行中。所以这么好。
Problem is the decimal separator.
问题是小数点分隔符。
I get from the DDE a value like -5.18834 Using the macro or pasting it into excel, the value shows -518.557 but the actual value is -518557. The decimal point gets wrongly interpreted by excel. When I paste the same value in notepad I get correct value i.e. -5.18557 I tried deviding the value by 10000 in excel, but some values are 5 numbers instead of 6, so then it's wrong again.
我从 DDE 得到一个类似 -5.18834 的值 使用宏或将其粘贴到 excel 中，该值显示 -518.557 但实际值为 -518557。小数点被excel错误解释。当我在记事本中粘贴相同的值时，我得到了正确的值，即 -5.18557 我尝试在 excel 中将值除以 10000，但有些值是 5 个数字而不是 6 个数字，所以又错了。
I tried formatting cells, changing decimal separator in excel's options, changed regional setting on my pc but none of those work. I also tried adding TextFileDecimalSeparator = "." in the macro (found on this website) but that doesn't work either. Processing the data after the macro has finished is not an option.
我尝试格式化单元格，更改 excel 选项中的小数点分隔符，更改我电脑上的区域设置，但这些都不起作用。我也尝试添加 TextFileDecimalSeparator = "." 在宏中（在本网站上找到），但这也不起作用。在宏完成后处理数据不是一种选择。
Thanks for any help.
谢谢你的帮助。
回答by Axel Richter
Your enumeration, of what you have tried already, seems to rule this out, but i would be very bewildered if the following not would work:
您已经尝试过的枚举似乎排除了这一点，但如果以下方法不起作用，我会感到非常困惑：
Application.DecimalSeparator = "."
Application.ThousandsSeparator = ","
Application.UseSystemSeparators = False
' here do your paste
Application.UseSystemSeparators = True
回答by Ludek Krula
I had a similar problem - decimal numbers that I passed with macro into cells were assumed by excel as text... So I tried exactly same procedures as well as you did...Didn't help... I tried even the solution mentioned above...it didn't help either. It's a great solution though. Unfortunately it wasn't useful in my case, macro simply ignored this and used other seperator instead. I tried many things and I felt desperate and then I came with this clumsy solution:
我有一个类似的问题 - 我用宏传递到单元格的十进制数字被 excel 假定为文本......所以我尝试了与你完全相同的程序......没有帮助......我什至尝试了解决方案上面提到...它也没有帮助。不过，这是一个很好的解决方案。不幸的是，它在我的情况下没有用，宏只是忽略了这一点，而是使用了其他分隔符。我尝试了很多事情，但我感到绝望，然后我提出了这个笨拙的解决方案：
Cells.Replace What:=",", Replacement:=".", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Macro ignored the setting for the decimal separator so I decided to replace given separator with right one. It works! Like I said it's clumsy solution but as long as it works...
宏忽略了小数点分隔符的设置，所以我决定用正确的分隔符替换给定的分隔符。有用！就像我说的这是笨拙的解决方案，但只要它有效......
回答by e-mre
There is a faster way without writing a macro. You can change separators through he Excel options.
有一种不用写宏的更快的方法。您可以通过 Excel 选项更改分隔符。
