Hi, folks,

trying to transfer data by copy and paste from Excel to an
input-field in the other window that contains a "mask" of a
mainframe dialogue (probably IMS/DC or CICS) makes the screen frozen.
Typing same letters into that mask is possible without any problems.

Can the "copy and paste" be done by an VBA-program? And translated
data same time "on the fly" form ASCII ? (Excel) to (probably) EBCDIC
(mainframe)
data format?

If VBA couldn't do that, is it possible to solve the problem by using
"scripting host" programming? If anyone is out there has some experiences
in this matter that would be great! Thanks for any hint. Best would be the
code
of a sample solution. :-)

Greetings
Peter




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.744 / Virus Database: 496 - Release Date: 24.08.2004

It can probably be done - can't really show code since it is too dependent on
your system. However, here is what you could do:

Create either a Worksheet_Change() event procedure or a CommandButton with a
_Click() event procedure that has code to do the following:
a) read the cell value you need to convert
b) apply the conversion routine (dependent on the coding scheme of the
system you are pasting to. You may need to write a VBA function that reads
each character and applies the code translations).
c) Use the AppActivate statement to activate your destination system app;
e.g. AppActivate "Mainframe"
(this assumes it is already open, otherwise Shell command can open it).
d) Use SendKeys statement to send any key combination needed to move the
cursor to the input field where the data is to be entered; e.g. SendKeys
"%E{TAB}{TAB}" (this send alt-E, and two tabs, as if to activate Edit
function then tab two fields - just an example)
e) Use SendKeys now to actually type in the data, e.g. SendKeys
ConvertedText (where ConvertedText would be the variable containing the
converted input text).

Excel VBA Help will give details on the AppActivate and SendKeys statements.
Warning: Timing the AppActivate and SendKeys statements can be problematic
at times, especially if other apps take control of Windows while you are
doing this. I have done similar things and most times it works fine - but if
user gets an incoming email notification at the wrong time strange things can
happen (since the SendKeys will work on the active app - in this case the
email notification instead of the destination app!) So this is possible but
needs to be tested and used with discretion... (OK, disclaimer done.)

Hope this gives you enough to go by to get started...
K Dales

"Peter Ostermann" wrote:

Hi, folks,

trying to transfer data by copy and paste from Excel to an
input-field in the other window that contains a "mask" of a
mainframe dialogue (probably IMS/DC or CICS) makes the screen frozen.
Typing same letters into that mask is possible without any problems.

Can the "copy and paste" be done by an VBA-program? And translated
data same time "on the fly" form ASCII ? (Excel) to (probably) EBCDIC
(mainframe)
data format?

If VBA couldn't do that, is it possible to solve the problem by using
"scripting host" programming? If anyone is out there has some experiences
in this matter that would be great! Thanks for any hint. Best would be the
code
of a sample solution. :-)

Greetings
Peter




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.744 / Virus Database: 496 - Release Date: 24.08.2004

"K Dales" wrote:

Create either a Worksheet_Change() event procedure or a CommandButton with
a
_Click() event procedure that has code to do the following:
a) read the cell value you need to convert
b) apply the conversion routine (dependent on the coding scheme of the
system you are pasting to. You may need to write a VBA function that
reads
each character and applies the code translations).

Hello K. Dales,

I like to start decent. I will create a button that copies
the active cell into the "background", then converts it
into EBCDIC.

After, I will position the curser manually into the right field
of the mainframe mask and then just "paste" there
using "CTRL - V"

If that ever would work, then I would go ahead later.

Can you help me with a code example just for the conversion to "EBCDIC "?
And say also , where the cell-content is stored, if it would have been just
copied by "CTRL - C"? How can I address this place by VBA for to
replace the content? Thanks.

Best Regards
Peter Ostermann



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.744 / Virus Database: 496 - Release Date: 24.08.2004

Hello Peter;

As for the conversion ASCII to EBCDIC, I don't work with
EBCDIC so not fluent with the character codes. If you
search the knowledge base for "ASCII EBCDIC conversion"
perhaps you may find some info or even a function you can
use.

If not, here is an idea: set up a worksheet (hidden if
desired - I will call it "ConvertCodes") that lists all
the EBCDIC codes in column (A) set up such that the row
number is the corresponding ASCII code (for example, the
ASCII code 65 is for "A", so in cell A65 put whatever the
numeric EBCDIC code for "A" is). Then you could set up an
easy conversion formula:

Public Function ConvertEBCDIC(ASCIIText As String) As
String

Dim Result As String, i As Integer, ECode As Byte

Result = ""

For i = 1 To Len(ASCIIText)

ECode = Asc(Mid(ASCIIText, i, 1))
Result = Result & Chr(Sheets("ConvertCodes").Range("A"
& ECode))

Next i

ConvertEBCDIC = Result

End Function

