• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 729
  • Last Modified:

PB6, Excel OLE and non-English characters

Life was easy when I used to fill my Excel worksheet only with English characters, like Sheet.Cells[1,1].Value="string value". But things changed when I tried to transfer some Russian characters (cp1251) from PB to Excel. Excel's cell looks like I don't have Russian font - letters with accents, etc - like international ASCII 128+ characters. My next step was to discover how Excel stores Russian strings in its cells. I tried to read cell's value from Excel and got totally unreadable crap with "undefined character" boxes. Furthermore, when I tried to transfer one Russian (1,1) value to another (3,3) (through PB), using Sheet.Cells[3,3].Value = Sheet.Cells[1,1].Value - I got another unreadable crap in Excel's cell 3,3 ! Value for (1,1) was, say, "test(eng)+test(rus)" and in (3,3) I got "test(eng)+crap(unknown)".

This stuff drives me insane. Some non-PB people gave me a clue to use win32api function called OemToAnsi in order to convert my strings to some form of Unicode or something. I declared this function like FUNCTION boolean OemToAnsi ( ref string src, ref string dst ) Library "USER32". During test run ...

boolean res

string str1
string str2

str1 = "A"
str2 = "B"

res = OemToAnsi( str1, str2 )

... application crashes with "Error calling external function OemToAnsi". I don't have so-called PowerBuilder-for-Unicode-edition, so I don't have any useful ideas about how to transfer my Russian strings from PB to Excel.

Please help, I'm giving all points that I've got.
0
NMi
Asked:
NMi
  • 5
  • 3
1 Solution
 
berXpertCommented:
Hi NMi,

your function crash becase OemToAnsi was used in Win 16 in a dll called keyboard, so if you are in Win16 change the "user32" for "keyboard" and it'll work.

In Win32 you use OemToCharA:

FUNCTION Long OemToChar( ref string src, ref string dst ) Library "User32" ALIAS FOR "OemToCharA"

With your code but now Long:

--------
Long res

string str1
string str2

str1 = "A"
str2 = "B"

res = OemToChar( str1, str2 )

MessageBox(str1, str2)


----------
I tested and application don't crash.

BTW the opposite function is



FUNCTION Long CharToOem( ref string src, ref string dst ) Library "User32" ALIAS FOR "CharToOemA"


Let me know if it works

BerX
0
 
NMiAuthor Commented:
Yeah, _this function_ seems to work. But I this that's not what I need. What I get from CharToOem function is some weird string str2 with the same length as str1 - that's absolutely not the case of Excel - I don't think that str2 gets converted to Unicode. When I see what Excel contains in its cells, it has length between Len and Len*2 of the string within the cell.

Furthermore, it's still a kind of miracle for me that when I simply try to transfer one cell to another (like in original message), I get crap instead of similar cell...
0
 
berXpertCommented:
I would like to help you more but my Windows is in spanish and the movement between cells doesn't change chars like in you sample, maybe if you send me a XLS with some chars I can try something else.

I did this:

ole_Excel.Application.Cells[3,3].Value = ole_Excel.Application.Cells[1,1].Value

On a cell with : "Hola á ü ÑñÁÚÜ"
and the result was as expected: "Hola á ü ÑñÁÚÜ"

I read that Russian characters need 1.7 bytes average to be represented while latinamerican need only 1.1 on a UTF8 format. Maybe you are losing 1 byte of information while the movement (or getting 1 extra).

I use a function to convert my string into UTF8 and get this:

"Hola á ü ÑñÁÚÜ"

That corresponds with what you said about lenght = len*2

I also read that Excel doest convert UTF8 but it need ISO-8859-1.

Sorry but maybe you wont be able to solve your problem.

BerX
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
NMiAuthor Commented:
WOW !!! I don't know what kind ot b*s*t is that !!!
I just can't get why do the contents of the target cell depend on its position !!! Check this out:

lole_Sheet.Cells[1,2].Value = lole_Sheet.Cells[1,1].Value
lole_Sheet.Cells[1,3].Value = lole_Sheet.Cells[1,1].Value
lole_Sheet.Cells[1,4].Value = lole_Sheet.Cells[1,1].Value
lole_Sheet.Cells[2,1].Value = lole_Sheet.Cells[1,1].Value
lole_Sheet.Cells[2,2].Value = lole_Sheet.Cells[1,1].Value
lole_Sheet.Cells[2,3].Value = lole_Sheet.Cells[1,1].Value
lole_Sheet.Cells[2,4].Value = lole_Sheet.Cells[1,1].Value

Guess what ? Do I get the same result in my cells ? I'm in deep shock !

http://nmi.ru/tmp/excel-pb.gif

As you can see, we've got original value in cell[1,1]. It is three Russian letters - in transcription that would be ABVabv. Its lenght equals to 6, no surprise. But what I get in other cells is 3 chars long ! Futhermore, the result is highly unstable and unpredictable - I can get another set of results the next time I run my application.
0
 
NMiAuthor Commented:
Cool ! My friend told me that the first thing to do is to upgrade to the latest maintenance release. After upgrading to 6.5 build 444, the problem has gone ! :))

Now I'll try to enter my own values to the cells...
0
 
NMiAuthor Commented:
... seems like Russian started to work since maintenance release 6.5 :) But anyhow - thanks for the support :)
0
 
NMiAuthor Commented:
Not a solution that I need, but still good advice ;)
0
 
berXpertCommented:
I'm glad you solve it!

Any time :0)
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now