1

I would like to read value from Excel cell containing Russian characters to VBA string variable or variant variable (for matrix). Suppose in A1 cell we have Russian characters (displayed properly).

When I read it as MyString=Range("A1").Value the variable MyString reads as ?????????????????????????????????150???50

Simmilarly this test fails:

? Activecell.Value

When I dump string to file with this code:

Dim myFile As String
Dim N As Integer
N = FreeFile()
myFile = "C:\MyFileName.txt"
Open myFile For Output As #N
Print #N, SQL
Close #N
Debug.Print myFile

I get ?????

Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
  • 4
    The VBA console does not support outputting Unicode characters. That does not mean they are broken. Output them to another cell - it should work as expected. – Tomalak Oct 09 '17 at 11:56
  • @Tomalak to another cell maybe yes. But I have to keep them in variant variable and then in string variable. When I send such a string variable with ADO to server or dump it (`Print #N, MyString`) to a text file I have `?????`. – Przemyslaw Remin Oct 09 '17 at 12:01
  • *How* do you dump them to a file? – Tomalak Oct 09 '17 at 12:01
  • @Tomalak I updated my question. – Przemyslaw Remin Oct 09 '17 at 12:07
  • 3
    Use a TextStream object to save the file. It has a Unicode option. See https://stackoverflow.com/questions/1016326/unicode-string-to-flat-file-from-vba – Tomalak Oct 09 '17 at 12:13
  • 4
    Go to your regional settings `Control Panel -> Clock, Language, and Region -> Region and Language`, select `Administrative` tab, click `Change system locale` in the `Language for non-Unicode programs`. Change that to whatever language you're using (other than English), then restart your PC (required!). This is to supply the correct characters with codes>=128 in the ASCII table (that's how languages were set in the pre-Unicode era). Should fix it. – nurchi Nov 27 '17 at 18:15
  • If comments could be accepted as answers, I would accept Tomalak comments. "Use a TextStream object to save the file. It has a Unicode option. See" https://stackoverflow.com/q/1016326/1903793 – Przemyslaw Remin Jan 18 '23 at 17:06
  • This works for me - "When you copy and paste your VBA check if the Cyrillic characters are shown correctly - if not before pressing Ctrl + C to copy, change your language from English to your local Cyrillic language (sounds crazy but at my case this was the reason)" – Аникин Василий Jan 18 '23 at 07:54

2 Answers2

2
  1. read the cells in a NOT type variable (because its UTF-8)
  2. save the variable by using ADODB.stream (bytes not strings)
Ralf
  • 21
  • 2
1

I had the same issue for Cyrillic VBA.

To solve it:

1st. Go to Regional and Language options Administrative -> Curent Language for non-Unicode programs - and choose your cyrillic language.

2nd. In excel - enter the VBA ( Alt +F11) choose tools -> Options -> Editor Format - and choose Font - Times New Roman (Cyrillic)

If the problem still occures - check if you're using a newer version on Excel ( my file was .xlxm so I save it as .xlsx and then create a new module (copy paste the VBA) and it worked.

When you copy and paste your VBA check if the Cyrillic characters are shown correctly - if not before pressing Ctrl + C to copy, change your language from English to your local Cyrillic language (sounds crazy but at my case this was the reason)

Hope this helps.

Stoyan
  • 21
  • 3
  • Thank you for the solution. My problem was not to read a value within the code but to read it with my eyes. It is actual for Microsoft Visual Basic 6.5 version 1057 (Office Tab Enterprise v8.00). Any Cyrillic font would fit. – nik s Oct 23 '22 at 08:14