Changing Excel language from German to English

Question

I got a macro, previously made in German Excel Version, for another market (English Speakers).

I am dealing with big amount of data and last things are Charts which are working automatically. One button, save as and done. When I changed my language on Excel into English, I made corrections in the program itself (like the Name of Sheets or Charts), but still is giving me an error.

Sub Button2_Click()

Dim fd As FileDialog
Dim FileChosen As Integer
Dim FileName As String
Dim tempWB As Workbook
Dim i As Integer
Dim templateLocation As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

' *** Template Location to define ***
MsgBox "Please define your Templates folder!", vbOKOnly + vbInformation, "Information!"
templateLocation = GetTemplateLocation()

Set fd = Application.FileDialog(msoFileDialogFilePicker)

' *** Define the location to load certain file ***
If templateLocation = "" Then
Exit Sub
End If
fd.InitialFileName = "Q:\Objekt"
MsgBox "Upload your files for Macro Charts!", vbOKOnly + vbExclamation, "Information!"
fd.InitialView = msoFileDialogViewList
fd.AllowMultiSelect = True

FileChosen = fd.Show
If FileChosen = -1 Then
For i = 1 To fd.SelectedItems.Count
    Set tempWB = Workbooks.Open(fd.SelectedItems(i))
    Call ReadDataFromSourceFile(tempWB, templateLocation)
Next i
End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Private Sub ReadDataFromSourceFile(src As Workbook, templateLocation As String)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


' *** Creating Charts ***
'Inlet Heating
Range("A:A,J:K").Select
ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers).Select
ActiveChart.SetSourceData Source:=Range("Table1!$A:$A,Table1!$J:$K")
ActiveChart.ApplyChartTemplate ( _
    templateLocation & "\Inlet_heating.crtx" _
    )
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).MinimumScaleIsAuto = True
ActiveChart.Axes(xlCategory).MaximumScaleIsAuto = True
ActiveChart.Axes(xlCategory).MajorUnit = 1
ActiveSheet.Shapes("Diagramm 1").Height = 240.9448818898
ActiveSheet.Shapes("Diagramm 1").Width = 453.5433070866
ActiveChart.ChartTitle.Select
Selection.Caption = "CS - Inlet_heating ()"
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.Caption = "Temperature (°C)"

'Inlet Pressure
Columns("A:C").Select
ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers).Select
ActiveChart.SetSourceData Source:=Range("Table1!$A:$C")
ActiveChart.ApplyChartTemplate ( _
    templateLocation & "\Inlet_pressure.crtx" _
    )
ActiveSheet.ChartObjects("Diagramm 2").Activate
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).MinimumScaleIsAuto = True
ActiveChart.Axes(xlCategory).MaximumScaleIsAuto = True
ActiveChart.Axes(xlCategory).MajorUnit = 1
ActiveSheet.Shapes("Diagramm 2").Height = 240.9448818898
ActiveSheet.Shapes("Diagramm 2").Width = 453.5433070866
ActiveChart.ChartTitle.Select
Selection.Caption = "CS - Inlet_pressure ()"
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.Caption = "Pressure (mbar)"

'Module Temperature 1
Range("A:A,D:F").Select
ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers).Select
ActiveChart.SetSourceData Source:=Range("Table1!$A:$A,Table1!$D:$F")
ActiveChart.ApplyChartTemplate ( _
    templateLocation & "\Module_temperature.crtx")
ActiveSheet.ChartObjects("Diagramm 3").Activate
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).MinimumScaleIsAuto = True
ActiveChart.Axes(xlCategory).MaximumScaleIsAuto = True
ActiveChart.Axes(xlCategory).MajorUnit = 1
ActiveSheet.Shapes("Diagramm 3").Height = 240.9448818898
ActiveSheet.Shapes("Diagramm 3").Width = 453.5433070866
ActiveChart.ChartTitle.Select
Selection.Caption = "CS - C1 - CC ()"
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.Caption = "Temperature (°C)"

'Module Temperature 2
Range("A:A,G:I").Select
ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers).Select
ActiveChart.SetSourceData Source:=Range("Table1!$A:$A,Table1!$G:$I")
ActiveChart.ApplyChartTemplate ( _
    templateLocation & "\Module_temperature.crtx")
ActiveSheet.ChartObjects("Diagramm 4").Activate
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).MinimumScaleIsAuto = True
ActiveChart.Axes(xlCategory).MaximumScaleIsAuto = True
ActiveChart.Axes(xlCategory).MajorUnit = 1
ActiveSheet.Shapes("Diagramm 4").Height = 240.9448818898
ActiveSheet.Shapes("Diagramm 4").Width = 453.5433070866
ActiveChart.ChartTitle.Select
Selection.Caption = "CS - C2 - CC ()"
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.Caption = "Temperature (°C)"

'Outlet Concentration
Sheets("Table2").Select
Columns("A:E").Select
ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers).Select
ActiveChart.SetSourceData Source:=Range("Table2!$A:$E")
ActiveChart.ApplyChartTemplate ( _
    templateLocation & "\Outlet_concentration.crtx")
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).MinimumScaleIsAuto = True
ActiveChart.Axes(xlCategory).MaximumScaleIsAuto = True
ActiveChart.Axes(xlCategory).MajorUnit = 1
ActiveSheet.Shapes("Diagramm 1").Height = 240.9448818898
ActiveSheet.Shapes("Diagramm 1").Width = 453.5433070866
ActiveChart.ChartTitle.Select
Selection.Caption = "CS - Outlet_concentration ()"
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.Caption = "Concentration (ppb)"

