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 64 65 66 67
|
Option Explicit
Sub TestEnLaissantLExtensionEnCsv()
Dim NomDuRepertoire As String, NomDuFichierCsv As String
NomDuRepertoire = "XXXXX\" ' A adapter
NomDuFichierCsv = "query.csv"
Importer_Csv_ModeTexte NomDuRepertoire, NomDuFichierCsv
ChangerLesValeurs2 ActiveSheet, 1
End Sub
Sub Importer_Csv_ModeTexte(ByVal Repertoire As String, ByVal Fichier As String)
Workbooks.OpenText Filename:=Repertoire & "\" & Fichier, _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 2), Array(14, 1), Array(15, 1), _
Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1)), TrailingMinusNumbers:=True
Cells.EntireColumn.AutoFit
End Sub
Sub ChangerLesValeurs2(ByVal FeuilleCsv As Worksheet, ByVal LigneDeTitre As Long)
Dim I As Long, J As Long
Dim DerniereLigne As Long, DerniereColonne As Long
Dim MaDate As Variant
With FeuilleCsv
DerniereLigne = .UsedRange.Rows.Count
DerniereColonne = .UsedRange.Columns.Count
For I = 1 To DerniereColonne
If InStr(1, .Cells(LigneDeTitre, I), "Date", vbTextCompare) > 0 Then
For J = LigneDeTitre + 1 To DerniereLigne
If .Cells(J, I) <> "" Then
MaDate = Split(.Cells(J, I), " ")
.Cells(J, I) = Format(MoulinetteDate(MaDate(0)), "dd/mm/yyyy")
End If
Next J
End If
Next I
End With
End Sub
Function MoulinetteDate(ByVal DateEnTexte As String) As Variant
Dim MesSlashs As Variant
Application.Volatile
MesSlashs = Split(DateEnTexte, "/")
If UBound(MesSlashs) = 2 Then
MoulinetteDate = DateSerial(CLng(MesSlashs(2)), CLng(MesSlashs(1)), CLng(MesSlashs(0)))
End If
End Function |
Partager