Macro marche en francais mais ne marche pas en allemand

Le 30/05/2011 à 19:41

Bonjour tout le le monde,

j'ai un fichier excel 2010 avec des macros qui marche bien en français mais qui ne marche pas en anglais.

qqu'un a une idée pourquoi ça ne marche pas?

merci pour votre aide.

Le 30/05/2011 à 20:41

Bonsoir

Une piste

En Français : Sheets("Feuil1")

En Anglais : Sheets("Sheet1")

Surement d'autre

Le 30/05/2011 à 21:49

Bonsoir,

merci pour ton message. je dois donner plus de précisions : en fait les macros ont été développées en vba donc en anglais. je ne vois pas pourquoi ça ne marche pas en allemand?

Le 30/05/2011 à 22:56

Bonsoir

Et si tu précisais ce qui ne fonctionne pas exactement. Plus simple non ?

VBA est toujours en Anglais.

A bientôt

Le 31/05/2011 à 11:44

je n'ai pas excel allemand sur ma machine . je ne dispose que des ecrans envoyés par des utilisateurs allemands avec des messages d'erreur en allemand. je suis en train d'installer excel allemand pour le tester.

si vous avez des pistes pour savoir pourquoi une macro marche avec excel (français et anglais ) et pas en allemand ?

bonne journée

Le 31/05/2011 à 11:49

re

Et si tu mettais ici la macro qui te pose un souci. Là on peut pas savoir.

Le 31/05/2011 à 12:29

Bonjour

Private Sub Workbook_Open()
Dim num As Range
Dim num1 As Range
Dim pNom As String
Dim pId As String
Dim pIdSup As String
Dim pSupName As String
Dim pPers As String
Dim pMaxPer As String
Dim pPlant As String
Dim valPlant As Range

pNom = ActiveWorkbook.Name

With Application
        .DecimalSeparator = "."
        .ThousandsSeparator = " "
        .UseSystemSeparators = False
End With

'Application.DisplayAlerts = False

Application.ScreenUpdating = False

