On Apr 30, 2:39 pm, "Anthony Jones" <A...@yadayadayada.com> wrote:
> "Greg Lovern" <gr...@gregl.net> wrote in message
>
> news:0d1a3361-6e4d-47da-8edd-90a4201fdd79@m......
>
>
>
> > I need to get Excel data with Japanese characters uploaded as a text
> > tab-delimited files to a web server that requiresShift-JIScharacter-
> > encoding for Japanese characters. Excel won't save asShift-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 asShift-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 toShift-JIS:
>
> > Dim strShiftJIS As String
> > Const LOCALE_ID_SHIFT_JIS As Long = 1041 'this is the decimalshift-> jisLCID 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 ashift-jischarset, and upload it to the server:
>
> > (I inherited this part, except for specifying ashift-jischarset,
> > 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 toShift-JIS.
> >  -- IXMLHTTPRequest.Send the text asShift-JIS, to a server that
> > requiresShift-JIS.
>
> > Any suggestions on what I'm doing wrong?
>
> Try changing this:-
>
> Dim strShiftJIS As String
>
> to this:-
>
> Dim abytShiftJIS() As Byte
>
> then use:-
>
> abytShiftJIS = StrConv(sText, vbFromUnicode, LOCALE_ID_SHIFT_JIS)
>
> and
>
> Call httpreq.send(abytShiftJIS)
>
> --
> Anthony Jones - MVP ASP/ASP.NET

Thanks, I tried those changes, but unfortunately the characters
reported back by the server tool are still the wrong Japanese
characters.

Greg