|
Korean script compatibility with Vba
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! |
Korean script compatibility with Vba
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" wrote in message ... 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! |
Korean script compatibility with Vba
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" wrote: 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" wrote in message ... 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! |
Korean script compatibility with Vba
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" wrote in message ... 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" wrote: 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" wrote in message ... 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! |
Korean script compatibility with Vba
Thanks NickHK, that works! You solved my problem.
RyanG "NickHK" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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! |
| All times are GMT +1. The time now is 02:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com