Korean script compatibility with Vba

[RyanG]

How do I handle Korean script in the Excel Vba code?

Specifically, I am trying to save a document to a file directory that has a
Korean text in the directory path. I.e., C:\Documents and Settings\ì‹ ë™í•˜\My
Documents. However, when I record the macro just to see how Excel would
write the code, the Korean text turns into ???, i.e. ActiveWorkbook.SaveAs
Filename:= _
"C:\Documents and Settings\???\My Documents\Book5.csv", FileFormat:= _
xlCSV, CreateBackup:=False
Indeed, re-running the recorded macro causes an error because ??? is not a
valid directory name. I don't like hard coding names, so even when I use a
variable to pick up the directory name from a cell, hovering the mouse over
the variable when in step mode, indicates that the Korean letters are not
recognized and the variable value becomes ???.

I have the Korean IME loaded, so I can type the Korean characters in most
programs, but not in Vba.

Along these same lines, I want to use MsgBox(Msg, Style, Title) to display
the message in Korean, but again, the Korean letters can not be entered
directy into the Vba script, nor passed using a variable.

Is it possible for Korean letters to be handled with Vba? If so how?

Thanks for any help that is given!

 

[NickHK]

The editor is ANSI only, although all VBA string are Unicode internally.
So, whilst VBA variables are able to hold the correct Unicode text, the VB
editor is not able to display them; hence the "???"
Dim TempStr as string
TempStr=Range("A1").Value 'Which holds some Korean (Unicode) text

Debug.Print TempStr

????

So if you need to work with actual text outside of that range in the VBE,
you need to use Hex value and ChrW/byte arrays.
You can use a WS cell instead of the Immediate window to see Unicode values.

In your case, it may be better to resort to the API to get the MyDocuments
folder, but use the W version of the API:
http://forums.devx.com/archive/index.php/t-37771.html

NickHK

 

[RyanG]

Thanks for the post Nick, but I'm afraid it doesn't help. Perhaps I confused
the situtation by using a system directory. The intent is to use any
directory a user might define, such at
H:\CustomerFiles\롬만\Data_Files\ì‹ ë™í•˜\Data_Files where the Korean text is not
the username. I have already found that Vba doesn't like spaces in the
directory name; it appears to be the same for unicode?

Here is the test code I ran

DirectoryName1st = Range("A1").Value
DirectoryName2nd = Range("A2").Value
ChDir DirectoryName1st
NewDirectoryName = DirectoryName1st & DirectoryName2nd
ChDir NewDirectoryName

Where DirectoryName1st = C:\Documents and Settings
and DirectoryName2nd = \ì‹ ë™í•˜
I get the error "Path not Found" when executing the last line.

I even went so far as to try entering the unicode using ChrW
NewDirectoryName = DirectoryName1st & "\" & ChrW(-15648) & ChrW(-19495) &
ChrW(-10920)
but I still get the same error.

Likewise, neither the use of a variable nor ChrW technique worked with the
message box. I have to believe it is possible to display Korean characters
in a message box because I have been sent a screen shot where the error
message window is all Korean text.

Thanks,
Ryan

 

[NickHK]

Assuming that you are working on an English OS, VBA uses the ANSI API calls
under the hood.
However, as you need to work with Unicode paths, call the Wide APIs
directly.
Note that Dir() works here, because it does not have to work with a Unicode
path; the CurDir is alraedy set.
IIRC, if you need to use Dir with Unicode path, you will need to resort to
the API; FindFirstFileW etc.

Private Declare Function SetCurrentDirectory Lib "kernel32" Alias
"SetCurrentDirectoryW" (ByVal lpPathName As Long) As Long

Private Sub CommandButton1_Click()
Dim Path As String
Dim RetVal As Long

Path = "C:\" & Range("A1").Value
RetVal = SetCurrentDirectory(StrPtr(Path))

If RetVal > 0 Then
Debug.Print Dir("*.*")
End If

End Sub

NickHK

 

[RyanG]

Thanks NickHK, that works! You solved my problem.

RyanG