.Paste Method does not work correctly / behaves di

Question

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.

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 actual user.

Plase help!

Answer 1

you could set Range.numberFormat

or use Range.PasteSpecial( [xlPasteTypes], [xlPasteOperation]), doc here, particularly xlPasteValuesAndNumberFormats or xlPasteSpecialOperationMultiply

Answer 2

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.