Logo GNT
Actualités Tests & Guides Bons Plans
GTA 6 iPhone 17 Copilot Switch 2 Temu ChatGPT Tesla
OVH Cloud OVH Cloud
Entraide Applications Microsoft Office Excel boucle

boucle

2 réponses
Avatar
MaDe
J'ai une macro assez 'bournine' et je voudrais qu'il y ai des boucles pour
l'ouverture des fichiers (vu que c'est les meme instrustion 5 fois) ainsi que
sur mes modif de variable et le choix du nombre de fichier gas_X que j'ouvre
(x=1 à 10), mais je ne c'est pas comment faire.
MErci d'avance a vous tous !

MADe

voici ma macro (longue je sais) :

Sub Macro()
'
' Macro Macro
'

fileToOpen = Application _
.GetOpenFilename("Plot Files (*.plot), *.plot")

Workbooks.OpenText Filename:=fileToOpen, Origin:=xlWindows _
, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False,
Comma:=False, _
Space:=True, Other:=False, FieldInfo:=Array(Array(1, 9), Array(2,
1), Array(3 _
, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Cells.Replace What:=".", Replacement:=",", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False

Sheets("gas").Select
Sheets("gas").Name = "gas_1"
Sheets("gas_1").Move After:=Workbooks("classeur_pour_macro.xls").Sheets(1)


fileToOpen = Application _
.GetOpenFilename("Plot Files (*.plot), *.plot")

Workbooks.OpenText Filename:=fileToOpen, Origin:=xlWindows _
, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False,
Comma:=False, _
Space:=True, Other:=False, FieldInfo:=Array(Array(1, 9), Array(2,
1), Array(3 _
, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Cells.Replace What:=".", Replacement:=",", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False

Sheets("gas").Select
Sheets("gas").Name = "gas_2"
Sheets("gas_2").Move After:=Workbooks("classeur_pour_macro.xls").Sheets(2)


fileToOpen = Application _
.GetOpenFilename("Plot Files (*.plot), *.plot")

Workbooks.OpenText Filename:=fileToOpen, Origin:=xlWindows _
, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False,
Comma:=False, _
Space:=True, Other:=False, FieldInfo:=Array(Array(1, 9), Array(2,
1), Array(3 _
, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Cells.Replace What:=".", Replacement:=",", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False

Sheets("gas").Select
Sheets("gas").Name = "gas_3"
Sheets("gas_3").Move After:=Workbooks("classeur_pour_macro.xls").Sheets(3)

fileToOpen = Application _
.GetOpenFilename("Plot Files (*.plot), *.plot")

Workbooks.OpenText Filename:=fileToOpen, Origin:=xlWindows _
, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False,
Comma:=False, _
Space:=True, Other:=False, FieldInfo:=Array(Array(1, 9), Array(2,
1), Array(3 _
, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Cells.Replace What:=".", Replacement:=",", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False

Sheets("gas").Select
Sheets("gas").Name = "gas_4"
Sheets("gas_4").Move After:=Workbooks("classeur_pour_macro.xls").Sheets(4)

fileToOpen = Application _
.GetOpenFilename("Plot Files (*.plot), *.plot")

Workbooks.OpenText Filename:=fileToOpen, Origin:=xlWindows _
, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False,
Comma:=False, _
Space:=True, Other:=False, FieldInfo:=Array(Array(1, 9), Array(2,
1), Array(3 _
, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Cells.Replace What:=".", Replacement:=",", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False

Sheets("gas").Select
Sheets("gas").Name = "gas_5"
Sheets("gas_5").Move After:=Workbooks("classeur_pour_macro.xls").Sheets(5)

fileToOpen = Application _
.GetOpenFilename("Plot Files (*.plot), *.plot")

Workbooks.OpenText Filename:=fileToOpen, Origin:=xlWindows _
, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False,
Comma:=False, _
Space:=True, Other:=False, FieldInfo:=Array(Array(1, 9), Array(2,
1), Array(3 _
, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Cells.Replace What:=".", Replacement:=",", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False

Sheets("gas").Select
Sheets("gas").Name = "gas_6"
Sheets("gas_6").Move After:=Workbooks("classeur_pour_macro.xls").Sheets(6)

' Sheets("Feuil1").Select
' Application.DisplayAlerts = False
' ActiveWindow.SelectedSheets.Delete

Workbooks.Add
ActiveWorkbook.SaveAs Filename:="C:\sarnet\traitement_essai.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Sheets("Feuil3").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Sheets("Feuil2").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
' Windows("traitement_essai.xls").Activate
' der = ThisWorkbook.Sheets.Count
Windows("Classeur_pour_macro.xls").Activate
Sheets("gas_1").Select
Sheets("gas_1").Copy After:=Workbooks("traitement_essai.xls").Sheets(1)
Windows("Classeur_pour_macro.xls").Activate
Sheets("gas_2").Select
Sheets("gas_2").Copy After:=Workbooks("traitement_essai.xls").Sheets(2)
Windows("Classeur_pour_macro.xls").Activate
Sheets("gas_3").Select
Sheets("gas_3").Copy After:=Workbooks("traitement_essai.xls").Sheets(3)
Windows("Classeur_pour_macro.xls").Activate
Sheets("gas_4").Select
Sheets("gas_4").Copy After:=Workbooks("traitement_essai.xls").Sheets(4)
Windows("Classeur_pour_macro.xls").Activate
Sheets("gas_5").Select
Sheets("gas_5").Copy After:=Workbooks("traitement_essai.xls").Sheets(5)
Windows("Classeur_pour_macro.xls").Activate
Sheets("gas_6").Select
Sheets("gas_6").Copy After:=Workbooks("traitement_essai.xls").Sheets(6)

Sheets("Feuil1").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete

Sheets("gas_1").Select
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*10^-5"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B543"), Type:=xlFillDefault
Range("B2:B543").Select
Selection.NumberFormat = "0.00"


Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("D1").Select
ActiveCell.FormulaR1C1 = "P_R (Bar)"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*10^-10"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D543"), Type:=xlFillDefault
Range("D2:D543").Select
Selection.NumberFormat = "0.00"
ActiveWindow.ScrollRow = 1

Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Range("F1").Select
ActiveCell.FormulaR1C1 = "T_R (°C)"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]*10^-5)-273.1"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F543"), Type:=xlFillDefault
Range("F2:F543").Select
Selection.NumberFormat = "0.00"
ActiveWindow.ScrollRow = 1

Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Range("H1").Select
ActiveCell.FormulaR1C1 = "RH_R (%)"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*10^-5"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H543"), Type:=xlFillDefault
Range("H2:H543").Select
Selection.NumberFormat = "0.00"
ActiveWindow.ScrollRow = 1

Range("J1").Select
ActiveCell.FormulaR1C1 = "AE-CONC*7000"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*7000*10^-5"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J543"), Type:=xlFillDefault
Range("J2:J543").Select
ActiveWindow.ScrollRow = 1
'
******************************************************************************
Sheets("gas_2").Select
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*10^-5"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B543"), Type:=xlFillDefault
Range("B2:B543").Select
Selection.NumberFormat = "0.00"

Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("D1").Select
ActiveCell.FormulaR1C1 = "P_R (Bar)"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*10^-10"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D543"), Type:=xlFillDefault
Range("D2:D543").Select
Selection.NumberFormat = "0.00"
ActiveWindow.ScrollRow = 1

Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Range("F1").Select
ActiveCell.FormulaR1C1 = "T_R (°C)"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]*10^-5)-273.1"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F543"), Type:=xlFillDefault
Range("F2:F543").Select
Selection.NumberFormat = "0.00"
ActiveWindow.ScrollRow = 1

Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Range("H1").Select
ActiveCell.FormulaR1C1 = "RH_R (%)"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*10^-5"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H543"), Type:=xlFillDefault
Range("H2:H543").Select
Selection.NumberFormat = "0.00"
ActiveWindow.ScrollRow = 1

