I wrote a program that imports selected data from one or several csv file(s). The program opens the first csv file and then according to some characteristics copies selected data to a newly created workbook. Subsequently the remaing csv files are opened and desired data is copied to the new workbook.

This program does its job well except that special characters are converted to japanese characters. The same problem occurs if I just open the csv file in Excel. However, if I instead of opening the csv file import it using "Data-from text" and specify File origin as Unicode UTF-8 then the special characters are not converted to japanese characters.

What I would like to do now is to modify the below loop so that the special characters are preserved. I don't know if this can be done by just modifying the below loop which is based on opening the csv file(s) or whether the loop needs to be completely rewritten and based on importing the csv data in UTF-8.

Yet another solution would be to change the default file opening format. On US or European Excel installations csv file special characters are not converted to japanese characters. I looked but cannot find any setting that would do the trick.

I need some help either reprogramming the below sub routine or changing the Excel setting.

'Open files and move data
                
        For i = LBound(FileName) To UBound(FileName)
            FileName2 = FileName(i)
            Workbooks.Open FileName:=FileName2
            csvWorkbook = ActiveWorkbook.Name
            SheetName2 = Workbooks(csvWorkbook).ActiveSheet.Name
                
            LastzColumn = Workbooks(csvWorkbook).Sheets(SheetName2).Cells(1, Columns.Count).End(xlToLeft).Column
            SheetzLastRow = Workbooks(csvWorkbook).Sheets(SheetName2).Range("A" & Rows.Count).End(xlUp).Row
            SheetArrangeDataLastRow = Workbooks(MainWorkbook).Sheets(SheetName).Range("H" & Rows.Count).End(xlUp).Row
            LastRowInitial = SheetArrangeDataLastRow
            
            Workbooks(MainWorkbook).Activate
            Sheets(SheetName).Select
            Rows(SheetArrangeDataLastRow + 2).Select
            For z = 1 To SheetzLastRow - 1
                Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Next z
            
            For y = 1 To 8
                For x = 1 To LastzColumn
                    If Workbooks(MainWorkbook).Sheets(SheetName).Cells(1, y) = Workbooks(csvWorkbook).Sheets(SheetName2).Cells(1, x) Then
                        Workbooks(csvWorkbook).Sheets(SheetName2).Activate
                        Workbooks(csvWorkbook).Sheets(SheetName2).Range(Cells(2, x), Cells(SheetzLastRow, x)).Copy
                        Workbooks(MainWorkbook).Activate
                        Sheets(SheetName).Select
                        Cells(SheetArrangeDataLastRow + 1, y).Select
                        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                            :=False, Transpose:=False
                    End If
                Next x
            Next y

            Workbooks(csvWorkbook).Activate
            ActiveWorkbook.Close
            Workbooks(MainWorkbook).Sheets(SheetName).Select
        
        Next i