Thread: CSV export with Russian letters

sakinen,

Give the following code a try. It uses a second worksheet (named "Conversion") to get the values to find (column A) and replace with (column B):

Sub write_csv_xls()
    
    Dim xlsPath As String: xlsPath = ActiveWorkbook.FullName
    Dim FileExt As String: FileExt = StrReverse(Left(StrReverse(xlsPath), InStr(StrReverse(xlsPath), ".")))
    ActiveWorkbook.SaveAs Filename:=Replace(xlsPath, FileExt, ".csv"), FileFormat:=xlCSV
    Dim wbCSV As Workbook: Set wbCSV = ActiveWorkbook
    
    Dim wsCnvt As Worksheet: Set wsCnvt = Sheets("Conversion")
    Dim strFind() As Variant, strReplace() As Variant, arrIndex As Long
    strFind = wsCnvt.Range("A1", wsCnvt.Cells(Rows.Count, "A").End(xlUp)).Value
    strReplace = wsCnvt.Range("B1", wsCnvt.Cells(Rows.Count, "B").End(xlUp)).Value
    
    Dim allCells() As Variant: allCells = wbCSV.ActiveSheet.UsedRange.Value
    Dim r As Long, c As Long
    For r = 1 To UBound(allCells, 1)
        For c = 1 To UBound(allCells, 2)
            For arrIndex = 1 To UBound(strFind)
                allCells(r, c) = Replace(allCells(r, c), strFind(arrIndex, 1), strReplace(arrIndex, 1))
            Next arrIndex
        Next c
    Next r
    ActiveSheet.UsedRange.Value = allCells
    
    Workbooks.Open xlsPath
    wbCSV.Close True
    
End Sub

Hope that helps,
~tigeravatar

Wow. Works like a charm. Bow to the master!
I have a couple of questions.
If i understood the code i can write down more letters to translate i want in a conversion table.
Is there a way to insert a few rows of code that kills previous csv without asking to overwrite it. I tried to use from a previous code but something got wrong.
Thanks a lot again!

sakinen,

To prevent it from asking if you want to override it, just wrap it in Application.DisplayAlerts = False/True as follows:

Sub write_csv_xls()
    
    Application.DisplayAlerts = False
    
    Dim xlsPath As String: xlsPath = ActiveWorkbook.FullName
    Dim FileExt As String: FileExt = StrReverse(Left(StrReverse(xlsPath), InStr(StrReverse(xlsPath), ".")))
    ActiveWorkbook.SaveAs Filename:=Replace(xlsPath, FileExt, ".csv"), FileFormat:=xlCSV
    Dim wbCSV As Workbook: Set wbCSV = ActiveWorkbook
    
    Dim wsCnvt As Worksheet: Set wsCnvt = Sheets("Conversion")
    Dim strFind() As Variant, strReplace() As Variant, arrIndex As Long
    strFind = wsCnvt.Range("A1", wsCnvt.Cells(Rows.Count, "A").End(xlUp)).Value
    strReplace = wsCnvt.Range("B1", wsCnvt.Cells(Rows.Count, "B").End(xlUp)).Value
    
    Dim allCells() As Variant: allCells = wbCSV.ActiveSheet.UsedRange.Value
    Dim r As Long, c As Long
    For r = 1 To UBound(allCells, 1)
        For c = 1 To UBound(allCells, 2)
            For arrIndex = 1 To UBound(strFind)
                allCells(r, c) = Replace(allCells(r, c), strFind(arrIndex, 1), strReplace(arrIndex, 1))
            Next arrIndex
        Next c
    Next r
    ActiveSheet.UsedRange.Value = allCells
    
    Workbooks.Open xlsPath
    wbCSV.Close True
    
    Application.DisplayAlerts = True
    
End Sub

Hope that helps,
~tigeravatar

Thanks a lot man. It works fine.
The only thing left that i noticed just a minute ago is that if i change something and do not save excel, after running a macro changed values return to the previous ones. Nothing to worry abut thou.
Thanks again!