Range("J1").Select
ActiveCell.FormulaR1C1 = "AE-CONC*7000"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*7000*10^-5"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J543"), Type:=xlFillDefault
Range("J2:J543").Select
ActiveWindow.ScrollRow = 1
'
******************************************************************************
Sheets("gas_3").Select
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*10^-5"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B543"), Type:=xlFillDefault
Range("B2:B543").Select
Selection.NumberFormat = "0.00"

Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("D1").Select
ActiveCell.FormulaR1C1 = "P_R (Bar)"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*10^-10"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D543"), Type:=xlFillDefault
Range("D2:D543").Select
Selection.NumberFormat = "0.00"
ActiveWindow.ScrollRow = 1

Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Range("F1").Select
ActiveCell.FormulaR1C1 = "T_R (°C)"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]*10^-5)-273.1"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F543"), Type:=xlFillDefault
Range("F2:F543").Select
Selection.NumberFormat = "0.00"
ActiveWindow.ScrollRow = 1

Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Range("H1").Select
ActiveCell.FormulaR1C1 = "RH_R (%)"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*10^-5"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H543"), Type:=xlFillDefault
Range("H2:H543").Select
Selection.NumberFormat = "0.00"
ActiveWindow.ScrollRow = 1

Range("J1").Select
ActiveCell.FormulaR1C1 = "AE-CONC*7000"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*7000*10^-5"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J543"), Type:=xlFillDefault
Range("J2:J543").Select
ActiveWindow.ScrollRow = 1
'
******************************************************************************
Sheets("gas_4").Select
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*10^-5"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B543"), Type:=xlFillDefault
Range("B2:B543").Select
Selection.NumberFormat = "0.00"

Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("D1").Select
ActiveCell.FormulaR1C1 = "P_R (Bar)"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*10^-10"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D543"), Type:=xlFillDefault
Range("D2:D543").Select
Selection.NumberFormat = "0.00"
ActiveWindow.ScrollRow = 1

Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Range("F1").Select
ActiveCell.FormulaR1C1 = "T_R (°C)"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]*10^-5)-273.1"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F543"), Type:=xlFillDefault
Range("F2:F543").Select
Selection.NumberFormat = "0.00"
ActiveWindow.ScrollRow = 1

Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Range("H1").Select
ActiveCell.FormulaR1C1 = "RH_R (%)"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*10^-5"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H543"), Type:=xlFillDefault
Range("H2:H543").Select
Selection.NumberFormat = "0.00"
ActiveWindow.ScrollRow = 1