If Sheets("Properties").Cells(1, 256).Text <> "OK" Then
    Sheets("Properties").Cells(1, 256).Value = "OK"
    Sheets("MasterData").Select

    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DBQ=C:\Data\ABIS;DefaultDir=C:\Data\ABIS;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId" _
        ), Array( _
        "=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommit" _
        ), Array("Sync=Yes;")), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT DISTINCT TXTODBC.ID_SUPPLIER" & Chr(13) & "" & Chr(10) & "FROM TXTODBC.TXT TXTODBC WHERE TXTODBC.ID_SUPPLIER<>'null'")
        .Name = "ABIS_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = "C:\Data\ABIS\ABIS.dsn"
        .Refresh BackgroundQuery:=False
    End With
    nb = 2
    While Sheets("MasterData").Cells(nb, 1).Text <> ""
        If pId = "" Then
            pId = Sheets("MasterData").Cells(nb, 1).Text
        Else
            pId = pId + ";" + Sheets("MasterData").Cells(nb, 1).Text
        End If
        nb = nb + 1
    Wend
    Sheets("Properties").Select
    Sheets("Properties").Cells(8, 3).Value = pId
    Sheets("MasterData").Select
    pIdSup = pId
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DBQ=C:\Data\ABIS;DefaultDir=C:\Data\ABIS;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId" _
        ), Array( _
        "=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommit" _
        ), Array("Sync=Yes;")), Destination:=Range("B1"))
        .CommandText = Array( _
        "SELECT DISTINCT TXTODBC.YEAR_MONTH" & Chr(13) & "" & Chr(10) & "FROM TXTODBC.TXT TXTODBC" & Chr(13) & "" & Chr(10) & "WHERE (TXTODBC.YEAR_MONTH<>'6 month  Rolling')" _
        )
        .Name = "ABIS_2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = "C:\Data\ABIS\ABIS.dsn"
        .Refresh BackgroundQuery:=False
    End With

    nb = 2
    While Sheets("MasterData").Cells(nb, 2).Text <> ""
        If pPers = "" Then
            pPers = Sheets("MasterData").Cells(nb, 2).Text
        Else
            pPers = pPers + ";" + Sheets("MasterData").Cells(nb, 2).Text
        End If
        nb = nb + 1
    Wend
    Sheets("Properties").Select
    Sheets("Properties").Cells(10, 3).Value = pPers
    Sheets("MasterData").Select

    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DBQ=C:\Data\ABIS;DefaultDir=C:\Data\ABIS;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId" _
        ), Array( _
        "=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommit" _
        ), Array("Sync=Yes;")), Destination:=Range("C1"))
        .CommandText = Array( _
        "SELECT MAX(TXTODBC.YEAR_MONTH)" & Chr(13) & "" & Chr(10) & "FROM TXTODBC.TXT TXTODBC" & Chr(13) & "" & Chr(10) & "WHERE (TXTODBC.YEAR_MONTH<>'6 month  Rolling')" _
        )
        .Name = "ABIS_3"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = "C:\Data\ABIS\ABIS.dsn"
        .Refresh BackgroundQuery:=False
    End With

    nb = 2
    While Sheets("MasterData").Cells(nb, 3).Text <> ""
        If pMaxPer = "" Then
            pMaxPer = Sheets("MasterData").Cells(nb, 3).Text
        Else
            pMaxPer = pMaxPer + ";" + Sheets("MasterData").Cells(nb, 3).Text
        End If
        nb = nb + 1
    Wend
    Sheets("Properties").Select
    Sheets("Properties").Cells(12, 3).Value = pMaxPer
    Sheets("MasterData").Select

    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DBQ=C:\Data\ABIS;DefaultDir=C:\Data\ABIS;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId" _
        ), Array( _
        "=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommit" _
        ), Array("Sync=Yes;")), Destination:=Range("D1"))
        .CommandText = Array( _
        "SELECT DISTINCT TXTODBC.PLANT" & Chr(13) & "" & Chr(10) & "FROM TXTODBC.TXT TXTODBC where TXTODBC.PLANT<>'null'" & Chr(13) & "" & Chr(10) & "" _
        )
        .Name = "ABIS_4"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = "C:\Data\ABIS\ABIS.dsn"
        .Refresh BackgroundQuery:=False
    End With

    nb = 2
    While Sheets("MasterData").Cells(nb, 4).Text <> ""
        If pPlant = "" Then
            pPlant = Sheets("MasterData").Cells(nb, 4).Text
        Else
            pPlant = pPlant + ";" + Sheets("MasterData").Cells(nb, 4).Text
        End If
        nb = nb + 1
    Wend
    Sheets("Properties").Select
    Sheets("Properties").Cells(11, 3).Value = pPlant
    Sheets("MasterData").Select

    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DBQ=C:\Data\ABIS;DefaultDir=C:\Data\ABIS;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId" _
        ), Array( _
        "=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommit" _
        ), Array("Sync=Yes;")), Destination:=Range("E1"))
        .CommandText = Array( _
        "SELECT DISTINCT TXTODBC.SUPPLIER_NAME" & Chr(13) & "" & Chr(10) & "FROM TXTODBC.TXT TXTODBC" & Chr(13) & "" & Chr(10) & " WHERE (TXTODBC.SUPPLIER_NAME<>'null')" _
        )
        .Name = "ABIS_5"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = "C:\Data\ABIS\ABIS.dsn"
        .Refresh BackgroundQuery:=False
    End With

    nb = 2
    While Sheets("MasterData").Cells(nb, 5).Text <> ""
        If pSupName = "" Then
            pSupName = Sheets("MasterData").Cells(nb, 5).Text
        Else
            pSupName = pSupName + ";" + Sheets("MasterData").Cells(nb, 5).Text
        End If
        nb = nb + 1
    Wend
    Sheets("Properties").Select
    Sheets("Properties").Cells(9, 3).Value = pSupName
    Sheets("MasterData").Select

    'ActiveCell.FormulaR1C1 = "OK"

    Sheets("Properties").Select
    Range("C8:C9").Select
    Selection.Copy

    Sheets("chart").Select
    Range("F4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Sheets("Supplier Overview").Select
    Range("F4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Supplier Overview").Cells(6, 6).Value = Sheets("Properties").Cells(12, 3).Value
    Sheets("Supplier Overview").Visible = False

    Sheets("Properties").Select
    Range("C10:C11").Select
    Selection.Copy

    Range("C117").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Range("C11").Activate
    Selection.Replace What:="%%%*", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Sheets("Properties").Select
    Range("D19:K32").Select
    Selection.Copy
    Sheets("Chart").Select
    Range("L49").Select
    ActiveSheet.Paste
    Sheets("Chart").Cells(6, 6).Value = Sheets("Properties").Cells(12, 3).Value
    'Sheets("Supplier Overview").Select
    'Range("L49").Select
    'ActiveSheet.Paste

    Sheets("Data").Select
    Range("A1").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    Sheets("Data2").Select
    Range("A1").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    Sheets("Data").Select

    'Sheets("Supplier Overview").Visible = False

    i = 2
    Do While Sheets("MasterData").Cells(i, 4).Text <> ""
        plant = Trim(Sheets("MasterData").Cells(i, 4).Text)

        Sheets("chart").Select
        Sheets.Add
        Sheets("chart").Select
        Cells.Select
        Selection.Copy
        Sheets((i + 2)).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Sheets((i + 2)).Cells(3, 4).Value = plant
        Sheets((i + 2)).Select
        Sheets((i + 2)).Name = plant

            ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).XValues = "='" + plant + "'!R50C1:R62C1"
    ActiveChart.SeriesCollection(1).Values = "='" + plant + "'!R50C4:R62C4"
    ActiveChart.SeriesCollection(1).Name = "='" + plant + "'!R49C4"
    ActiveChart.SeriesCollection(2).XValues = "='" + plant + "'!R50C1:R62C1"
    ActiveChart.SeriesCollection(3).XValues = "='" + plant + "'!R50C1:R62C1"
    ActiveWindow.Visible = False
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).XValues = "='" + plant + "'!R50C1:R62C1"
    ActiveChart.SeriesCollection(1).Values = "='" + plant + "'!R50C5:R62C5"
    ActiveChart.SeriesCollection(1).Name = "='" + plant + "'!R49C5"
    ActiveWindow.Visible = False
    'Windows("624159_2008_05_16_16_49_00.xls").Activate
    ActiveSheet.ChartObjects("Chart 9").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).XValues = "='" + plant + "'!R50C1:R62C1"
    ActiveChart.SeriesCollection(1).Values = "='" + plant + "'!R50C6:R62C6"
    ActiveChart.SeriesCollection(1).Name = "='" + plant + "'!R49C6"
    ActiveChart.SeriesCollection(2).XValues = "='" + plant + "'!R50C1:R62C1"
    ActiveWindow.Visible = False
    'Windows("624159_2008_05_16_16_49_00.xls").Activate
    ActiveSheet.ChartObjects("Chart 8").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).XValues = "='" + plant + "'!R50C1:R62C1"
    ActiveChart.SeriesCollection(1).Values = "='" + plant + "'!R50C10:R62C10"
    ActiveChart.SeriesCollection(1).Name = "='" + plant + "'!R49C10"
    ActiveChart.SeriesCollection(2).XValues = "='" + plant + "'!R50C1:R62C1"
    ActiveChart.SeriesCollection(2).Values = "='" + plant + "'!R50C15:R62C15"
    ActiveChart.SeriesCollection(3).XValues = "='" + plant + "'!R50C1:R62C1"
    ActiveChart.SeriesCollection(3).Values = "='" + plant + "'!R50C16:R62C16"
    ActiveWindow.Visible = False
    'Windows("624159_2008_05_16_16_49_00.xls").Activate
    ActiveSheet.ChartObjects("Chart 5").Activate
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).XValues = "='" + plant + "'!R50C1:R62C1"
    ActiveChart.SeriesCollection(1).Values = "='" + plant + "'!R50C7:R62C7"
    ActiveChart.SeriesCollection(1).Name = "='" + plant + "'!R49C7"
    ActiveChart.SeriesCollection(2).XValues = "='" + plant + "'!R50C1:R62C1"
    ActiveChart.SeriesCollection(2).Values = "='" + plant + "'!R50C8:R62C8"
    ActiveChart.SeriesCollection(2).Name = "='" + plant + "'!R49C8"
    ActiveChart.SeriesCollection(3).XValues = "='" + plant + "'!R50C1:R62C1"
    ActiveChart.SeriesCollection(3).Values = "='" + plant + "'!R50C9:R62C9"
    ActiveChart.SeriesCollection(3).Name = "='" + plant + "'!R49C9"
    ActiveWindow.Visible = False
    'Windows("624159_2008_05_16_16_49_00.xls").Activate
    Range("A1").Select

        i = i + 1
     If Environ("REPORT_SERVER_CLOSE") = "YES" Then
            ActiveSheet.PageSetup.PrintArea = "$A$1:$K$63"
            With ActiveSheet.PageSetup
                .LeftHeader = ""
                .CenterHeader = ""
                .RightHeader = ""
                .LeftFooter = ""
                .CenterFooter = ""
                .RightFooter = ""
                .LeftMargin = Application.InchesToPoints(0.787401575)
                .RightMargin = Application.InchesToPoints(0.787401575)
                .TopMargin = Application.InchesToPoints(0.984251969)
                .BottomMargin = Application.InchesToPoints(0.984251969)
                .HeaderMargin = Application.InchesToPoints(0.4921259845)
                .FooterMargin = Application.InchesToPoints(0.4921259845)
                .PrintHeadings = False
                .PrintGridlines = False
                .PrintComments = xlPrintNoComments
                .PrintQuality = 300
                .CenterHorizontally = False
                .CenterVertically = False
                .Orientation = xlPortrait
                .Draft = False
                .PaperSize = xlPaperA4
                .FirstPageNumber = xlAutomatic
                .Order = xlDownThenOver
                .BlackAndWhite = False
                .Zoom = False
                .FitToPagesWide = 1
                .FitToPagesTall = 1
                .PrintErrors = xlPrintErrorsDisplayed
            End With
        End If
