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-byte 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:19 pm, Greg Lovern 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.

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