Range("J1").Select
ActiveCell.FormulaR1C1 = "AE-CONC*7000"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*7000*10^-5"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J543"), Type:=xlFillDefault
Range("J2:J543").Select
ActiveWindow.ScrollRow = 1
'
******************************************************************************
Sheets("gas_5").Select
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*10^-5"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B543"), Type:=xlFillDefault
Range("B2:B543").Select
Selection.NumberFormat = "0.00"

Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("D1").Select
ActiveCell.FormulaR1C1 = "P_R (Bar)"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*10^-10"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D543"), Type:=xlFillDefault
Range("D2:D543").Select
Selection.NumberFormat = "0.00"
ActiveWindow.ScrollRow = 1

Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Range("F1").Select
ActiveCell.FormulaR1C1 = "T_R (°C)"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]*10^-5)-273.1"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F543"), Type:=xlFillDefault
Range("F2:F543").Select
Selection.NumberFormat = "0.00"
ActiveWindow.ScrollRow = 1

Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Range("H1").Select
ActiveCell.FormulaR1C1 = "RH_R (%)"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*10^-5"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H543"), Type:=xlFillDefault
Range("H2:H543").Select
Selection.NumberFormat = "0.00"
ActiveWindow.ScrollRow = 1

Range("J1").Select
ActiveCell.FormulaR1C1 = "AE-CONC*7000"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*7000*10^-5"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J543"), Type:=xlFillDefault
Range("J2:J543").Select
ActiveWindow.ScrollRow = 1
'
******************************************************************************
Sheets("gas_6").Select
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*10^-5"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B543"), Type:=xlFillDefault
Range("B2:B543").Select
Selection.NumberFormat = "0.00"

Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("D1").Select
ActiveCell.FormulaR1C1 = "P_R (Bar)"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*10^-10"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D543"), Type:=xlFillDefault
Range("D2:D543").Select
Selection.NumberFormat = "0.00"
ActiveWindow.ScrollRow = 1

Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Range("F1").Select
ActiveCell.FormulaR1C1 = "T_R (°C)"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]*10^-5)-273.1"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F543"), Type:=xlFillDefault
Range("F2:F543").Select
Selection.NumberFormat = "0.00"
ActiveWindow.ScrollRow = 1

Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Range("H1").Select
ActiveCell.FormulaR1C1 = "RH_R (%)"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*10^-5"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H543"), Type:=xlFillDefault
Range("H2:H543").Select
Selection.NumberFormat = "0.00"
ActiveWindow.ScrollRow = 1

Range("J1").Select
ActiveCell.FormulaR1C1 = "AE-CONC*7000"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*7000*10^-5"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J543"), Type:=xlFillDefault
Range("J2:J543").Select
ActiveWindow.ScrollRow = 1
'
******************************************************************************


