Macro: Bilingual songs (translation line by line)
Hello we often sing English songs with German translation in church. The text then often looks like this:
English text
German text
English text
German text
etc.
Here is the Soloution to avoid writing English Text; German Text; English Text; German Text... by hand
To avoid having to write everything by hand I asked for a solution in the German forum. The user: Bertl has sent me a macro for Excel which automatically arranges the orignal text and the translation alternately one below the other. Many thanks also at this point for it, without him I would not have been able to do this.
I have now adapted his code so that blank lines are also copied.
To run the macro you have to create an excel file with 3 sheets.
In the first sheet you put the Original Text
In the second sheet you put the Translation
In the third sheet you get the song text in the correct order, after starting the marco
Here is the code (works in Excel and Libre Office):
Option VBASupport 1 (Delete row in Excel, only needed in Libre Office)
Sub Openlp()
'Variablendeklaration
Dim lngLast As Long
Dim ze_en As Long
Dim ze_de As Long
Dim ze_en_de As Long
Dim Offset As Long
'Variablen initalisieren
ze_en = 1
ze_de = 1
ze_en_de = 1
Offset = 0
lngLast = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
Do While ze_en <= lngLast
Sheets(3).Cells(ze_en_de, 1) = Sheets(1).Cells(ze_en, 1) &"" (Excel geht auch ohne &"")
Sheets(3).Cells(ze_en_de + 1, 1) = Sheets(2).Cells(ze_en, 1) '(without Formatting Tags)
'Sheets(3).Cells(ze_en_de + 1, 1) = & "{tl}" Sheets(2).Cells(ze_en, 1) & "{/tl}" (with Formatting Tags [Example: tl])
If Sheets(1).Cells(ze_en, 1) <> "" Then
ze_en_de = ze_en_de + 2
ze_en = ze_en + 1
Else
ze_en_de = ze_en_de + 1
ze_en = ze_en + 1
End If
Loop
End Sub
I hope somebody could use this Macro for his work at Church.