CSV export with Russian letters

[sakinen]

First of all i need to explain why I need this because its the usual question that people ask me. I use some excel file for Autocad automation. Im basicaly fillin the tables in Autocad from the data in Excel. I export the CSV in a separate file from that same excel (Im using a macro for that) and use that in Autocad. It all works fine when using English keyboard. Coleagues from Moscow asked me if i could do the same automation For CAD in their office. Of course they all use russian letters in their work. Problem occurs when i save the csv from the excel i use in Russian instead of letters i get a lot of question marks. I didnt even bother to import that in Autocad. But the thing that works is hex representation of the given letter.
For example,<table border="0" cellpadding="0" cellspacing="0" height="661" width="123"><tbody> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ц</td> <td>\U+0446</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">у</td> <td>\U+0443</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">к</td> <td>\U+043A</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">е</td> <td>\U+0435</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">н</td> <td>\U+043D</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">г</td> <td>\U+0433</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ш</td> <td>\U+0448</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">щ</td> <td>\U+0449</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">з</td> <td>\U+0437</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">х</td> <td>\U+0445</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ъ</td> <td>\U+044A</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ф</td> <td>\U+0444</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ы</td> <td>\U+044B</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">в</td> <td>\U+0432</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">а</td> <td>\U+0430</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">п</td> <td>\U+043F</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">р</td> <td>\U+0440</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">о</td> <td>\U+043E</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">л</td> <td>\U+043B</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">д</td> <td>\U+0434</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ж</td> <td>\U+0436</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">э</td> <td>\U+044D</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">я</td> <td>\U+044F</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ч</td> <td>\U+0447</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">с</td> <td>\U+0441</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">м</td> <td>\U+043C</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">и</td> <td>\U+0438</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">т</td> <td>\U+0442</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ь</td> <td>\U+044C</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">б</td> <td>\U+0431</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ю</td> <td>\U+044E</td> </tr> </tbody></table>When imported in CAD this works just fine. Russian letters appear.
So my question is, is there a way to translate the Russian letters in above stated manner, in the moment when macro saves the csv. To be perfectly clear, i want my excel unchanged, but the csv translated.
This is the code i use for csv save as:

Sub write_csv_xls()     Application.DisplayAlerts = False          xls = ActiveWorkbook.FullName     Length = Len(xls) - 3     csv = Left(xls, Length) & "csv"          ActiveWorkbook.SaveAs Filename:= _         csv, FileFormat:=xlCSV, _         CreateBackup:=False      'Delete the Existing copy of the file so we can save it back over it     'without the write-protected error     Kill xls     ActiveWorkbook.SaveAs Filename:= _         xls, FileFormat:=xlNormal, _          Password:="", WriteResPassword:="", ReadOnlyRecommended:= _         False, CreateBackup:=False              Application.DisplayAlerts = True End Sub

Thanks in advance!

 

[Ralajer]

The below should be what you need. I handle the saving the csv file through a temp file. The conversion of the Russian letters gets called from the main sub.

Sub write_csv_xls()
    Dim xls As String
    Dim csv As String
    Dim tmpFile As String
    Dim xlsWB As Workbook
    Dim csvWB As Workbook
    
    Application.DisplayAlerts = False
    Set xlsWB = ActiveWorkbook
    With xlsWB
        .Save
        xls = .FullName
        tmpFile = Environ("temp") & "\" & Format(Time(), "HHMMSS") & .Name
        csv = Left(xls, InStrRev(xls, ".")) & "csv"
        .SaveCopyAs Filename:=tmpFile
    End With
        Set csvWB = Workbooks.Open(Filename:=tmpFile)
        
    With csvWB
        '// May have to adjust range and sheet. If used range is very large and
        '//  not all cells contain Russian characters limit range to improve speed.
        convertToUnicode (.Sheets(1).UsedRange)
        .SaveAs Filename:=csv, FileFormat:=xlCSV, _
            CreateBackup:=False
        .Close
    End With
    xlsWB.Activate
        
    Kill tmpFile
            
    Application.DisplayAlerts = True
End Sub