' graphique Aerosol_mass_in_gas

Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("gas_1").Range("J2:J543"),
PlotBy _
:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:= _
"Aerosol_mass_in_gas"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Aerosol mass in gas"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time (s)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Mass (g)"
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With

With ActiveChart.Axes(xlValue)
.MinimumScale = 0
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlCustom
.CrossesAt = 0
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowNone, LegendKey:=False
ActiveChart.Axes(xlCategory).Select
With Selection.Border
.Weight = xlHairline
.LineStyle = xlAutomatic
End With
With Selection
.MajorTickMark = xlOutside
.MinorTickMark = xlOutside
.TickLabelPosition = xlNextToAxis
End With
With ActiveChart.Axes(xlCategory)
.CrossesAt = 1
.TickLabelSpacing = 40
.TickMarkSpacing = 40
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=gas_1!R2C2:R543C2"
ActiveChart.SeriesCollection(1).Values = "=gas_1!R2C10:R543C10"
ActiveChart.SeriesCollection(1).Name = "=""Gas_1"""
ActiveChart.SeriesCollection(2).Values = "=gas_2!R2C10:R543C10"
ActiveChart.SeriesCollection(2).Name = "=""Gas_2"""
ActiveChart.SeriesCollection(3).Values = "=gas_3!R2C10:R543C10"
ActiveChart.SeriesCollection(3).Name = "=""Gas_3"""
ActiveChart.SeriesCollection(4).Values = "=gas_4!R2C10:R543C10"
ActiveChart.SeriesCollection(4).Name = "=""Gas_4"""
ActiveChart.SeriesCollection(5).Values = "=gas_5!R2C10:R543C10"
ActiveChart.SeriesCollection(5).Name = "=""Gas_5"""
ActiveChart.SeriesCollection(6).Values = "=gas_6!R2C10:R543C10"
ActiveChart.SeriesCollection(6).Name = "=""Gas_6"""
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.NumberFormat = "0"
ActiveChart.Axes(xlValue).AxisTitle.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = xlHorizontal
End With
Selection.Left = 60
Selection.Top = 40
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.Left = 674
Selection.Top = 402
ActiveChart.ChartArea.Select
ActiveChart.Legend.Select
Selection.Left = 150
Selection.Top = 350
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With

With ActiveChart.PageSetup
.LeftFooter = "&F"
.CenterFooter = "&D"
.RightFooter = "&P sur &N"
.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)
.ChartSize = xlFullPage
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.BlackAndWhite = False
.Zoom = 100
End With


Sheets("Aerosol_mass_in_gas").Select
Sheets("Aerosol_mass_in_gas").Move After:=Sheets(5)
' end graph

' graphique Containment Pressure

Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("gas_1").Range("D2:D543"),
PlotBy _
:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:= _
"Containment_pressure"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Containment pressure"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time (s)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "P (Bar)"
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With

With ActiveChart.Axes(xlValue)
.MinimumScale = 1
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlCustom
.CrossesAt = 1
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowNone, LegendKey:=False
ActiveChart.Axes(xlCategory).Select
With Selection.Border
.Weight = xlHairline
.LineStyle = xlAutomatic
End With
With Selection
.MajorTickMark = xlOutside
.MinorTickMark = xlOutside
.TickLabelPosition = xlNextToAxis
End With
With ActiveChart.Axes(xlCategory)
.CrossesAt = 1
.TickLabelSpacing = 40
.TickMarkSpacing = 40
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=gas_1!R2C2:R543C2"
ActiveChart.SeriesCollection(1).Values = "=gas_1!R2C4:R543C4"
ActiveChart.SeriesCollection(1).Name = "=""Gas_1"""
ActiveChart.SeriesCollection(2).Values = "=gas_2!R2C4:R543C4"
ActiveChart.SeriesCollection(2).Name = "=""Gas_2"""
ActiveChart.SeriesCollection(3).Values = "=gas_3!R2C4:R543C4"
ActiveChart.SeriesCollection(3).Name = "=""Gas_3"""
ActiveChart.SeriesCollection(4).Values = "=gas_4!R2C4:R543C4"
ActiveChart.SeriesCollection(4).Name = "=""Gas_4"""
ActiveChart.SeriesCollection(5).Values = "=gas_5!R2C4:R543C4"
ActiveChart.SeriesCollection(5).Name = "=""Gas_5"""
ActiveChart.SeriesCollection(6).Values = "=gas_6!R2C4:R543C4"
ActiveChart.SeriesCollection(6).Name = "=""Gas_6"""
ActiveChart.Axes(xlCategory).Select
Selection.TickLabels.NumberFormat = "0"
ActiveChart.Axes(xlValue).AxisTitle.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = xlHorizontal
End With
Selection.Left = 60
Selection.Top = 40
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.Left = 674
Selection.Top = 402
ActiveChart.ChartArea.Select
ActiveChart.Legend.Select
Selection.Left = 150
Selection.Top = 350
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With