'Pressure_Switch
Sheets("Table3").Select
Columns("A:C").Select
ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers).Select
ActiveChart.SetSourceData Source:=Range("Table3!$A:$C")
ActiveChart.ApplyChartTemplate ( _
    templateLocation & "\Pressure_switch.crtx")
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).MinimumScaleIsAuto = True
ActiveChart.Axes(xlCategory).MaximumScaleIsAuto = True
ActiveChart.Axes(xlCategory).MajorUnit = 1
ActiveSheet.Shapes("Diagramm 1").Height = 240.9448818898
ActiveSheet.Shapes("Diagramm 1").Width = 453.5433070866
ActiveChart.ChartTitle.Select
Selection.Caption = "CS - Pressure_switch ()"
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.Caption = "Pressure (mbar)"

'Positioning and combining on Sheet1
Sheets("Table1").Select
Application.CommandBars("Format Object").Visible = False
ActiveSheet.ChartObjects("Diagramm 4").Activate
ActiveSheet.Shapes("Diagramm 4").IncrementLeft 480
ActiveSheet.Shapes("Diagramm 4").IncrementTop 223
Range("U15").Select
ActiveSheet.ChartObjects("Diagramm 3").Activate
ActiveSheet.Shapes("Diagramm 3").IncrementLeft 480
ActiveSheet.Shapes("Diagramm 3").IncrementTop -22
Range("O8").Select
ActiveWindow.SmallScroll Down:=6
ActiveSheet.ChartObjects("Diagramm 2").Activate
ActiveSheet.Shapes("Diagramm 2").IncrementLeft 27
ActiveSheet.Shapes("Diagramm 2").IncrementTop 223
Range("L11").Select
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveSheet.Shapes("Diagramm 1").IncrementLeft 27
ActiveSheet.Shapes("Diagramm 1").IncrementTop -22
Range("L9").Select

Sheets("Table2").Select
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.Parent.Cut
Sheets("Table1").Select
Range("C6").Select
ActiveSheet.Paste

Sheets("Table3").Select
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.Parent.Cut
Sheets("Table1").Select
Range("C22").Select
ActiveSheet.Paste

' *** Close and SaveAs in the same Folder where we took original file ***
        Application.ActiveWorkbook.Close

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Function GetTemplateLocation() As String
Dim fldr As FileDialog
Dim sItem As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select Template location"
    .AllowMultiSelect = False
    .InitialFileName = Application.DefaultFilePath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
NextCode:
GetTemplateLocation = sItem
Set fldr = Nothing
End Function

Can you provide us with some source code, error message, ...? — Dominique, Dec 20 '18 at 08:17
Yes, the code is up! error is in line: ActiveSheet.ChartObjects("Diagramm 1").Activate — M1rzÄ, Dec 20 '18 at 08:31
@Dominique and this appears only when I Change my Excel language.. — M1rzÄ, Dec 20 '18 at 08:37

score 1 · Accepted Answer · answered Dec 20 '18 at 09:01

1

You're assuming that you have a chart Diagramm 1 in your sheet, but in other versions of Excel, the name will be different.

You should avoid to work with ActiveSheet, ActiveChart and Select to access any objects. I strongly advice to read How to avoid using Select in Excel VBA

In your case, instead of Selecting the created chart and work with ActiveChart, assign the result of the AddChart2 method to a variable. Your code could look like this (I suggest you use a worksheet variable instead of ActiveSheet, but that's not the point for the moment so I keep it here):

Dim sh As Shape, ch As Chart
Set sh = ActiveSheet.Shapes.AddChart2(240, xlXYScatterLinesNoMarkers)
sh.Name = "Inlet_heating"
Set ch = sh.Chart
With ch
    .SetSourceData Source:=Range("Table1!$A:$A,Table1!$J:$K")
    .ApplyChartTemplate ( templateLocation & "\Inlet_heating.crtx")
    .Axes(xlCategory).MinimumScaleIsAuto = True
    ....
end with

answered Dec 20 '18 at 09:01

FunThomas

23,043
3
18
34

Do I Need to place it before every Chart? Somehow it is not working as I wanted.. Charts are usually "made" with desctiption...but I didnt get it so... (I am not good in VBA and so on).. – M1rzÄ Dec 20 '18 at 12:59
You have do do this for every chart. My advice would be to do this for the first chart (save the code for the others and remove it from the sub). Then check if it works as expected. Remove `ActiveChart` from all statements. – FunThomas Dec 20 '18 at 13:41
.ChartObjects("Diagramm 1").Activate It makes an error, same one actually, that I had at very beginning. Dont you think it has something to do because I recorded everything on German, and now I wanna use it on English? Some language changes in code for instance? – M1rzÄ Dec 20 '18 at 14:17
Like I wrote: Creating a chart in an English version of Excel will not create "Diagramm 1" (instead, "Chart 1"). Don't access the chart by name (except if you have assigned it with your code, see 3rd line of my example code). Instead, assign the created chart into a variable and work with that. – FunThomas Dec 20 '18 at 14:21
I did it! It works now! Thank you once more for your help!! – M1rzÄ Dec 20 '18 at 14:36

Changing Excel language from German to English

1 Answers1