粘贴方法，其中“，"是小数点 [英] .Paste Method where `,` is the decimal mark

问题描述

我正在从SAP获取数据以通过VBA粘贴到我的Excel工作表中.(还有其他导出数据的方法，但这只是我的一种方法.)

数据通过SAP放入Windows剪贴板.然后将其放入最新的Excel工作表中(请参见 Sub importStuff ，然后进行格式化，请参见 Sub divData ).粘贴后看起来像这样(包括空格):

|FOO:酒吧|360.000 |

在我调用 divData 后，它被分为两个单独的列.(这就是我想要的格式)

[SOME_ID:SOME_NAME] [360.000]

有时Excel格式将 360.000 设置为 360,000 = 360 .仅对于以 0 结尾的值，才会发生这种情况.因此 360.000 设置为 360 ， 312.312.001.800 设置为 312.312.001,80 .

我正在使用德语版本的Excel 14.0.7166.5000(32位).因此，.用于数字分组，，是小数点.

这是我的代码

  Sub importStuff()dBegin = wsUeb.Range("BeginPlan")dEnd = wsUeb.Range("EndPlan")'很多'的'其他'SAP资料SAP_Session.findById("wnd [0]/usr/tabsTABSTRIP_MYTAB/tabpPUSH4/ssub％_SUBSCREEN_MYTAB:ZCO_SUSAETZE_NEW:0400/ctxtP_LAYOUT").Text ="/ZL_UMSPIEXP"SAP_Session.findById("wnd [0]/tbar [1]/btn [8]").按SAP_Session.findById("wnd [0]/tbar [1]/btn [45]").pressSAP_Session.findById("wnd [1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG [4,0]").选择SAP_Session.findById("wnd [1]/tbar [0]/btn [0]").按ActiveWorkbook.Sheets.Add After:=工作表(Worksheets.Count)工作表(Worksheets.Count).PasteWorksheets(Worksheets.Count).Name ="Plan-Umsaetze"&分贝-"&结束呼叫divData结束子子divData()ActiveSheet.Columns("A:A").TextToColumns _数据类型:= xlDelimited，_TextQualifier:= xlTextQualifierNone，_ConsecutiveDelimiter:=否，_其他:=是，_OtherChar:="|"结束子 

这是怎么回事.

1. 我打开工作簿
2. 我叫 importStuff
3. 之后我调用 divData ，无论是否在 importStuff 中调用
都没关系
4. 一切都很好
5. 我再次致电 importStuff
6. 我根本不打 divData (不管我是否打通)
7. 我得到了错误的值

但是，如果我只按 Ctrl + v ，则可以正确插入值(请参见4.).

我无法通过Windows Shell调用 Ctrl + v ，因为新工作表对用户不可见.

解决方案

这只是Jochen的内容，我的评论是作为答案发布的.今天我遇到了同样的问题，这就是为什么我没看过这篇文章.

我将列格式化为文本，导入了数据，将格式更改为数字，然后将每个值乘以1.

I'm grabbing data from SAP to paste into my Excel sheet via VBA. (There are other methods for exporting data, but this is the one I am limited to.)

The data is put into the Windows-Clipboard via SAP. Afterwards it is put into the newest Excel-Worksheet (see Sub importStuff and then formatted see Sub divData) The data is delimited with pipes | and after pasting it looks like this (including the blanks):

| FOO: BAR | 360.000 |

After I Call divData it is split into two separate columns. (this how I want it to be formatted)

[SOME_ID: SOME_NAME][360.000]

Sometimes Excel formats 360.000 to 360,000 = 360. This only happens for values that end in a 0. So 360.000 is formatted to 360, 312.312.001.800 would be formatted to 312.312.001,80.

I'm using a German version of Excel 14.0.7166.5000 (32-Bit). So a . is for digit grouping, a , is the decimal mark.

This my code

Sub importStuff()

dBegin = wsUeb.Range("BeginPlan")
dEnd = wsUeb.Range("EndPlan")

'lots 
'of 
'other 
'SAP-Stuff

SAP_Session.findById("wnd[0]/usr/tabsTABSTRIP_MYTAB/tabpPUSH4/ssub%_SUBSCREEN_MYTAB:ZCO_SUSAETZE_NEW:0400/ctxtP_LAYOUT").Text = "/ZL_UMSPIEXP"
    SAP_Session.findById("wnd[0]/tbar[1]/btn[8]").press
    SAP_Session.findById("wnd[0]/tbar[1]/btn[45]").press
    SAP_Session.findById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[4,0]").Select
    SAP_Session.findById("wnd[1]/tbar[0]/btn[0]").press

    ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
    Worksheets(Worksheets.Count).Paste
    Worksheets(Worksheets.Count).Name = "Plan-Umsaetze " & dBegin & " - " & dEnd

Call divData

End Sub

Sub divData()

ActiveSheet.Columns("A:A").TextToColumns _
DataType:=xlDelimited, _
TextQualifier:=xlTextQualifierNone, _
ConsecutiveDelimiter:=False, _
Other:=True, _
OtherChar:="|"

End Sub

Here is what happens.

1. I open the workbook
2. I call importStuff
3. I call divData afterwards, doesn't matter if it is called within importStuff or not
4. everything is fine
5. I call importStuff again
6. I don't call divData at all (doesn't matter if I do)
7. I get erroneous values

But if I just press Ctrl+v the values get inserted just fine (see 4.)).

I can't call Ctrl+v via Windows Shell, because the new worksheet is invisible for the user.

解决方案

This is just Jochen's and my comment posted as an answer. I had the same issue today, that's why I necro'd this post.

I formatted the column to text, imported the data, changed the format back to numbers and then multiplied each value with 1. That did the trick.

这篇关于粘贴方法，其中“，"是小数点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！