Macro: Bilingual songs (translation line by line)

edited July 2021 in General Discussion

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


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()


   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


   ze_en_de = ze_en_de + 1

   ze_en = ze_en + 1

   End If


End Sub

I hope somebody could use this Macro for his work at Church.

Sign In or Register to comment.