粘贴方法不正确/不同于“Ctrl + v” [英] .Paste Method does not work correctly / behaves differently than "Ctrl + v"
问题描述
我想做什么?
我正在从SAP获取数据,并希望通过vba将其粘贴到我的excel表中。 (还有其他导出数据的方法,但这是我所限制的。)数据通过SAP放入Windows剪贴板中。之后它被放入最新的excel-Worksheet(参见 Sub importStuff
然后格式化(参见 Sub divData
)数据用管道 |
分隔,粘贴后看起来像这样(包括空格):
| FOO:BAR | 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)
'lot
'
'其他
'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])按
SAP_Session.findById(wnd [0 ] / tbar [1] / btn [45])。按
SAP_Session.findById(wnd [1] / usr / subSUBSCREEN_STEPLOOP:SAPLSPO5:0150 / sub:SAPLSPO5:0150 / radSPOPLI-SELFLAG [ ])。选择
SAP_Session.findById(wnd [1] / tbar [0] / btn [0])。按
ActiveWorkbook.Sheets.Add After:= Worksheets Worksheets.Count)
工作表(Worksheets.Count).Paste
工作表(Worksheets.Count).Name =Plan-Umsaetze& dBegin& - & dEnd
调用divData
End Sub
Sub divData()
ActiveSheet.Columns(A:A ).TextToColumns _
DataType:= xlDelimited,_
TextQualifier:= xlTextQualifierNone,_
ConsecutiveDelimiter:= False,_
其他:= True,_
OtherChar: = |
End Sub
现在这里发生了什么。
- 我打开工作簿
- 我调用
importStuff
li>
- 我以后调用
divData
,如果在importStuff
或者不是 - 一切都很好
- 我再次致电
importStuff
- 我不会调用
divData
(如果我没有关系) - 我得到错误的值
但是如果我只是按 Ctrl + v
(见4))。
我不能通过Windows shell调用 Ctrl + v
,因为新的
Plase help!
这只是Jochen的,我的评论作为答案发表。我今天有同样的问题,这就是为什么我不知道这个帖子。
我将列格式化为文本,导入数据,将格式更改为数字,然后将每个值乘以1。这样做的伎俩。
I've seen a lot of weird things happening in excel, but this is probably the weirdest.
What do I want to do?
I'm grabbing data from SAP and want to paste it 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]
But(!) sometimes the format of the numbers get screwed up and 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
Now here is what happens.
- I open the workbook
- I call
importStuff
- I call
divData
afterwards, doesn't matter if it is called withinimportStuff
or not - everything is fine
- I call
importStuff
again - I don't call
divData
at all (doesn't matter if I do) - 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 actual user.
Plase help!
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.
这篇关于粘贴方法不正确/不同于“Ctrl + v”的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!