As for the other question: If the user has copied with
Ctrl-C, then the value is already stored in the clipboard
and you don't have to do anything but use the SendKeys
statement to do the Ctrl-V to paste (SendKeys "^V"). All
this occurs just as if it was being typed manually.

On the other hand, in my original reply I was suggesting a
method that did not require the user to have to remember
to enter the Ctrl-C. If you create a Command Button, you
can have VBA read the cell contents when the button is
pressed, store this value in a String variable, and then
use it with SendKeys to enter it in the mainframe app.
This will allow the user to automate the whole copy/paste
process with the click of the button, e.g:

Sub CommandButton1_Click()
Dim CopyTxt as String

CopyTxt = Range("InputCell").Value
AppActivate "Mainframe"

' Use SendKeys here to do any necessary menus/navigation
to prepare for input of data

SendKeys CopyTxt

End Sub

The code samples are just basic outlines but hope they
help.

K Dales

-----Original Message-----

"K Dales" wrote:

Create either a Worksheet_Change() event procedure or a
CommandButton with
a
_Click() event procedure that has code to do the
following:
a) read the cell value you need to convert
b) apply the conversion routine (dependent on the
coding scheme of the
system you are pasting to. You may need to write a VBA
function that
reads
each character and applies the code translations).

Hello K. Dales,

I like to start decent. I will create a button that copies
the active cell into the "background", then converts it
into EBCDIC.

After, I will position the curser manually into the right
field
of the mainframe mask and then just "paste" there
using "CTRL - V"

If that ever would work, then I would go ahead later.

Can you help me with a code example just for the
conversion to "EBCDIC "?
And say also , where the cell-content is stored, if it
would have been just
copied by "CTRL - C"? How can I address this place by
VBA for to
replace the content? Thanks.

Best Regards
Peter Ostermann



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.744 / Virus Database: 496 - Release Date:
24.08.2004


.

Hello K. Dales,

thanks a lot for engaging in the problem.
I your ideas will help me solving the task!

Regards
Peter

"K Dales" wrote:

Hello Peter;

As for the conversion ASCII to EBCDIC, I don't work with
EBCDIC so not fluent with the character codes. If you
search the knowledge base for "ASCII EBCDIC conversion"
perhaps you may find some info or even a function you can
use.

If not, here is an idea: set up a worksheet (hidden if
desired - I will call it "ConvertCodes") that lists all
the EBCDIC codes in column (A) set up such that the row
number is the corresponding ASCII code (for example, the
ASCII code 65 is for "A", so in cell A65 put whatever the
numeric EBCDIC code for "A" is). Then you could set up an
easy conversion formula:

Public Function ConvertEBCDIC(ASCIIText As String) As
String

Dim Result As String, i As Integer, ECode As Byte

Result = ""

For i = 1 To Len(ASCIIText)

ECode = Asc(Mid(ASCIIText, i, 1))
Result = Result & Chr(Sheets("ConvertCodes").Range("A"
& ECode))

Next i

ConvertEBCDIC = Result

End Function

As for the other question: If the user has copied with
Ctrl-C, then the value is already stored in the clipboard
and you don't have to do anything but use the SendKeys
statement to do the Ctrl-V to paste (SendKeys "^V"). All
this occurs just as if it was being typed manually.

On the other hand, in my original reply I was suggesting a
method that did not require the user to have to remember
to enter the Ctrl-C. If you create a Command Button, you
can have VBA read the cell contents when the button is
pressed, store this value in a String variable, and then
use it with SendKeys to enter it in the mainframe app.
This will allow the user to automate the whole copy/paste
process with the click of the button, e.g:

Sub CommandButton1_Click()
Dim CopyTxt as String

CopyTxt = Range("InputCell").Value
AppActivate "Mainframe"

' Use SendKeys here to do any necessary menus/navigation
to prepare for input of data

SendKeys CopyTxt

End Sub

The code samples are just basic outlines but hope they
help.

K Dales

-----Original Message-----

"K Dales" wrote:

Create either a Worksheet_Change() event procedure or a
CommandButton with
a
_Click() event procedure that has code to do the
following:
a) read the cell value you need to convert
b) apply the conversion routine (dependent on the
coding scheme of the
system you are pasting to. You may need to write a VBA
function that
reads
each character and applies the code translations).

Hello K. Dales,

I like to start decent. I will create a button that copies
the active cell into the "background", then converts it
into EBCDIC.

After, I will position the curser manually into the right
field
of the mainframe mask and then just "paste" there
using "CTRL - V"

If that ever would work, then I would go ahead later.

Can you help me with a code example just for the
conversion to "EBCDIC "?
And say also , where the cell-content is stored, if it
would have been just
copied by "CTRL - C"? How can I address this place by
VBA for to
replace the content? Thanks.

Best Regards
Peter Ostermann



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.744 / Virus Database: 496 - Release Date:
24.08.2004


.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.744 / Virus Database: 496 - Release Date: 24.08.2004