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