On Apr 29, 5:29*pm, "Mark Ivey" (do_not_spam)
wrote:
Are you sure that your conversion to Unicode isn't causing the issue?

Yes, about as sure as reasonably possible, because:
-- That step is the same as in the manual procedure that works fine,
the only difference being manually saving it as Unicode Text from
Excel, vs. programmatically saving it as Unicode Text (xlUnicodeText)
from Excel.
-- We tested taking the Unicode file created programmatically and
used it in the rest of the manual procedure (opened that file in Word
and saved as Shift-JIS, etc.), and that worked fine.

The conversion to Unicode happens when saving the Excel file as
Excel's "Unicode Text" file type.


Can you save the file as a text file?

We are saving the file as a text file. When we save the Excel file as
Excel's "Unicode Text" file type, we are saving the file as a text
file. Excel's "Unicode Text" file type is Unicode tab-delimited plain
text.

Greg


Mark Ivey

"Greg Lovern" wrote in message

...



I need to get Excel data with Japanese characters uploaded as a text
tab-delimited files to a web server that requires Shift-JIS character-
encoding for Japanese characters. Excel won't save as Shift-JIS.

First, it works fine if we manually:
-- Save out from Excel as Excel's "Unicode Text" file type.
-- Open in Word.
-- Save out from Word as Shift-JIS (automatically prompted by Word
when saving plain text file).
-- Upload to the server using an internal web tool to which I have no
access to or knowledge of the code that makes it work.

To preserve the Japanese characters, I save from Excel as the "Unicode
Text" file type, which is tab-delimited as required. This much is the
same as the manual steps above that work correctly.

Then I use the FileSystemObject to load the Unicode file into a text
variable:

Dim fso As Object
Dim oTextStream As Object
Dim sText as String
Set fso = CreateObject("Scripting.FileSystemObject")
Set oTextStream = fso.OpenTextFile(sFile, , , -1) '-1 = TristateTrue =
Unicode
sText = oTextStream.ReadAll

Then I convert the Unicode text to Shift-JIS:

Dim strShiftJIS As String
Const LOCALE_ID_SHIFT_JIS As Long = 1041 'this is the decimal shift-
jis LCID as opposed to the Hex one.
strShiftJIS = StrConv(sText, vbFromUnicode, LOCALE_ID_SHIFT_JIS)

Then I create an MSXML object, specifiying a content type of "text/
xml" with a shift-jis charset, and upload it to the server:

(I inherited this part, except for specifying a shift-jis charset,
from code that works fine for English, German, and French. The person
who wrote it is no longer with the company.)

Dim httpreq As Object 'IXMLHTTPRequest
Set httpreq = CreateObject("MSXML2.XMLHTTP.3.0")
Call httpreq.Open(bstrmethod:="POST", bstrurl:="http://myurl",
varAsync:=False)
Call httpreq.setRequestHeader("Authorization", "Basic " & <encoded
password)
Call httpreq.setRequestHeader("Content-Type", "text/xml;charset=shift-
jis")
Call httpreq.send(strShiftJIS)

The upload is successful, but the characters are wrong. They are
Japanese characters but not the right ones.

So the steps a
-- Save file from Excel as Unicode.
-- Open file with FileSystemObject as Unicode.
-- Convert text from Unicode to Shift-JIS.
-- Upload text as Shift-JIS, to a server that requires Shift-JIS.

Any suggestions on what I'm doing wrong?

Thanks,

Greg- Hide quoted text -

- Show quoted text -