Macro marche en francais mais ne marche pas en allemand
d
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.
Bonsoir
Une piste
En Français : Sheets("Feuil1")
En Anglais : Sheets("Sheet1")
Surement d'autre
d
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?
Bonsoir
Et si tu précisais ce qui ne fonctionne pas exactement. Plus simple non ?
VBA est toujours en Anglais.
A bientôt
d
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
re
Et si tu mettais ici la macro qui te pose un souci. Là on peut pas savoir.
d
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 Subre,
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
d
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