Re: Excel and Shift-JIS
- From: Greg Lovern <gregl@xxxxxxxxx>
- Date: Thu, 8 May 2008 15:46:11 -0700 (PDT)
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 <gr...@xxxxxxxxx> 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 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
.
- Prev by Date: Controlling how a second excel file opens
- Next by Date: VBA Additional controls
- Previous by thread: Controlling how a second excel file opens
- Next by thread: VBA Additional controls
- Index(es):
Relevant Pages
|