This code gets called from the main and does the conversion in the temp file before it is saved as csv file

Sub convertToUnicode(convertRng As Range)
    Dim RussianLetter As Variant
    Dim Unicode As Variant
    Dim cell As Range
    Dim str As String
    Dim i As Integer
    
    RussianLetter = Array(ChrW(&H446), ChrW(&H443), ChrW(&H43A), ChrW(&H435), _
            ChrW(&H43D), ChrW(&H433), ChrW(&H448), ChrW(&H449), ChrW(&H437), _
            ChrW(&H445), ChrW(&H44A), ChrW(&H444), ChrW(&H44B), ChrW(&H432), _
            ChrW(&H430), ChrW(&H43F), ChrW(&H440), ChrW(&H43E), ChrW(&H43B), _
            ChrW(&H434), ChrW(&H436), ChrW(&H44D), ChrW(&H44F), ChrW(&H447), _
            ChrW(&H441), ChrW(&H43C), ChrW(&H438), ChrW(&H442), ChrW(&H44C), _
            ChrW(&H431), ChrW(&H44E))
    Unicode = Array("\U+0446", "\U+0443", "\U+043A", "\U+0435", "\U+043D", _
            "\U+0433", "\U+0448", "\U+0449", "\U+0437", "\U+0445", "\U+044A", _
            "\U+0444", "\U+044B", "\U+0432", "\U+0430", "\U+043F", "\U+0440", _
            "\U+043E", "\U+043B", "\U+0434", "\U+0436", "\U+044D", "\U+044F", _
            "\U+0447", "\U+0441", "\U+043C", "\U+0438", "\U+0442", "\U+044C", _
            "\U+0431", "\U+044E")
            
    For Each cell In convertRng
        str = cell.Value
        For i = LBound(Unicode) To UBound(Unicode)
            str = Replace(str, RussianLetter(i), Unicode(i))
        Next i
        cell.Value = str
    Next cell
End Sub

Let me know if this works.

 

[sakinen]

First of all thanks for the help.
Unfortunately routine didn't work.
Let me explain my steps.
I copied two scripts in vba editor in order just like you posted.
Then i changed sheet and range i'm going to use.

convertToUnicode (.Sheet1.russian)

Sheet1 is of course the name of the sheet and russian is the name of the range i later made in name manager.
When i apply the macro i get
"Runtime error 438
Object doesn't support this property or method"
Maybe i missed some adjustment in your code?

 

[Ralajer]

I tried it with sheet codename and it didn't work for me either. I am not sure if that's because it's in a separate workbook than the macro. To make things distinct I'm defining the convert range outside the call to the sub using the sheet's display name. Try the below

Sub write_csv_xls()
    Dim xls As String
    Dim csv As String
    Dim tmpFile As String
    Dim xlsWB As Workbook
    Dim csvWB As Workbook
[COLOR="red"]    Dim rusRng As Range[/COLOR]
    
    Application.DisplayAlerts = False
    Set xlsWB = ActiveWorkbook
    With xlsWB
        .Save      '// Save if needed
        xls = .FullName
        tmpFile = Environ("temp") & "\" & Format(Time(), "HHMMSS") & .Name
        csv = Left(xls, InStrRev(xls, ".")) & "csv"
        .SaveCopyAs Filename:=tmpFile
    End With
        Set csvWB = Workbooks.Open(Filename:=tmpFile)
    With csvWB
[COLOR="Red"]
        'Change Sheets("[B]Russian[/B]") to the displayed Name of the sheet
        Set rusRng = .Sheets("Russian").Range("russian")
        Call convertToUnicode(rusRng)[/COLOR]
        .SaveAs Filename:=csv, FileFormat:=xlCSV, _
            CreateBackup:=False
        .Close
    End With
    
    xlsWB.Activate
    
    Kill tmpFile
            
    Application.DisplayAlerts = True
End Sub

Let me know if that fixes it.

 

[sakinen]

I'm now getting

"Compile error:
Sub or function not defined.

 

[Ralajer]

Where is the convertToUnicode Sub in relation to the write_csv_xls. Is it in the same module? If not move there and let me know if you still get the same error.