'        Sheets("chart").Select
 '       Sheets("chart").Copy After:=Sheets((i + 1))
'        Sheets("chart (2)").Select
'        Sheets("chart (2)").Name = plant
'        Range("A1").Select
'        Sheets(plant).Select
'        Sheets(plant).Cells(3, 4).Value = plant

    Loop

    Sheets("chart").Visible = False
    Sheets("Data").Visible = False
    Sheets("Data2").Visible = False
    Sheets("MasterData").Visible = False
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Sheets("Supplier  Overview").Select
    ActiveWorkbook.Save

    If Environ("REPORT_SERVER_CLOSE") = "YES" Then
        Sheets("Properties").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Supplier  Overview").Select
    Sheets("Supplier  Overview").Move Before:=Sheets(4)
    ActiveWorkbook.PrintOut Copies:=1, Collate:=True
        Set fs = CreateObject("Scripting.FileSystemObject")
        If fs.FileExists("C:\5_PANEL_PDF\" + pIdSup + "_" + Format(DateAdd("m", -1, Date), "mmmyyyy") + ".pdf") Then
            fs.deleteFile "C:\5_PANEL_PDF\" + pIdSup + "_" + Format(DateAdd("m", -1, Date), "mmmyyyy") + ".pdf"
        End If
        If fs.FileExists("C:\5_PANEL_PDF\" + pIdSup + "_" + Format(DateAdd("m", -2, Date), "mmmyyyy") + ".pdf") Then
            fs.deleteFile "C:\5_PANEL_PDF\" + pIdSup + "_" + Format(DateAdd("m", -2, Date), "mmmyyyy") + ".pdf"
        End If
        If fs.FileExists("C:\5_PANEL_PDF\" + pIdSup + "_" + Format(DateAdd("m", -3, Date), "mmmyyyy") + ".pdf") Then
            fs.deleteFile "C:\5_PANEL_PDF\" + pIdSup + "_" + Format(DateAdd("m", -3, Date), "mmmyyyy") + ".pdf"
        End If

        fs.movefile "C:\5_PANEL_PDF\Temp.pdf", "C:\5_PANEL_PDF\" + pIdSup + "_" + Format(DateAdd("m", -1, Date), "mmmyyyy") + ".pdf"
        ActiveWorkbook.Close (False)
        ActiveWorkbook.Close
    End If
End If
End Sub
Le 31/05/2011 à 13:23

re,

Vérifie que tes tableaux croisés sont bien nommés car dans ton code tu les nommes "PivotTable1" et il se peut qu'ils soient nommés autrement en fonction de la langue. Au besoin remplace "PivotTable1" par 1

L'instruction pourrait être --> ActiveSheet.PivotTables(1).PivotCache.Refresh

Sinon le code pourrait être simplifié à ce que je peux voir.

A te relire

Le 31/05/2011 à 14:03

Bonjour Dan,

merci pour ta reponse. je vais tester le fichier avec la modification que t'as apporté. je te tiendrai au courant.

je te souhaite une bonne fin de journée.

-- 31 Mai 2011, 16:09 --

salut dan

j'ai changé le code mais ça ne marche pas . ci joint le code et le message d'erreur ainsi que la ligne qui pose souci pour excel allemand. merci pour ton aide.

Private Sub Workbook_Open()
Dim num As Range
Dim num1 As Range
Dim pNom As String
Dim pId As String
Dim pIdSup As String
Dim pSupName As String
Dim pPers As String
Dim pMaxPer As String
Dim pPlant As String
Dim valPlant As Range

pNom = ActiveWorkbook.Name

With Application
        .DecimalSeparator = "."
        .ThousandsSeparator = " "
        .UseSystemSeparators = False
End With

'Application.DisplayAlerts = False

Application.ScreenUpdating = False

If Sheets("Properties").Cells(1, 256).Text <> "OK" Then
    Sheets("Properties").Cells(1, 256).Value = "OK"
    Sheets("MasterData").Select

    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DBQ=C:\Data\ABIS;DefaultDir=C:\Data\ABIS;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId" _
        ), Array( _
        "=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommit" _
        ), Array("Sync=Yes;")), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT DISTINCT TXTODBC.ID_SUPPLIER" & Chr(13) & "" & Chr(10) & "FROM TXTODBC.TXT TXTODBC WHERE TXTODBC.ID_SUPPLIER<>'null'")
        .Name = "ABIS_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = "C:\Data\ABIS\ABIS.dsn"
        .Refresh BackgroundQuery:=False
    End With
    nb = 2
    While Sheets("MasterData").Cells(nb, 1).Text <> ""
        If pId = "" Then
            pId = Sheets("MasterData").Cells(nb, 1).Text
        Else
            pId = pId + ";" + Sheets("MasterData").Cells(nb, 1).Text
        End If
        nb = nb + 1
    Wend
    Sheets("Properties").Select
    Sheets("Properties").Cells(8, 3).Value = pId
    Sheets("MasterData").Select
    pIdSup = pId
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DBQ=C:\Data\ABIS;DefaultDir=C:\Data\ABIS;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId" _
        ), Array( _
        "=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommit" _
        ), Array("Sync=Yes;")), Destination:=Range("B1"))
        .CommandText = Array( _
        "SELECT DISTINCT TXTODBC.YEAR_MONTH" & Chr(13) & "" & Chr(10) & "FROM TXTODBC.TXT TXTODBC" & Chr(13) & "" & Chr(10) & "WHERE (TXTODBC.YEAR_MONTH<>'6 month  Rolling')" _
        )
        .Name = "ABIS_2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = "C:\Data\ABIS\ABIS.dsn"
        .Refresh BackgroundQuery:=False
    End With

    nb = 2
    While Sheets("MasterData").Cells(nb, 2).Text <> ""
        If pPers = "" Then
            pPers = Sheets("MasterData").Cells(nb, 2).Text
        Else
            pPers = pPers + ";" + Sheets("MasterData").Cells(nb, 2).Text
        End If
        nb = nb + 1
    Wend
    Sheets("Properties").Select
    Sheets("Properties").Cells(10, 3).Value = pPers
    Sheets("MasterData").Select

    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DBQ=C:\Data\ABIS;DefaultDir=C:\Data\ABIS;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId" _
        ), Array( _
        "=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommit" _
        ), Array("Sync=Yes;")), Destination:=Range("C1"))
        .CommandText = Array( _
        "SELECT MAX(TXTODBC.YEAR_MONTH)" & Chr(13) & "" & Chr(10) & "FROM TXTODBC.TXT TXTODBC" & Chr(13) & "" & Chr(10) & "WHERE (TXTODBC.YEAR_MONTH<>'6 month  Rolling')" _
        )
        .Name = "ABIS_3"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = "C:\Data\ABIS\ABIS.dsn"
        .Refresh BackgroundQuery:=False
    End With

    nb = 2
    While Sheets("MasterData").Cells(nb, 3).Text <> ""
        If pMaxPer = "" Then
            pMaxPer = Sheets("MasterData").Cells(nb, 3).Text
        Else
            pMaxPer = pMaxPer + ";" + Sheets("MasterData").Cells(nb, 3).Text
        End If
        nb = nb + 1
    Wend
    Sheets("Properties").Select
    Sheets("Properties").Cells(12, 3).Value = pMaxPer
    Sheets("MasterData").Select

    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DBQ=C:\Data\ABIS;DefaultDir=C:\Data\ABIS;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId" _
        ), Array( _
        "=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommit" _
        ), Array("Sync=Yes;")), Destination:=Range("D1"))
        .CommandText = Array( _
        "SELECT DISTINCT TXTODBC.PLANT" & Chr(13) & "" & Chr(10) & "FROM TXTODBC.TXT TXTODBC where TXTODBC.PLANT<>'null'" & Chr(13) & "" & Chr(10) & "" _
        )
        .Name = "ABIS_4"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = "C:\Data\ABIS\ABIS.dsn"
        .Refresh BackgroundQuery:=False
    End With

    nb = 2
    While Sheets("MasterData").Cells(nb, 4).Text <> ""
        If pPlant = "" Then
            pPlant = Sheets("MasterData").Cells(nb, 4).Text
        Else
            pPlant = pPlant + ";" + Sheets("MasterData").Cells(nb, 4).Text
        End If
        nb = nb + 1
    Wend
    Sheets("Properties").Select
    Sheets("Properties").Cells(11, 3).Value = pPlant
    Sheets("MasterData").Select

    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DBQ=C:\Data\ABIS;DefaultDir=C:\Data\ABIS;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId" _
        ), Array( _
        "=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommit" _
        ), Array("Sync=Yes;")), Destination:=Range("E1"))
        .CommandText = Array( _
        "SELECT DISTINCT TXTODBC.SUPPLIER_NAME" & Chr(13) & "" & Chr(10) & "FROM TXTODBC.TXT TXTODBC" & Chr(13) & "" & Chr(10) & " WHERE (TXTODBC.SUPPLIER_NAME<>'null')" _
        )
        .Name = "ABIS_5"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = "C:\Data\ABIS\ABIS.dsn"
        .Refresh BackgroundQuery:=False
    End With

    nb = 2
    While Sheets("MasterData").Cells(nb, 5).Text <> ""
        If pSupName = "" Then
            pSupName = Sheets("MasterData").Cells(nb, 5).Text
        Else
            pSupName = pSupName + ";" + Sheets("MasterData").Cells(nb, 5).Text
        End If
        nb = nb + 1
    Wend
    Sheets("Properties").Select
    Sheets("Properties").Cells(9, 3).Value = pSupName
    Sheets("MasterData").Select

    'ActiveCell.FormulaR1C1 = "OK"

    Sheets("Properties").Select
    Range("C8:C9").Select
    Selection.Copy

    Sheets("chart").Select
    Range("F4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Sheets("Supplier Overview").Select
    Range("F4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Supplier Overview").Cells(6, 6).Value = Sheets("Properties").Cells(12, 3).Value
    Sheets("Supplier Overview").Visible = False

    Sheets("Properties").Select
    Range("C10:C11").Select
    Selection.Copy

    Range("C117").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Range("C11").Activate
    Selection.Replace What:="%%%*", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Sheets("Properties").Select
    Range("D19:K32").Select
    Selection.Copy
    Sheets("Chart").Select
    Range("L49").Select
    ActiveSheet.Paste
    Sheets("Chart").Cells(6, 6).Value = Sheets("Properties").Cells(12, 3).Value
    'Sheets("Supplier Overview").Select
    'Range("L49").Select
    'ActiveSheet.Paste

    Sheets("Data").Select
    Range("A1").Select
    ActiveSheet.PivotTables(1).PivotCache.Refresh
    Sheets("Data2").Select
    Range("A1").Select
    ActiveSheet.PivotTables(1).PivotCache.Refresh
    Sheets("Data").Select

    'Sheets("Supplier Overview").Visible = False

    i = 2
    Do While Sheets("MasterData").Cells(i, 4).Text <> ""
        plant = Trim(Sheets("MasterData").Cells(i, 4).Text)

        Sheets("chart").Select
        Sheets.Add
        Sheets("chart").Select
        Cells.Select
        Selection.Copy
        Sheets((i + 2)).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Sheets((i + 2)).Cells(3, 4).Value = plant
        Sheets((i + 2)).Select
        Sheets((i + 2)).Name = plant

            ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).XValues = "='" + plant + "'!R50C1:R62C1"
    ActiveChart.SeriesCollection(1).Values = "='" + plant + "'!R50C4:R62C4"
    ' le message d'erreur sur la ligne suivante : " Application defined or object defined error"
    ActiveChart.SeriesCollection(1).Name = "='" + plant + "'!R49C4"
    ActiveChart.SeriesCollection(2).XValues = "='" + plant + "'!R50C1:R62C1"
    ActiveChart.SeriesCollection(3).XValues = "='" + plant + "'!R50C1:R62C1"
    ActiveWindow.Visible = False
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).XValues = "='" + plant + "'!R50C1:R62C1"
    ActiveChart.SeriesCollection(1).Values = "='" + plant + "'!R50C5:R62C5"
    ActiveChart.SeriesCollection(1).Name = "='" + plant + "'!R49C5"
    ActiveWindow.Visible = False
    'Windows("624159_2008_05_16_16_49_00.xls").Activate
    ActiveSheet.ChartObjects("Chart 9").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).XValues = "='" + plant + "'!R50C1:R62C1"
    ActiveChart.SeriesCollection(1).Values = "='" + plant + "'!R50C6:R62C6"
    ActiveChart.SeriesCollection(1).Name = "='" + plant + "'!R49C6"
    ActiveChart.SeriesCollection(2).XValues = "='" + plant + "'!R50C1:R62C1"
    ActiveWindow.Visible = False
    'Windows("624159_2008_05_16_16_49_00.xls").Activate
    ActiveSheet.ChartObjects("Chart 8").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).XValues = "='" + plant + "'!R50C1:R62C1"
    ActiveChart.SeriesCollection(1).Values = "='" + plant + "'!R50C10:R62C10"
    ActiveChart.SeriesCollection(1).Name = "='" + plant + "'!R49C10"
    ActiveChart.SeriesCollection(2).XValues = "='" + plant + "'!R50C1:R62C1"
    ActiveChart.SeriesCollection(2).Values = "='" + plant + "'!R50C15:R62C15"
    ActiveChart.SeriesCollection(3).XValues = "='" + plant + "'!R50C1:R62C1"
    ActiveChart.SeriesCollection(3).Values = "='" + plant + "'!R50C16:R62C16"
    ActiveWindow.Visible = False
    'Windows("624159_2008_05_16_16_49_00.xls").Activate
    ActiveSheet.ChartObjects("Chart 5").Activate
    ActiveChart.Axes(xlValue).MajorGridlines.Select
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).XValues = "='" + plant + "'!R50C1:R62C1"
    ActiveChart.SeriesCollection(1).Values = "='" + plant + "'!R50C7:R62C7"
    ActiveChart.SeriesCollection(1).Name = "='" + plant + "'!R49C7"
    ActiveChart.SeriesCollection(2).XValues = "='" + plant + "'!R50C1:R62C1"
    ActiveChart.SeriesCollection(2).Values = "='" + plant + "'!R50C8:R62C8"
    ActiveChart.SeriesCollection(2).Name = "='" + plant + "'!R49C8"
    ActiveChart.SeriesCollection(3).XValues = "='" + plant + "'!R50C1:R62C1"
    ActiveChart.SeriesCollection(3).Values = "='" + plant + "'!R50C9:R62C9"
    ActiveChart.SeriesCollection(3).Name = "='" + plant + "'!R49C9"
    ActiveWindow.Visible = False
    'Windows("624159_2008_05_16_16_49_00.xls").Activate
    Range("A1").Select

        i = i + 1
     If Environ("REPORT_SERVER_CLOSE") = "YES" Then
            ActiveSheet.PageSetup.PrintArea = "$A$1:$K$63"
            With ActiveSheet.PageSetup
                .LeftHeader = ""
                .CenterHeader = ""
                .RightHeader = ""
                .LeftFooter = ""
                .CenterFooter = ""
                .RightFooter = ""
                .LeftMargin = Application.InchesToPoints(0.787401575)
                .RightMargin = Application.InchesToPoints(0.787401575)
                .TopMargin = Application.InchesToPoints(0.984251969)
                .BottomMargin = Application.InchesToPoints(0.984251969)
                .HeaderMargin = Application.InchesToPoints(0.4921259845)
                .FooterMargin = Application.InchesToPoints(0.4921259845)
                .PrintHeadings = False
                .PrintGridlines = False
                .PrintComments = xlPrintNoComments
                .PrintQuality = 300
                .CenterHorizontally = False
                .CenterVertically = False
                .Orientation = xlPortrait
                .Draft = False
                .PaperSize = xlPaperA4
                .FirstPageNumber = xlAutomatic
                .Order = xlDownThenOver
                .BlackAndWhite = False
                .Zoom = False
                .FitToPagesWide = 1
                .FitToPagesTall = 1
                .PrintErrors = xlPrintErrorsDisplayed
            End With
        End If
