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 are:
-- 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
Can you save the file as a text file?
Mark Ivey
"Greg Lovern" <gr...@gregl.net> wrote in message
news:02f66481-c9c1-402c...@w74g2000hsh.googlegroups.com...
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
> > Greg- Hide quoted text -
>
> - Show quoted text -
Looks like your best bet is the windows API function WideCharToMultiByte.
You might want to have a look at the windows API function
WideCharToMultiByte in MSDN. It looks like it will do what you need it to
(convert the unicode into Shift-JIS).
http://msdn.microsoft.com/en-us/library/ms776420(VS.85).aspx
It looks like it will do what you need it to, although you'll have to
remember in your code that you're working with the converted data so you may
want to work in unicode as much as possible and then convert to shift-jis at
the last opportunity.
~ James
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)
Here's the thread in microsoft.public.win32.programmer.international
where he suggested it for converting from Unicode to Shift-JIS:
I wanted to test StrConv's conversion from Unicode to Shift-JIS by
saving it out as a file and manually uploading, but I didn't find a
way to save it out that preserves Shift-JIS.
Thanks,
Greg
On Apr 30, 5:09 am, James Snell <JamesSn...@discussions.microsoft.com>
wrote:
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