Bonjour Aurélie Machut ,

I am familiar with your problem, since I am based in Switzerland, too. What I sometimes do, is save the source text from the project I got from my customer (Fichier > Enregistrement avancé > Enregistrer la source sous) and then count the standard lines in Word. Or I simply copy the text from the source column in Studio into a Word document and count the standards lines (that works for any kind of file).

Best regards,

Annemieke

Hello Annemieke, thanks for sharing. This is exactly what I'd like to do automatically. I'm using an excel file but I'm sure there's a way to speed up this. Let's see!

Aurélie Machut Annemieke Stössel-Lub 

Here's a short idea...

The script is this:

Sub GenerateTranslationReport()
    Dim wsData As Worksheet
    Dim wsReport As Worksheet
    Dim lastRow As Long, i As Long
    Dim totalCharacters As Long
    Dim charsPerLine As Integer, linesPerPage As Integer
    Dim ratePerLine As Double ' Use Double for compatibility with InputBox
    Dim lineCount As Double, pageCount As Double, totalCost As Double

    ' Use the active worksheet for data
    Set wsData = ActiveSheet

    ' Default values for prompts
    charsPerLine = 55
    linesPerPage = 30
    ratePerLine = 2.2 ' Default rate, prompt without currency symbol

    ' Prompt for user input with defaults
    charsPerLine = Application.InputBox("Enter characters per line (default is 55):", "Characters Per Line", charsPerLine, Type:=1)
    linesPerPage = Application.InputBox("Enter lines per page (default is 30):", "Lines Per Page", linesPerPage, Type:=1)
    ratePerLine = Application.InputBox("Enter rate per line in CHF (default is 2.2, do not include currency symbol):", "Rate Per Line", ratePerLine, Type:=1)

    ' Calculate total characters excluding header row
    With wsData
        lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
        For i = 2 To lastRow
            totalCharacters = totalCharacters + Len(.Cells(i, "C").Value)
        Next i
    End With

    ' Calculate line and page counts, and total cost
    lineCount = totalCharacters / charsPerLine
    pageCount = lineCount / linesPerPage
    totalCost = lineCount * ratePerLine

    ' Find the first blank worksheet or create a new one for the report
    On Error Resume Next
    Set wsReport = ThisWorkbook.Sheets("Translation Report")
    If wsReport Is Nothing Then
        Set wsReport = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        wsReport.Name = "Translation Report"
    End If
    On Error GoTo 0

    ' Output the report
    With wsReport
        .Cells.Clear
        .Cells(1, 1).Value = "Total Characters"
        .Cells(1, 2).Value = totalCharacters
        .Cells(2, 1).Value = "Line Count"
        .Cells(2, 2).Value = lineCount
        .Cells(3, 1).Value = "Page Count"
        .Cells(3, 2).Value = pageCount
        .Cells(4, 1).Value = "Total Cost (CHF)"
        .Cells(4, 2).Value = totalCost
    End With

    MsgBox "Report generated successfully in the 'Translation Report' worksheet.", vbInformation
End Sub

Aurélie Machut Annemieke Stössel-Lub 

Here's a short idea...

The script is this:

Sub GenerateTranslationReport()
    Dim wsData As Worksheet
    Dim wsReport As Worksheet
    Dim lastRow As Long, i As Long
    Dim totalCharacters As Long
    Dim charsPerLine As Integer, linesPerPage As Integer
    Dim ratePerLine As Double ' Use Double for compatibility with InputBox
    Dim lineCount As Double, pageCount As Double, totalCost As Double

    ' Use the active worksheet for data
    Set wsData = ActiveSheet

    ' Default values for prompts
    charsPerLine = 55
    linesPerPage = 30
    ratePerLine = 2.2 ' Default rate, prompt without currency symbol

    ' Prompt for user input with defaults
    charsPerLine = Application.InputBox("Enter characters per line (default is 55):", "Characters Per Line", charsPerLine, Type:=1)
    linesPerPage = Application.InputBox("Enter lines per page (default is 30):", "Lines Per Page", linesPerPage, Type:=1)
    ratePerLine = Application.InputBox("Enter rate per line in CHF (default is 2.2, do not include currency symbol):", "Rate Per Line", ratePerLine, Type:=1)

    ' Calculate total characters excluding header row
    With wsData
        lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
        For i = 2 To lastRow
            totalCharacters = totalCharacters + Len(.Cells(i, "C").Value)
        Next i
    End With

    ' Calculate line and page counts, and total cost
    lineCount = totalCharacters / charsPerLine
    pageCount = lineCount / linesPerPage
    totalCost = lineCount * ratePerLine

    ' Find the first blank worksheet or create a new one for the report
    On Error Resume Next
    Set wsReport = ThisWorkbook.Sheets("Translation Report")
    If wsReport Is Nothing Then
        Set wsReport = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        wsReport.Name = "Translation Report"
    End If
    On Error GoTo 0

    ' Output the report
    With wsReport
        .Cells.Clear
        .Cells(1, 1).Value = "Total Characters"
        .Cells(1, 2).Value = totalCharacters
        .Cells(2, 1).Value = "Line Count"
        .Cells(2, 2).Value = lineCount
        .Cells(3, 1).Value = "Page Count"
        .Cells(3, 2).Value = pageCount
        .Cells(4, 1).Value = "Total Cost (CHF)"
        .Cells(4, 2).Value = totalCost
    End With

    MsgBox "Report generated successfully in the 'Translation Report' worksheet.", vbInformation
End Sub

Hi Paul, thanks a lot for your effort. That's an idea!

Hi Aurélie Machut , Annemieke Stössel-Lub ,

We've released a new build that should fix the issue mentioned.

Please install the new one from the integrated appstore and let us know if all ok.

Regards,

Oana