Spliting up an excel spreadsheet with languages' rows into multiple files

I have many excel files containing terminological data in 3 different languages:
The header (first colum) indicates the languages' code (EN, FR, ES) for each row in every file.
I need to split up every file according to these 3 languages and I should only have 3 final files containing all the rows in each language respectively.

Many thanks in advance,

Caro

All files are in the same folder indeed and it doesn´t matter in which order they are read.

It doesn´t matter either how the final files are named. I just need to have 3 files at the end, one for each language containing only the information in the same language.

Attached you find a sample of the tables.

Thanks!

Many thanks for your help in this, though I haven´t been able to download the file... I got the following error message:

The page you've been trying to access was blocked.

Reason: Forbidden file type! An attempt was made to download a forbidden file type.
Transaction ID is 53EB0B7E85790E02577D.

As to your question, the titles' row don´t change, so there is no need for you to do anything else in this regard.

But if you could, it would be much appreciated if you could add something in the macro so that the 3 sheets are then saved as 3 different files. The name of the files could be "University_Projects_EN", "University_Projects_FR" and "University_Projects_ES".

Many thanks again! This is really useful, as I'll have many other files to process in the same way.

Hi again!

I could neither open the attached file (same error as last time) nor the link. For the link it even says "This website is known to distribute malware, or is part of a security incident or cyber threat as reported by the Computer Emergency Response Team (CERT)."

To be honest, I feel disappointed...

Hi p24leclerc!

I certainly did not try to open the file from another computer, since it supposedly contains a malware. I could try later on tonight when I go back home, but I´ll wait for Jindon's reply first. I have already had a bad experience with a Trojan and I don´t want to spend again days removing it :D
Thanks for your help! It's much appreciated!

See if this works.

Sub test()
    Dim myDir As String, fn As String, e, x
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show Then myDir = .SelectedItems(1) & "\"
    End With
    If myDir = "" Then Exit Sub
    x = Array("EN", "FR", "ES")
    For Each e In x
        With Workbooks.Add
            .SaveAs myDir & e & ".xls"
        End With
    Next
    fn = Dir(myDir & "*.xls")
    Do While fn <> ""
        Select Case True
            Case fn Like "EN.*", fn Like "FR.*", fn Like "ES.*"
            Case Else
                With Workbooks.Open(myDir & fn).Sheets(1).Cells(1).CurrentRegion
                    .Parent.AutoFilterMode = False
                    For Each e In Array("EN", "FR", "ES")
                        .AutoFilter 1, e
                        If Workbooks(e & ".xls").Sheets(1).Cells(1) = "" Then
                            .Copy Workbooks(e & ".xls").Sheets(1).Cells(1)
                        Else
                            .Offset(1).Copy Workbooks(e & ".xls").Sheets(1).Range("a" & Rows.Count).End(xlUp)(2)
                        End If
                    Next
                    .Parent.Parent.Close False
                End With
        End Select
        fn = Dir
    Loop
    For Each e In x
        Workbooks(e & ".xls").Close True
    Next
End Sub

Hi Jindon!

Many thanks for helping me out here. I´ve saved the macro in my personal VBA folder and it ran well (the folders ES, EN and FR were created) but when I checked the content of the newly created files it popped-up the following message:

"The file you are trying to open 'ES.xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

I really cannot see the problem here...I have Windows system and Microsoft 2010.

Also, I wanted to ask you how do I run the macro on all files at the same time? Cause I need to have at the end only 3 files, one for each language containing only the information for that same language.

Many thanks again!!!

Duplicagte///

Try change all ".xls" to ".xlsx"

Also, I wanted to ask you how do I run the macro on all files at the same time?

What do you mean by "all files"?

I´ve changed the extension but although I didn´t get the same message, the 3 created files were empty.
By all files I mean that I need to automise the spliting of language rows from different xlsx files. I have aprox. 100 xlsx files that look like the sample table attached above and I need to have that information saved as 3 files (one for each language).

