Wrong data format?
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 |
Wrong data format?
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 |
Wrong data format?
"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 |
Wrong data format?
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 . |
Wrong data format?
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 |
All times are GMT +1. The time now is 04:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com