Copy from AS400

[inkserious]

I often need to copy and paste values from an AS400 system. They are either positive or negative integers. However, when copying and pasting into an Excel spreadsheet they are copied as strings as opposed to numeric values. I have a macro that I've been working on, but it doesn't seem to function properly. Sometime it will work and other times it won't.

The postive integers would be of the format 3900
and the negative 3900-

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10"
Dim cell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each cell In Target
If Right(cell.Value, 1) = "-" Then
cell.Value = Left(cell.Value, Len(cell.Value) - 1) * -1
cell.NumberFormat = "0;<0>"
End If
Next cell
End If

ws_exit:
Application.EnableEvents = True
End Sub

Any ideas?

Thanks.

-ep

 

[SkipVought]

Hi,

What data type is the number on the mainframe?

Post an actual value like 123C or whatever.

Skip,

_[red][/red]

 

[SkipVought]

take a look at...

http://www.datamission.co.uk/AS400_Utilities1.html

Skip,

_[red][/red]

 

[inkserious]

Under preferences in my AS400 session, there was a cut/copy option that allowed my force leading +/-

All this time I was doing it the hard way...

Thanks all.

Regards,

-ep