Hi CaroDuna and p24leclerc,

Just to let you know.. I downloaded both files that p24leclerc attached without issue..

Sounds the data range doesn't start from 1st row...

By the way

fn = Dir(myDir & "*.xls")

This extension should not be changed...

Table_Sample.xlsBy not changing the extension in that string I get an error.
The data range does beginn as from the 1st row... attached you find a sample table if you'd like to have a look at.

The code should look like

Sub test()
    Dim myDir As String, fn As String, e, x
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show Then myDir = .SelectedItems(1) & "\"
    End With
    If myDir = "" Then Exit Sub
    x = Array("EN", "FR", "ES")
    For Each e In x
        With Workbooks.Add
            .SaveAs myDir & e & ".xlsx"
        End With
    Next
    fn = Dir(myDir & "*.xls")
    Do While fn <> ""
        Select Case True
            Case fn Like "EN.*", fn Like "FR.*", fn Like "ES.*"
            Case Else
                With Workbooks.Open(myDir & fn).Sheets(1).Cells(1).CurrentRegion
                    .Parent.AutoFilterMode = False
                    For Each e In Array("EN", "FR", "ES")
                        .AutoFilter 1, e
                        If Workbooks(e & ".xlsx").Sheets(1).Cells(1) = "" Then
                            .Copy Workbooks(e & ".xlsx").Sheets(1).Cells(1)
                        Else
                            .Offset(1).Copy Workbooks(e & ".xlsx").Sheets(1).Range("a" & Rows.Count).End(xlUp)(2)
                        End If
                    Next
                    .Parent.Parent.Close False
                End With
        End Select
        fn = Dir
    Loop
    For Each e In x
        Workbooks(e & ".xlsx").Close True
    Next
End Sub

I tested here and working.

For some reason it doesn´t work properly here. All 3 files I get after runnig the macro are completely empty

Can you just run the code and the read msg if any comes up?

Sub test()
    Dim myDir As String, fn As String, e, x
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show Then myDir = .SelectedItems(1) & "\"
    End With
    If myDir = "" Then Exit Sub
    x = Array("EN", "FR", "ES")
    For Each e In x
        With Workbooks.Add
            .SaveAs myDir & e & ".xlsx"
        End With
    Next
    fn = Dir(myDir & "*.xls")
    If fn = "" Then MsgBox "No file found"
    Do While fn <> ""
        MsgBox fn
        Select Case True
            Case fn Like "EN.*", fn Like "FR.*", fn Like "ES.*"
            Case Else
                With Workbooks.Open(myDir & fn).Sheets(1).Cells(1).CurrentRegion
                    .Parent.AutoFilterMode = False
                    For Each e In Array("EN", "FR", "ES")
                        .AutoFilter 1, e
                        If Workbooks(e & ".xlsx").Sheets(1).Cells(1) = "" Then
                            .Copy Workbooks(e & ".xlsx").Sheets(1).Cells(1)
                        Else
                            .Offset(1).Copy Workbooks(e & ".xlsx").Sheets(1).Range("a" & Rows.Count).End(xlUp)(2)
                        End If
                    Next
                    .Parent.Parent.Close False
                End With
        End Select
        fn = Dir
    Loop
    For Each e In x
        Workbooks(e & ".xlsx").Close True
    Next
End Sub

I´ve got the following messages:
"ES.xlsx", "FR.xlsx", "EN.xlsx" and the files are empty as well

That means you have no excel file other than newly created files.

I wouldn´t be quite sure, cause I tried with both ways... with existing files namend "ES, "EN" and "FR" and without them and I´ve got the same message for both runnings.
The information is not being "exported" into the 3 new files.

Of course, there's no file to be processed according to the result of the messages.

Just place your sample file "Table_Sample.xls" in the folder and rum the code then select that folder.

Duplicate............

I finally could use it!!! Thanks a lot and above all for your patience with me!!!