With ActiveChart.PageSetup
.LeftFooter = "&F"
.CenterFooter = "&D"
.RightFooter = "&P sur &N"
.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)
.ChartSize = xlFullPage
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.BlackAndWhite = False
.Zoom = 100
End With


Sheets("Containment_pressure").Select
Sheets("Containment_pressure").Move After:=Sheets(6)
' end graph

Windows("Classeur_pour_macro.xls").Activate
Sheets("gas_1").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Sheets("gas_2").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Sheets("gas_3").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Sheets("gas_4").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Sheets("gas_5").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Sheets("gas_6").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Windows("traitement_essai.xls").Activate
End Sub
Signaler un problème avec ce contenu

2 réponses

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Avatar
isabelle
bonjour MADe,

tu pourrais mettre cette boucle,

For i = 1 To 10
fileToOpen = Application _
.GetOpenFilename("Plot Files (*.plot), *.plot")

Workbooks.OpenText Filename:=fileToOpen, Origin:=xlWindows _
, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:úlse, Comma:úlse, _
Space:=True, Other:úlse, FieldInfo:=Array(Array(1, 9), Array(2, 1), Array(3 _
, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Cells.Replace What:=".", Replacement:=",", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:úlse

Sheets("gas").Select
Sheets("gas").Name = "gas_" & i
Sheets("gas_1").Move After:=Workbooks("classeur_pour_macro.xls").Sheets(i)
Next

'et celle ci,

For i = 1 To 6
Windows("Classeur_pour_macro.xls").Activate
Sheets("gas_" & i).Select
Sheets("gas_" & i).Copy After:=Workbooks("traitement_essai.xls").Sheets(i)
Next

'et celle ci

For i = 1 To 6
Sheets("gas_" & i).Select
For y = 2 To 10 Step 2
Columns(y).Insert Shift:=xlToRight
Cells(2, y).FormulaR1C1 = "=RC[-1]*10^-5"
Cells(2, y).AutoFill Destination:=Range(Cells(2, y), Cells(543, y)), Type:=xlFillDefault
Range(Cells(2, y), Cells(543, y)).NumberFormat = "0.00"
Next
Next

comme tu voie le principe est toujours le même,

a+

isabelle
Avatar
MaDe
CA m'a beaucoup aider , par contre il y a ti un solution pour les graphiques
pour mettre des boucles et d'avoir le choix du nombre de series dans diverses
feuilles.

Merci encore.

MaDe


bonjour MADe,

tu pourrais mettre cette boucle,

For i = 1 To 10
fileToOpen = Application _
.GetOpenFilename("Plot Files (*.plot), *.plot")

Workbooks.OpenText Filename:=fileToOpen, Origin:=xlWindows _
, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:úlse, Comma:úlse, _
Space:=True, Other:úlse, FieldInfo:=Array(Array(1, 9), Array(2, 1), Array(3 _
, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Cells.Replace What:=".", Replacement:=",", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:úlse

Sheets("gas").Select
Sheets("gas").Name = "gas_" & i
Sheets("gas_1").Move After:=Workbooks("classeur_pour_macro.xls").Sheets(i)
Next

'et celle ci,

For i = 1 To 6
Windows("Classeur_pour_macro.xls").Activate
Sheets("gas_" & i).Select
Sheets("gas_" & i).Copy After:=Workbooks("traitement_essai.xls").Sheets(i)
Next

'et celle ci

For i = 1 To 6
Sheets("gas_" & i).Select
For y = 2 To 10 Step 2
Columns(y).Insert Shift:=xlToRight
Cells(2, y).FormulaR1C1 = "=RC[-1]*10^-5"
Cells(2, y).AutoFill Destination:=Range(Cells(2, y), Cells(543, y)), Type:=xlFillDefault
Range(Cells(2, y), Cells(543, y)).NumberFormat = "0.00"
Next
Next

comme tu voie le principe est toujours le même,

a+

isabelle