EBCDIC碼與ASCII碼相互轉(zhuǎn)換VBA

1、A text file of data from a mainframe computer may be encoded in the EBCDIC character system. This isnot directly usable by VBA, which uses the ASCII character encoding system when reading and writing text files.This article provides a function to translate between two character encoding schemes and

2、some helper functions to build the translation table.可能對(duì)文本文件的大型機(jī)中的數(shù)據(jù)進(jìn)行編碼，EBCDIC字符系統(tǒng)中。這不是直接由VBA,使用ASCII字 符編碼系統(tǒng)讀取和寫(xiě)入文本文件時(shí)可用。本文提供了兩個(gè)的字符編碼方案和一些helper函數(shù)，生成翻譯表之間進(jìn)行轉(zhuǎn)換的函數(shù)。Notes Regarding Character SetsVBA supports UNICODE, which is a superset of ASCII. Only characters that fall into theASCII range (Chr(0) t

3、o Chr(255) will be translated.The translation tables supplied can be used to translate a string of text from the . English EBCDIC code page (CECP 037) character set to the ISO/ANSI ASCII character set and back again.The ISO/ANSI ASCII character set is used by Windows, but it is not the same as the I

4、BM PC OEM ASCII character set, although the lower 128 characters are identical.Because there are several variations of both the EBCDIC and ASCII character sets, especially for international use, feel free to add customized translation tables.Here are some notable differences in the ASCII-to-EBCDIC t

5、ranslation for HP, IBM (as documented in the IBM 3780 manual), and AT&T:有關(guān)字符集的筆記VBA支持UNICODE是ASCII的一個(gè)超集。只有屬于(將轉(zhuǎn)換為Chr(255) Chr(0)ASCII范圍的字符提供的轉(zhuǎn)換表可用于翻譯的美國(guó)英語(yǔ)EBCDIC代碼頁(yè)(CECP 037)字符集中的文本，以ISO/ANSI ASCII字符集字符串和反向切換。ISO/ANSI ASCII字符集使用的 Windows,但不是與 舊M 的PC OEM ASCII字符集相同 雖然較低的128個(gè)字符都相同。因?yàn)橛辛?EBCDIC和ASCII

6、字符集中的幾種變體尤其是對(duì)于國(guó)際的使用隨意添加自定 義的轉(zhuǎn)換表。下面是ASCII到EBCDIC翻譯為HP、(為記錄在 舊M 3780手冊(cè))，舊M和AT & T某 些顯著的差異：ASCII HP EBCDIC IBM EBCDIC AT&T EBCDIC!214F5A5A5B4A5BAD5D5A5DBD人5E5F5E5FThe major difference is that 5A represents "!" to IBM and AT&T, but "'' to HP. Other differences not list

7、ed resolve themselves in the range of non-printable characters.The functions are:主要區(qū)別是該5A表示"口BM 和AT & T ,但""HP至限不列出其他不同解決本身中的 非打印字符范圍。這些函數(shù)是:FunctionDescriptionTranslateConverts a string from one character encoding scheme to another. Requires a translation table as one of the argum

8、ents.ASCII_To_EBCDIC_TableReturns a string containing the translation table for converting an ASCII string to an EBCDIC string.EBCDIC_To_ASCII_TableReturns a string containing the translation table for converting an EBCDIC string to an ASCII string.HexToStrA helper function that converts a string of

9、 hexadecimal digits intothe actualcharacters they represent.The function source is as follows. The code can be pasted into any VBA Module:函數(shù)源如下所示?？蓪⒋a粘貼到任何VBA模塊中：Function Translate(ByVal InText As String, xlatTable As String) As String ''Uses a translation table to map InText from one charac

10、ter set to another. 'Dim Temp As String, I As Long Temp = Space$(Len(InText)For I = 1 To Len(InText)Mid$(Temp, I, 1) = Mid$(xlatTable, Asc(Mid$(InText, I, 1) + 1, 1) Next I Translate = TempEnd FunctionFunction ASCII_To_EBCDIC_Table() As String ''Returns the following table as a string fo

11、r use by the Translate 'function to translate an EBCDIC string to an ASCII-ISO/ANSI string.''00 01 02 03 37 2D 2E 2F 16 05 25 0B 0C 0D 0E 0F'10 11 12 13 3c 3D 32 26 18 19 3F 27 1C 1D 1E 1F'40 5A 7F 7B 5B 6c 50 7D 4D 5D 5c 4E 6B 60 4B 61'F0 F1 F2 F3 F4 F5 F6 F7 F8 F9 7A 5E 4C

12、7E 6E 6F'7c C1 C2 C3 C4 C5 C6 C7 C8 C9 D1 D2 D3 D4 D5 D6'D7 D8 D9 E2 E3 E4 E5 E6 E7 E8 E9 AD E0 BD 5F 6D'79 81 82 83 84 85 86 87 88 89 91 92 93 94 95 96'97 98 99 A2 A3 A4 A5 A6 A7 A8 A9 C0 4F D0 A1 07'20 21 22 23 24 15 06 17 28 29 2A 2B 2C 09 0A 1B'30 31 1A 33 34 35 36 08 38

13、39 3A 3B 04 14 3E E1'41 42 43 44 45 46 47 48 49 51 52 53 54 55 56 57'58 59 62 63 64 65 66 67 68 69 70 71 72 73 74 75'76 77 78 80 8A 8B 8C 8D 8E 8F 90 9A 9B 9C 9D 9E'9F A0 AA AB AC 4A AE AF B0 B1 B2 B3 B4 B5 B6 B7'B8 B9 BA BB BC 6A BE BF CA CB CC CD CE CF DA dB'DC DD DE DF EA

14、EB EC ED EE EF FA FB FC FD FE FF 'ASCII_To_EBCDIC_Table = _HexToStr("000D2E2F1605250B0C0D0E0F3c3D3F271C1D1E1F") & _HexToStr("405A7F7B5B6c507D4D5D5c4E6B604B61F0F1F2F3F4F5F6F7F8F97A5E4c7E6E6F") & _HexToStr("7CC1C2c3c4c5c6c7c8c9D1D2D3D4D5D6D7D8D9E2E3E4E5E6E7E8E9ADE0

15、BD5F6D") & _HexToStr("9899A2A3A4A5A6A7A8A9c04FD0A107") & _HexToStr("A2B2c090A1B30311A0838393A3B04143EE1") & _HexToStr("737475") & _HexToStr("8A8B8c8D8E8F909A9B9c9D9E9FA0AAABAC4AAEAFB0B1B2B3B4B5B6B7") & _HexToStr("B8B9BABBBC6ABEBFC

16、ACBCCCDCECFDADBDCDDDEDFEAEBECEDEEEFFAFBFCFDFEFF") End FunctionFunction EBCDIC_To_ASCII_Table() As String 'Returns the following table as a string for use by the Translate'function to traslate an EBCDIC string to an ASCII-ISO/ANSI string. ''00 01 02 03 9C 09 86 7F 97 8D 8E 0B 0C

17、0D 0E 0F .-.'10 11 12 13 9D 85 08 87 18 19 92 8F 1C 1D 1E 1F '.'80 81 82 83 84 0A 17 1B 88 89 8A 8B 8C 05 06 07 ?".%。.'90 91 16 93 94 95 96 04 98 99 9A 9B 14 15 9E 1A '.""-.(tm).'20 A0 A1 A2 A3 A4 A5 A6 A7 A8 D5 2E 3C 28 2B 7C .。£。¥ | § .<(+|&

18、#39;26 A9 AA AB AC AD AE AF B0 B1 21 24 2A 29 3B 5E &(c)a"(r) - 0 ± !$*);A'2D 2F B2 B3 B4 B5 B6 B7 B8 B9 E5 2C 25 5F 3E 3F -/23' 0 1.,%_>'BA BB BC BD BE BF C0 C1 C2 60 3A 23 40 27 3D 22 o"1/41/23/4.':#'="'C3 61 62 63 64 65 66 67 68 69 C4 C5 C6 C7 C8

19、 C9 .abcdefghi.'CA 6A 6B 6C 6D 6E 6F 70 71 72 CB CC CD CE CF D0 .jklmnopqr.'D1 7E 73 74 75 76 77 78 79 7A D2 D3 D4 5B D6 D7 .stuvwxyz.'D8 D9 DA DB DC DD DE DF E0 E1 E2 E3 E4 5D E6 E7 .'7B 41 42 43 44 45 46 47 48 49 E8 E9 EA EB EC ED ABCDEFGHI.'7D 4A 4B 4C 4D 4E 4F 50 51 52 EE EF

20、F0 F1 F2 F3 JKLMNOPQR.'5C 9F 53 54 55 56 57 58 59 5A F4 F5 F6 F7 F8 F9 .STUVWXYZ.'30 31 32 33 34 35 36 37 38 39 FA FB FC FD FE FF 09. 'EBCDIC To ASCII Table =HexToStr("000102039c09867F978D8E0B0C0D0E0F9D19928F1C1D1E1F") & _HexToStr("840A171B88898A8B8c098999A9B14159E1A&q

21、uot;) & _HexToStr("20A0A1A2A3A4A5A6A7A8D52E3c282B7c26A9AAABACADAEAFB0B121242A293B5E") & _ HexToStr("2D2FB2B3B4B5B6B7B8B9E52C255F3E3FBABBBCBDBEBFC0C1C2603A2340273D22") & _ HexToStr("C366869c4c5c6c7c8c9CA6A6B6c6D6E6F707172CBCCCDCECFD0") & _ HexToStr("D17E7778797AD2D3D45BD6D7D8D9DADBDCDDDEDFE0E1E2E3E45DE6E7") &