'        Sheets("chart").Select
 '       Sheets("chart").Copy After:=Sheets((i + 1))
'        Sheets("chart (2)").Select
'        Sheets("chart (2)").Name = plant
'        Range("A1").Select
'        Sheets(plant).Select
'        Sheets(plant).Cells(3, 4).Value = plant

    Loop

    Sheets("chart").Visible = False
    Sheets("Data").Visible = False
    Sheets("Data2").Visible = False
    Sheets("MasterData").Visible = False
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Sheets("Supplier  Overview").Select
    ActiveWorkbook.Save

    If Environ("REPORT_SERVER_CLOSE") = "YES" Then
        Sheets("Properties").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Supplier  Overview").Select
    Sheets("Supplier  Overview").Move Before:=Sheets(4)
    ActiveWorkbook.PrintOut Copies:=1, Collate:=True
        Set fs = CreateObject("Scripting.FileSystemObject")
        If fs.FileExists("C:\5_PANEL_PDF\" + pIdSup + "_" + Format(DateAdd("m", -1, Date), "mmmyyyy") + ".pdf") Then
            fs.deleteFile "C:\5_PANEL_PDF\" + pIdSup + "_" + Format(DateAdd("m", -1, Date), "mmmyyyy") + ".pdf"
        End If
        If fs.FileExists("C:\5_PANEL_PDF\" + pIdSup + "_" + Format(DateAdd("m", -2, Date), "mmmyyyy") + ".pdf") Then
            fs.deleteFile "C:\5_PANEL_PDF\" + pIdSup + "_" + Format(DateAdd("m", -2, Date), "mmmyyyy") + ".pdf"
        End If
        If fs.FileExists("C:\5_PANEL_PDF\" + pIdSup + "_" + Format(DateAdd("m", -3, Date), "mmmyyyy") + ".pdf") Then
            fs.deleteFile "C:\5_PANEL_PDF\" + pIdSup + "_" + Format(DateAdd("m", -3, Date), "mmmyyyy") + ".pdf"
        End If

        fs.movefile "C:\5_PANEL_PDF\Temp.pdf", "C:\5_PANEL_PDF\" + pIdSup + "_" + Format(DateAdd("m", -1, Date), "mmmyyyy") + ".pdf"
        ActiveWorkbook.Close (False)
        ActiveWorkbook.Close
    End If
End If
End Sub

-- 01 Juin 2011, 00:01 --

Bonjour

j'ai un souci avec une macro qui marche bien en excel Anglais et français mais ne marche pas dans l'excel allemand

ci joint le code

merci pour votre aide

    Private Sub Workbook_Open()
    Dim num As Range
    Dim num1 As Range
    Dim pNom As String
    Dim pId As String
    Dim pIdSup As String
    Dim pSupName As String
    Dim pPers As String
    Dim pMaxPer As String
    Dim pPlant As String
    Dim valPlant As Range

    pNom = ActiveWorkbook.Name

    With Application
            .DecimalSeparator = "."
            .ThousandsSeparator = " "
            .UseSystemSeparators = False
    End With

    'Application.DisplayAlerts = False

    Application.ScreenUpdating = False

    If Sheets("Properties").Cells(1, 256).Text <> "OK" Then
        Sheets("Properties").Cells(1, 256).Value = "OK"
        Sheets("MasterData").Select

        With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
            "ODBC;DBQ=C:\Data\ABIS;DefaultDir=C:\Data\ABIS;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId" _
            ), Array( _
            "=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommit" _
            ), Array("Sync=Yes;")), Destination:=Range("A1"))
            .CommandText = Array( _
            "SELECT DISTINCT TXTODBC.ID_SUPPLIER" & Chr(13) & "" & Chr(10) & "FROM TXTODBC.TXT TXTODBC WHERE TXTODBC.ID_SUPPLIER<>'null'")
            .Name = "ABIS_1"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .SourceConnectionFile = "C:\Data\ABIS\ABIS.dsn"
            .Refresh BackgroundQuery:=False
        End With
        nb = 2
        While Sheets("MasterData").Cells(nb, 1).Text <> ""
            If pId = "" Then
                pId = Sheets("MasterData").Cells(nb, 1).Text
            Else
                pId = pId + ";" + Sheets("MasterData").Cells(nb, 1).Text
            End If
            nb = nb + 1
        Wend
        Sheets("Properties").Select
        Sheets("Properties").Cells(8, 3).Value = pId
        Sheets("MasterData").Select
        pIdSup = pId
        With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
            "ODBC;DBQ=C:\Data\ABIS;DefaultDir=C:\Data\ABIS;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId" _
            ), Array( _
            "=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommit" _
            ), Array("Sync=Yes;")), Destination:=Range("B1"))
            .CommandText = Array( _
            "SELECT DISTINCT TXTODBC.YEAR_MONTH" & Chr(13) & "" & Chr(10) & "FROM TXTODBC.TXT TXTODBC" & Chr(13) & "" & Chr(10) & "WHERE (TXTODBC.YEAR_MONTH<>'6 month  Rolling')" _
            )
            .Name = "ABIS_2"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .SourceConnectionFile = "C:\Data\ABIS\ABIS.dsn"
            .Refresh BackgroundQuery:=False
        End With

        nb = 2
        While Sheets("MasterData").Cells(nb, 2).Text <> ""
            If pPers = "" Then
                pPers = Sheets("MasterData").Cells(nb, 2).Text
            Else
                pPers = pPers + ";" + Sheets("MasterData").Cells(nb, 2).Text
            End If
            nb = nb + 1
        Wend
        Sheets("Properties").Select
        Sheets("Properties").Cells(10, 3).Value = pPers
        Sheets("MasterData").Select

        With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
            "ODBC;DBQ=C:\Data\ABIS;DefaultDir=C:\Data\ABIS;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId" _
            ), Array( _
            "=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommit" _
            ), Array("Sync=Yes;")), Destination:=Range("C1"))
            .CommandText = Array( _
            "SELECT MAX(TXTODBC.YEAR_MONTH)" & Chr(13) & "" & Chr(10) & "FROM TXTODBC.TXT TXTODBC" & Chr(13) & "" & Chr(10) & "WHERE (TXTODBC.YEAR_MONTH<>'6 month  Rolling')" _
            )
            .Name = "ABIS_3"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .SourceConnectionFile = "C:\Data\ABIS\ABIS.dsn"
            .Refresh BackgroundQuery:=False
        End With

        nb = 2
        While Sheets("MasterData").Cells(nb, 3).Text <> ""
            If pMaxPer = "" Then
                pMaxPer = Sheets("MasterData").Cells(nb, 3).Text
            Else
                pMaxPer = pMaxPer + ";" + Sheets("MasterData").Cells(nb, 3).Text
            End If
            nb = nb + 1
        Wend
        Sheets("Properties").Select
        Sheets("Properties").Cells(12, 3).Value = pMaxPer
        Sheets("MasterData").Select

        With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
            "ODBC;DBQ=C:\Data\ABIS;DefaultDir=C:\Data\ABIS;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId" _
            ), Array( _
            "=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommit" _
            ), Array("Sync=Yes;")), Destination:=Range("D1"))
            .CommandText = Array( _
            "SELECT DISTINCT TXTODBC.PLANT" & Chr(13) & "" & Chr(10) & "FROM TXTODBC.TXT TXTODBC where TXTODBC.PLANT<>'null'" & Chr(13) & "" & Chr(10) & "" _
            )
            .Name = "ABIS_4"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .SourceConnectionFile = "C:\Data\ABIS\ABIS.dsn"
            .Refresh BackgroundQuery:=False
        End With

        nb = 2
        While Sheets("MasterData").Cells(nb, 4).Text <> ""
            If pPlant = "" Then
                pPlant = Sheets("MasterData").Cells(nb, 4).Text
            Else
                pPlant = pPlant + ";" + Sheets("MasterData").Cells(nb, 4).Text
            End If
            nb = nb + 1
        Wend
        Sheets("Properties").Select
        Sheets("Properties").Cells(11, 3).Value = pPlant
        Sheets("MasterData").Select

        With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
            "ODBC;DBQ=C:\Data\ABIS;DefaultDir=C:\Data\ABIS;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId" _
            ), Array( _
            "=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommit" _
            ), Array("Sync=Yes;")), Destination:=Range("E1"))
            .CommandText = Array( _
            "SELECT DISTINCT TXTODBC.SUPPLIER_NAME" & Chr(13) & "" & Chr(10) & "FROM TXTODBC.TXT TXTODBC" & Chr(13) & "" & Chr(10) & " WHERE (TXTODBC.SUPPLIER_NAME<>'null')" _
            )
            .Name = "ABIS_5"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .SourceConnectionFile = "C:\Data\ABIS\ABIS.dsn"
            .Refresh BackgroundQuery:=False
        End With

        nb = 2
        While Sheets("MasterData").Cells(nb, 5).Text <> ""
            If pSupName = "" Then
                pSupName = Sheets("MasterData").Cells(nb, 5).Text
            Else
                pSupName = pSupName + ";" + Sheets("MasterData").Cells(nb, 5).Text
            End If
            nb = nb + 1
        Wend
        Sheets("Properties").Select
        Sheets("Properties").Cells(9, 3).Value = pSupName
        Sheets("MasterData").Select

        'ActiveCell.FormulaR1C1 = "OK"

        Sheets("Properties").Select
        Range("C8:C9").Select
        Selection.Copy

        Sheets("chart").Select
        Range("F4").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

        Sheets("Supplier Overview").Select
        Range("F4").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Supplier Overview").Cells(6, 6).Value = Sheets("Properties").Cells(12, 3).Value
        Sheets("Supplier Overview").Visible = False

        Sheets("Properties").Select
        Range("C10:C11").Select
        Selection.Copy

        Range("C117").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

        Range("C11").Activate
        Selection.Replace What:="%%%*", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False

        Sheets("Properties").Select
        Range("D19:K32").Select
        Selection.Copy
        Sheets("Chart").Select
        Range("L49").Select
        ActiveSheet.Paste
        Sheets("Chart").Cells(6, 6).Value = Sheets("Properties").Cells(12, 3).Value
        'Sheets("Supplier Overview").Select
       'Range("L49").Select
       'ActiveSheet.Paste

        Sheets("Data").Select
        Range("A1").Select
        ActiveSheet.PivotTables(1).PivotCache.Refresh
        Sheets("Data2").Select
        Range("A1").Select
        ActiveSheet.PivotTables(1).PivotCache.Refresh
        Sheets("Data").Select

        'Sheets("Supplier Overview").Visible = False

        i = 2
        Do While Sheets("MasterData").Cells(i, 4).Text <> ""
            plant = Trim(Sheets("MasterData").Cells(i, 4).Text)

            Sheets("chart").Select
            Sheets.Add
            Sheets("chart").Select
            Cells.Select
            Selection.Copy
            Sheets((i + 2)).Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
            Sheets((i + 2)).Cells(3, 4).Value = plant
            Sheets((i + 2)).Select
            Sheets((i + 2)).Name = plant

                ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.Axes(xlValue).MajorGridlines.Select
        ActiveChart.PlotArea.Select
        ActiveChart.SeriesCollection(1).XValues = "='" + plant + "'!R50C1:R62C1"
        ActiveChart.SeriesCollection(1).Values = "='" + plant + "'!R50C4:R62C4"
        ' le message d'erreur sur la ligne suivante : " Application defined or object defined error"
       ActiveChart.SeriesCollection(1).Name = "='" + plant + "'!R49C4"
        ActiveChart.SeriesCollection(2).XValues = "='" + plant + "'!R50C1:R62C1"
        ActiveChart.SeriesCollection(3).XValues = "='" + plant + "'!R50C1:R62C1"
        ActiveWindow.Visible = False
        ActiveSheet.ChartObjects("Chart 2").Activate
        ActiveChart.PlotArea.Select
        ActiveChart.SeriesCollection(1).XValues = "='" + plant + "'!R50C1:R62C1"
        ActiveChart.SeriesCollection(1).Values = "='" + plant + "'!R50C5:R62C5"
        ActiveChart.SeriesCollection(1).Name = "='" + plant + "'!R49C5"
        ActiveWindow.Visible = False
        'Windows("624159_2008_05_16_16_49_00.xls").Activate
       ActiveSheet.ChartObjects("Chart 9").Activate
        ActiveChart.PlotArea.Select
        ActiveChart.SeriesCollection(1).XValues = "='" + plant + "'!R50C1:R62C1"
        ActiveChart.SeriesCollection(1).Values = "='" + plant + "'!R50C6:R62C6"
        ActiveChart.SeriesCollection(1).Name = "='" + plant + "'!R49C6"
        ActiveChart.SeriesCollection(2).XValues = "='" + plant + "'!R50C1:R62C1"
        ActiveWindow.Visible = False
        'Windows("624159_2008_05_16_16_49_00.xls").Activate
       ActiveSheet.ChartObjects("Chart 8").Activate
        ActiveChart.PlotArea.Select
        ActiveChart.SeriesCollection(1).XValues = "='" + plant + "'!R50C1:R62C1"
        ActiveChart.SeriesCollection(1).Values = "='" + plant + "'!R50C10:R62C10"
        ActiveChart.SeriesCollection(1).Name = "='" + plant + "'!R49C10"
        ActiveChart.SeriesCollection(2).XValues = "='" + plant + "'!R50C1:R62C1"
        ActiveChart.SeriesCollection(2).Values = "='" + plant + "'!R50C15:R62C15"
        ActiveChart.SeriesCollection(3).XValues = "='" + plant + "'!R50C1:R62C1"
        ActiveChart.SeriesCollection(3).Values = "='" + plant + "'!R50C16:R62C16"
        ActiveWindow.Visible = False
        'Windows("624159_2008_05_16_16_49_00.xls").Activate
       ActiveSheet.ChartObjects("Chart 5").Activate
        ActiveChart.Axes(xlValue).MajorGridlines.Select
        ActiveChart.PlotArea.Select
        ActiveChart.SeriesCollection(1).XValues = "='" + plant + "'!R50C1:R62C1"
        ActiveChart.SeriesCollection(1).Values = "='" + plant + "'!R50C7:R62C7"
        ActiveChart.SeriesCollection(1).Name = "='" + plant + "'!R49C7"
        ActiveChart.SeriesCollection(2).XValues = "='" + plant + "'!R50C1:R62C1"
        ActiveChart.SeriesCollection(2).Values = "='" + plant + "'!R50C8:R62C8"
        ActiveChart.SeriesCollection(2).Name = "='" + plant + "'!R49C8"
        ActiveChart.SeriesCollection(3).XValues = "='" + plant + "'!R50C1:R62C1"
        ActiveChart.SeriesCollection(3).Values = "='" + plant + "'!R50C9:R62C9"
        ActiveChart.SeriesCollection(3).Name = "='" + plant + "'!R49C9"
        ActiveWindow.Visible = False
        'Windows("624159_2008_05_16_16_49_00.xls").Activate
       Range("A1").Select

            i = i + 1
         If Environ("REPORT_SERVER_CLOSE") = "YES" Then
                ActiveSheet.PageSetup.PrintArea = "$A$1:$K$63"
                With ActiveSheet.PageSetup
                    .LeftHeader = ""
                    .CenterHeader = ""
                    .RightHeader = ""
                    .LeftFooter = ""
                    .CenterFooter = ""
                    .RightFooter = ""
                    .LeftMargin = Application.InchesToPoints(0.787401575)
                    .RightMargin = Application.InchesToPoints(0.787401575)
                    .TopMargin = Application.InchesToPoints(0.984251969)
                    .BottomMargin = Application.InchesToPoints(0.984251969)
                    .HeaderMargin = Application.InchesToPoints(0.4921259845)
                    .FooterMargin = Application.InchesToPoints(0.4921259845)
                    .PrintHeadings = False
                    .PrintGridlines = False
                    .PrintComments = xlPrintNoComments
                    .PrintQuality = 300
                    .CenterHorizontally = False
                    .CenterVertically = False
                    .Orientation = xlPortrait
                    .Draft = False
                    .PaperSize = xlPaperA4
                    .FirstPageNumber = xlAutomatic
                    .Order = xlDownThenOver
                    .BlackAndWhite = False
                    .Zoom = False
                    .FitToPagesWide = 1
                    .FitToPagesTall = 1
                    .PrintErrors = xlPrintErrorsDisplayed
                End With
            End If
    '        Sheets("chart").Select
    '       Sheets("chart").Copy After:=Sheets((i + 1))
    '        Sheets("chart (2)").Select
    '        Sheets("chart (2)").Name = plant
    '        Range("A1").Select
    '        Sheets(plant).Select
    '        Sheets(plant).Cells(3, 4).Value = plant

        Loop

        Sheets("chart").Visible = False
        Sheets("Data").Visible = False
        Sheets("Data2").Visible = False
        Sheets("MasterData").Visible = False
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
        Sheets("Supplier  Overview").Select
        ActiveWorkbook.Save

        If Environ("REPORT_SERVER_CLOSE") = "YES" Then
            Sheets("Properties").Select
        ActiveWindow.SelectedSheets.Visible = False
        Sheets("Supplier  Overview").Select
        Sheets("Supplier  Overview").Move Before:=Sheets(4)
        ActiveWorkbook.PrintOut Copies:=1, Collate:=True
            Set fs = CreateObject("Scripting.FileSystemObject")
            If fs.FileExists("C:\5_PANEL_PDF\" + pIdSup + "_" + Format(DateAdd("m", -1, Date), "mmmyyyy") + ".pdf") Then
                fs.deleteFile "C:\5_PANEL_PDF\" + pIdSup + "_" + Format(DateAdd("m", -1, Date), "mmmyyyy") + ".pdf"
            End If
            If fs.FileExists("C:\5_PANEL_PDF\" + pIdSup + "_" + Format(DateAdd("m", -2, Date), "mmmyyyy") + ".pdf") Then
                fs.deleteFile "C:\5_PANEL_PDF\" + pIdSup + "_" + Format(DateAdd("m", -2, Date), "mmmyyyy") + ".pdf"
            End If
            If fs.FileExists("C:\5_PANEL_PDF\" + pIdSup + "_" + Format(DateAdd("m", -3, Date), "mmmyyyy") + ".pdf") Then
                fs.deleteFile "C:\5_PANEL_PDF\" + pIdSup + "_" + Format(DateAdd("m", -3, Date), "mmmyyyy") + ".pdf"
            End If

            fs.movefile "C:\5_PANEL_PDF\Temp.pdf", "C:\5_PANEL_PDF\" + pIdSup + "_" + Format(DateAdd("m", -1, Date), "mmmyyyy") + ".pdf"
            ActiveWorkbook.Close (False)
            ActiveWorkbook.Close
        End If
    End If
    End Sub
Rechercher des sujets similaires à "macro marche francais pas allemand"