I finally had an opportunity to talk to the developers who work with
the server to which we're uploading. It turns out the server expects
an array of 8 bits per element, or  in other words, a Byte array.
Sending plain text, which was how it was being done when I inherited
the project, happened to work fine for English/French/German and
probably all other single-bite character sets, but not for multi-byte
character sets.

Here's what is working fine now:

Save from Excel as plain text, which it turns out is Shift-JIS on
Japanese Excel, preserving the Japanese characters unlike doing the
same on English Excel.

Open as binary:

    nSourceFile = FreeFile
    Open sFile For Binary As #nSourceFile
    sText = InputB$(LOF(1), 1)
    Close #nSourceFile


Convert to Byte array:

    Dim bytearrayUploadText() As Byte
    bytearrayUploadText = sText


That didn't get past IXMLHTTPRequest.Send, which returned an error
saying the parameter (the byte array) was wrong. So, I wrapped the
byte array in a variant:

    Dim vArray As Variant 'declare as variant, not array.
    vArray = bytearrayUploadText

And that worked! On the first try and all subsequent tries, the
characters were all correct.

Greg


On Apr 29, 3:30 pm, Greg Lovern <gr...@gregl.net> wrote:
> 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.
>
> Here's what I'm doing:
>
> 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 a IXMLHTTPRequest 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 are:
>  -- Save file from Excel as Unicode.
>  -- Open file with FileSystemObject as Unicode.
>  -- Convert text from Unicode to Shift-JIS.
>  -- IXMLHTTPRequest.Send the text as Shift-JIS, to a server that
> requires Shift-JIS.
>
> Any suggestions on what I'm doing wrong?
>
> Thanks,
>
> Greg