1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63
| Public Sub telecharger()
Dim strURL As String, i As Long, x As Long
strURL = "http://www.ecb.europa.eu/paym/coll/assets/html/dla/EA/ea_all_150703.txt"
Workbooks.Open Filename:=strURL
Columns("A:W").Select
Columns("A:W").EntireColumn.AutoFit
Sheets.Add After:=Sheets(Sheets.Count)
Sheets.Add After:=Sheets(Sheets.Count)
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "L1A"
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "L1B"
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "L1C"
Sheets("ea_all_150703").Select
For i = 2 To 40000
If Range("c" & i) = "L1C" Then
If Range("F" & i) = "EUR" Or Range("F" & i) = "ATS" Or Range("F" & i) = "BEF" Or Range("F" & i) = "CYP" Or Range("F" & i) = "DEM" Or Range("F" & i) = "ESP" Or Range("F" & i) = "EEK" Or Range("F" & i) = "FIM" Or Range("F" & i) = "FRF" Or Range("F" & i) = "GRD" Or Range("F" & i) = "IEP" Or Range("F" & i) = "ITL" Or Range("F" & i) = "LUF" Or Range("F" & i) = "NLG" Or Range("F" & i) = "PTE" Or Range("F" & i) = "SIT" Or Range("F" & i) = "SKK" Or Range("F" & i) = "LVL" And Range("M" & i) = "IRAT" Or Range("M" & i) = "IRDE" Or Range("M" & i) = "IRFI" Or Range("M" & i) = "IRFR" Or Range("M" & i) = "IRNL" Or Range("M" & i) = "IRDK" Or Range("M" & i) = "IRSE" Then
Sheets("ea_all_150703").Select
Range("A" & i).Copy
Sheets("L1C").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Sheets("ea_all_150703").Select
End If
End If
If Range("c" & i) = "L1B" Then
If Range("F" & i) = "EUR" Or Range("F" & i) = "ATS" Or Range("F" & i) = "BEF" Or Range("F" & i) = "CYP" Or Range("F" & i) = "DEM" Or Range("F" & i) = "EPS" Or Range("F" & i) = "EEK" Or Range("F" & i) = "FIM" Or Range("F" & i) = "FRF" Or Range("F" & i) = "GRD" Or Range("F" & i) = "IEP" Or Range("F" & i) = "ITL" Or Range("F" & i) = "LUF" Or Range("F" & i) = "NLG" Or Range("F" & i) = "PTE" Or Range("F" & i) = "SIT" Or Range("F" & i) = "SKK" Or Range("F" & i) = "LVL" And Range("M" & i) = "IRAT" Or Range("M" & i) = "IRDE" Or Range("M" & i) = "IRFI" Or Range("M" & i) = "IRFR" Or Range("M" & i) = "IRNL" Or Range("M" & i) = "IRDK" Or Range("M" & i) = "IRSE" Then
Sheets("ea_all_150703").Select
Range("A" & i).Copy
Sheets("L1B").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Sheets("ea_all_150703").Select
End If
End If
If Range("c" & i) = "L1A" Then
If Range("F" & i) = "EUR" Or Range("F" & i) = "ATS" Or Range("F" & i) = "BEF" Or Range("F" & i) = "CYP" Or Range("F" & i) = "DEM" Or Range("F" & i) = "ESP" Or Range("F" & i) = "EEK" Or Range("F" & i) = "FIM" Or Range("F" & i) = "FRF" Or Range("F" & i) = "GRD" Or Range("F" & i) = "IEP" Or Range("F" & i) = "ITL" Or Range("F" & i) = "LUF" Or Range("F" & i) = "NLG" Or Range("F" & i) = "PTE" Or Range("F" & i) = "SIT" Or Range("F" & i) = "SKK" Or Range("F" & i) = "LVL" And Range("M" & i) = "IRAT" Or Range("M" & i) = "IRDE" Or Range("M" & i) = "IRFI" Or Range("M" & i) = "IRFR" Or Range("M" & i) = "IRNL" Or Range("M" & i) = "IRDK" Or Range("M" & i) = "IRSE" Then
Sheets("ea_all_150703").Select
Range("A" & i).Copy
Sheets("L1A").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Sheets("ea_all_150703").Select
End If
End If
If IsEmpty(ActiveCell.Value) Then
Exit For
End If
Next i
End Sub |
Partager