Write Line problem when writing simplified chinese characters

[Guest]

Hi All,

I am facing a problem that when using Excel to write worksheet value to text
file, the process failure if the cell vlaue has simplified chinese characters.

I am using Win 2000 Prof (Traditional Chinese version) and MS Office 2000
(Traditional Chinese version), Anyone can help? Many thanks.

----- Sample Code -----
Public Sub test()

Dim fso As New FileSystemObject
Dim ts As TextStream
Set ts = fso.CreateTextFile("c:\output.txt")

Dim wrkSheet As Worksheet
Dim rngRange As Excel.Range

Set wrkSheet = ThisWorkbook.Sheets.Item(1)
Set rngRange = wrkSheet.UsedRange

Dim strFieldValue As String

strFieldValue = rngRange.Cells(1, 1)

ts.WriteLine (strFieldValue)

End Sub

The cell(1,1) contains value "财产"

 

[Gareth]

Right of the bat I should confess I don't speak Chinese or have any
experience of handling it. I've played around with Arabic though so
maybe that had similar problems:

Which line does your procedure fail at?
strFieldValue = rngRange.Cells(1, 1)
or
ts.WriteLine (strFieldValue)

What is the error message?

How many characters are there in the simplified chinese characterset?
More than 256 say and therefore we need to be writing in unicode - or
does "simple" imply this is a single byte character set?

 

[Guest]

Thx reply. The exception raise on row

ts.WriteLine (strFieldValue)

the msg is "Invalid Procedure Call or argument"

Can u tell me how to "writing in unicode"?

Cause I check that Excel cannot resolve all of the simplied chinese
character and so only show the "?" for such unknown characters?

Many Thx.

 

[Gareth]

Hi,

I don't know why you get the error I'm afraid - it *might* be something
to do with the char set it might not.

Below I have adapted your code to write in unicode. I have also changed
it to create two files: one using FSO and the other using standard file
I/O. They both give the same results but I thought using the latter
method might resolve your problem anyway.

Note, when writing in unicode, EVERYTHING HAS TO BE UNICODE (excuse the
shouting). You will see below how I have prevented VBA from writing any
carriage returns and line feeds and instead written them myself - in
unicode. Likewise should you need tabs or commas in your file format,
don't forget to convert them to unicode too. (It's easily forgotten.)

I hope this helps. I would be interested to know if this resolves the
problem.

Good luck.
Gareth

Public Sub test()

Dim fso As New FileSystemObject
Dim ts As TextStream
Dim wrkSheet As Worksheet
Dim rngRange As Excel.Range
Dim strFieldValue As String
Dim F As Integer
Dim myCRLF As String

'make a string with a CRLF (CHR13&10) as unicode
myCRLF = StrConv(vbCrLf, vbUnicode)

Set ts = fso.CreateTextFile("c:\output1.txt")

'open a second file (to show different method)
F = FreeFile
Open "c:\output2.txt" For Output As #F

Set wrkSheet = ThisWorkbook.Sheets(1)
Set rngRange = wrkSheet.UsedRange


'You don't *need* to load the range into a string.
'You could just write the cells thus:
'ts.Write (StrConv(rngRange.Cells(1, 1), vbUnicode))
'but I left it as it was

strFieldValue = StrConv(rngRange.Cells(1, 1), vbUnicode)

'Notice we use 'write' rather than 'writeline' - to
'prevent it inserting a single byte CR and LF
ts.Write (strFieldValue & myCRLF)

'same again for the other file
Print #F, strFieldValue & myCRLF; 'the ';' stops it
'printing a vbCRLF


Close #F

End Sub

 

[Guest]

Hi,

Thanks for your reply. I have tried the 2nd method and no exception would be
thrown.

However after the strconv function, the cell vlaue is translated into
special characters. It is found that the cell value (refer from
gRange.Cells(1, 1)) is not the same initially. I wonder the original value is
in GB format.

As MS Excel can display the character which is Simplified Chinese, is it
possible to handle the same value in VBA by some other functions?

Many Thanks!!

 

[Gareth]

Hi,

I wonder if some different conversion is required from GB to Unicode. I
notice in VB there appears to be specific support for Simplified Chinese
e.g.

StrConv(strChinese, VbStrConv.TraditionalChinese)

But these don't appear to work in VBA - or at least not my installation.
Maybe it would be ok on an Excel with Chinese support. I'm afraid I
don't know anything about GB :-(

YOu might like to try removing the unicode conversion to see what
happens - it's nothing I can test on my machine though.

Good luck,
Gareth

gb simplified chinese excel support

 

[Guest]

Thanks Gareth.

Do anyone know any reference web site or information for handling Simplified
Chinese in Excel VBA for environment MS Office 2000 and/or 2003.

Thx.

 

[NickHK]

Ki,
I work on English W2K/English Office 2K. The rest of the network/Office 2K
is Chinese (HK).
I have numerous difficulties using VBA in this environment e.g. as soon as a
path has Chinese characters in it using .SaveAs.
Whilst XL itself is better (but not perfect as SaveAs still does not work
with Chinese characters), because VB/VBA does all its ANSI/Unicode
conversions it make the whole process difficult.
If you ever find help on this, post back as I would be interested.

NickHK

Thanks Gareth.

Do anyone know any reference web site or information for handling Simplified
Chinese in Excel VBA for environment MS Office 2000 and/or 2003.

Thx.

-------------- CUT ---------------