I'm sorry, I may have confused you when I mentioned "visually".
I don't care about font.

I just need the correct text data to appear in the output.

When I check the binary data on the Japanese file, I see that it basically
forced the unicode data into single byte characters.

I'm used to C++ where I can just treat the text data binary files and run
the data through functions to convert to UTF-8 and other encodings.

When I walk through the VBA code, it looks like the data is corrupted as
soon as the data is accessed through the Cells function.

"Joel" wrote

You are writting text data which has 256 different characters. Each Byte is
8 bits (refered to as Ascii) of data (2 raised to the 8th). There is no font
with text data. Depending on your language setting the text displays wiuth
the font as specified by the language setting.

If you import the text into Word which has multiple fonts you can change the
font to any language you want without changing the language setting.

There is no problem with your excel program. The easiest thing to do is to
open the text file and copy the data. then paste the data into a word
document ans select the proper Font for the language. Then save the word
document. Word will preserve the correct font which a text file cannot do.

"Christopher" wrote:

Hi,

I was hoping if someone could help me with a language problem I'm facing.
The code below is suppose to extract strings from a worksheet and create
string table files that will be fed into a resrouce file. I am using Excel
2007 currently.

The style of the worksheet:
First column has the string IDs
First row contains the name of the file to create (excluding column 1, which
is skipped)
Each column (file) has a different language (english, french, german,
italian, portuguese, spanish, and japanese)

The style of the output file:
Has header "STRINGTABLE" and "BEGIN"
Each row of text has a string ID and string value
Has footer "END"

I'm not an experienced VB programmer, so I am basically depending on Excel
to be smart enough to understand what I want. It "sort of" works, but it's
very annoying that I have to keep switching languages to get the correct
output.

The problem:
If I set my OS language settings to English, the non-Japanese languages are
extracted properly, but Japanese gets corrupted. If I set my OS language to
Japanese, languages like French loses all accents on characters.

What I'm aiming for:
To get what is exactly (visually) displayed by the excel spreadsheet to be
outputted to a file regardless of the OS language setting. I don't
necessarily care about what format it's in (except not in Unicode) as long as
it's consistent. Granted the work around is to swtich the OS language
settings, but I might have to add more languages later on and the work around
itself makes me cringe.

- Japanese is using Shift-JIS
- Other languages are using ANSI (extended latin) or UTF-8

I was hoping there is a way to change the language setting of the
application (on-the-fly) to something that is compatible with the language of
the strings (column) I am currently extracting.

I searched the forum, but couldn't find a similar issue posted. Sorry if
this issue has already been addressed.

Any help will be greatly appreciated.

The code:
Sub Export()

Dim TblName As String
Dim CurDir As String
Dim Text As String
Dim SID As String
Dim WriteBuf As String
Dim cCount As Integer
Dim rCount As Integer
Dim rowMax As Integer

'get directory base for tbl files
CurDir = GetCurDir

'get maximum number of rows
Dim wkst As Worksheet
Set wkst = Worksheets("StringTables")
rowMax = wkst.UsedRange.Rows.Count

'get first tbl column and initialize row and column count
TblName = CurDir + wkst.Cells(1, 2).Text
rCount = 2
cCount = 2

'keep looping as long as a tbl name exists
Do While TblName < CurDir
Open TblName For Output As #1
'print string table headers
Print #1, "STRINGTABLE"
Print #1, "BEGIN"

'get first string ID number and string value
SID = wkst.Cells(rCount, 1)
Text = wkst.Cells(rCount, cCount)
Do While rCount <= rowMax

'skip this row if ID or value is missing
If SID < "" And Text < "" Then
'Chr(9) = tab Chr(34) = double quote
WriteBuf = Chr(9) + SID + Chr(9) + Chr(34) + Text + Chr(34)
Print #1, WriteBuf
End If

'increment row count
rCount = rCount + 1
'get next pair of string ID and value
SID = wkst.Cells(rCount, 1)
Text = wkst.Cells(rCount, cCount)
Loop

'print table footer
Print #1, "END"
Close #1

'reset row count
rCount = 2
'increment column count (new language)
cCount = cCount + 1
'setup for new tbl file
TblName = CurDir + wkst.Cells(1, cCount).Text
Loop

End Sub