Bonjour a tous,
J'ai un souci lorsque je souhaite importer un fichier csv via une macro que j'ai creee sous excel. Et le titre de ce fichier csv est toujours une date.
En fait, afin d'importer le bon fichier via macro, j'utilise une boite de dialogue me demandant d'entrer la date recherchee. En fonction de la date rentree, la macro importe le fichier csv dont le titre (une date) correspond a la date que j'ai entree dans la boite de dialoque.
Tout marche bien si le jour de la date inscrite dans le titre du fichier csv est superieur a 9, c'est a dire entre 10 et 31. Seulement si ce jour est compris entre 1 et et 9, ca ne marche pas.
En d'autres termes, la macro n'accepte pas d'importer le fichier csv car son titre indique 09062008, mais fonctionne tres bien si son titre est 10062008. En fait le 0 entre en debut de date pose probleme.
Quelqu'un a t-il une idee??? Vous trouverez le script de la macro ci-dessous - bon courage!!
Merci d'avance pour votre aide.
CARALSOL
Application.ScreenUpdating = False
'
On Error GoTo ErrorHandler
Dim DateSaisie As Long
DateSaisie = InputBox("PNS FILE : Enter Date using the following format : DDMMYYYY")
Dim LaDate As Long
LaDate = DateSerial(CInt(Right(DateSaisie, 4)), CInt(Mid(DateSaisie, 3, 2)), _
CInt(Left(DateSaisie, 2)))
Sheets("C2C_PNS").Select
Columns("A:W").Select
Selection.ClearContents
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;\\todon010\projet\COMPTA\CPTA_CLIENTS_C2C\NFG\" _
& DateSaisie & "C2C_PNS.csv", Destination _
:=Range("A1"))
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1)
.Refresh BackgroundQuery:=False
End With
Sheets("C2C_PNS").Select
Selection.End(xlToRight).Select
ActiveSheet.Range("$A$1:$AD$25000").AutoFilter Field:=20
Selection.End(xlToLeft).Select
Range("A1").Select
Range("A1:AD1").Select
Selection.Font.Size = 16
Columns("A:AD").Select
Range(Selection, Selection.End(xlToRight)).Select
Columns("A:AD").EntireColumn.AutoFit
Selection.End(xlToLeft).Select
Range(Selection, Selection.End(xlToRight)).Select
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("H:J").Select
Selection.NumberFormat = "m/d/yyyy"
Selection.End(xlToLeft).Select
Columns("x:y").Select
Selection.ClearContents
Range("X2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(LEFT(RC[-8],3)=""411"",LEFT(RC[-8],3)=""416"")," & LaDate & " - RC[-13],"""")"
Range("Y2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",IF(RC24<0,""Current"",IF(AND(RC24>-1,RC24<31),""1-30 days"",IF(AND(RC24>30,RC24<61),""31-60 days"",IF(AND(RC24>60,RC24<91),""61-90 days"",IF(AND(RC24>90,RC24<181),""91-180 days"",IF(RC24>180,""More than 180 days"","""")))))))"
Range("Z2").Select
ActiveCell.FormulaR1C1 = "=IF(OR(RC[-1]="""",RC[-1]=""Current""),"""",RC[-12])"
Range("AA2").Select
ActiveCell.FormulaR1C1 = "=RC[-13]"
Range("X1").Select
Range("X2:AA2").Select
Selection.Copy
Range("X2:AA25000").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("X2:AA25000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("X1").Select
ActiveCell.FormulaR1C1 = "Nombre de jours de retard"
Range("Y1").Select
ActiveCell.FormulaR1C1 = "Tranche d'âge"
Range("Z1").Select
ActiveCell.FormulaR1C1 = "Impayés"
Range("AA1").Select
ActiveCell.FormulaR1C1 = "Encours Total"
Range("AA1").Select
Selection.End(xlToLeft).Select
Range("Y1").Select
Selection.End(xlToLeft).Select
Range("BA2").Select
ActiveCell.FormulaR1C1 = "A"
Range("BA3").Select
ActiveCell.FormulaR1C1 = "B"
Range("BA4").Select
ActiveCell.FormulaR1C1 = "C"
Range("BA5").Select
ActiveCell.FormulaR1C1 = "D"
Range("BA6").Select
ActiveCell.FormulaR1C1 = "E"
Range("BA7").Select
ActiveCell.FormulaR1C1 = "F"
Range("BA8").Select
ActiveCell.FormulaR1C1 = "G"
Range("BA9").Select
ActiveCell.FormulaR1C1 = "H"
Range("BA10").Select
ActiveCell.FormulaR1C1 = "I"
Range("BA11").Select
ActiveCell.FormulaR1C1 = "J"
Range("BA12").Select
ActiveCell.FormulaR1C1 = "K"
Range("BA13").Select
ActiveCell.FormulaR1C1 = "L"
Range("BA14").Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Range("BB2").Select
ActiveCell.FormulaR1C1 = "Dispute"
Range("BB3").Select
ActiveCell.FormulaR1C1 = "Payment terms"
Range("BB4").Select
ActiveCell.FormulaR1C1 = "Payment promised"
Range("BB5").Select
ActiveCell.FormulaR1C1 = "Payment made"
Range("BB6").Select
ActiveCell.FormulaR1C1 = "Repayment plan accepted"
Range("BB8").Select
ActiveCell.FormulaR1C1 = "Budget Issue"
Range("BB9").Select
ActiveCell.FormulaR1C1 = "Penalty invoices"
Range("BB10").Select
ActiveCell.FormulaR1C1 = "Legal action in progress"
Range("BB11").Select
ActiveCell.FormulaR1C1 = "Bankruptcy"
Range("BB12").Select
ActiveCell.FormulaR1C1 = "Payment by credit memo"
Range("BB13").Select
ActiveCell.FormulaR1C1 = "Compensation"
Range("BB14").Select
ActiveCell.FormulaR1C1 = "No feedback"
Range("BA15").Select
ActiveCell.FormulaR1C1 = "1"
Range("BA16").Select
ActiveCell.FormulaR1C1 = "2"
Range("BA17").Select
ActiveCell.FormulaR1C1 = "3"
Range("BA18").Select
ActiveCell.FormulaR1C1 = "4"
Range("BA19").Select
ActiveCell.FormulaR1C1 = "5"
Range("BA20").Select
ActiveCell.FormulaR1C1 = "6"
Range("BA21").Select
ActiveCell.FormulaR1C1 = "7"
Range("BA22").Select
ActiveCell.FormulaR1C1 = "50"
Range("BA23").Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Range("BB15").Select
ActiveCell.FormulaR1C1 = "Invoice not received"
Range("BB16").Select
ActiveCell.FormulaR1C1 = "Duplicated invoice"
Range("BB17").Select
ActiveCell.FormulaR1C1 = "PO acceptance problem"
Range("BB18").Select
ActiveCell.FormulaR1C1 = "Price / discount error"
Range("BB19").Select
ActiveCell.FormulaR1C1 = "Quantity error"
Range("BB20").Select
ActiveCell.FormulaR1C1 = "Quality problem"
Range("BB21").Select
ActiveCell.FormulaR1C1 = "No feedback"
Range("BB22").Select
ActiveCell.FormulaR1C1 = "X-rate difference"
Range("AB2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-9]=7,""No feedback"",IF(RC[-3]=""Current"",""Current"",IF(RC[-4]="""","""",IF(AND(RC[-10]="""",RC[-4]<91),""Clean Overdue < 3 months"",IF(AND(RC[-10]="""",RC[-4]>90),""Clean Overdue > 3 months"",IF(ISERROR(VLOOKUP(RC[-10],R2C[25]:R30C[26],2,FALSE)),"""",VLOOKUP(RC[-10],R2C[25]:R30C[26],2,FALSE)))))))"
Range("AC2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-10]=7,"""",IF(ISERROR(VLOOKUP(RC[-10],R2C[24]:R30C[25],2,FALSE)),"""",VLOOKUP(RC[-10],R2C[24]:R30C[25],2,FALSE)))"
Range("AB2:AC2").Select
Selection.Copy
Range("AB2:AC25000").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("AB2:AC25000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("AD2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-24],'Supplier Penalties list'!R1C[-29]:R508C[-29],1,FALSE)),'C2C_PNS'!RC[-2],""Supplier penalties"")"
Range("AD2").Select
Selection.AutoFill Destination:=Range("AD2:AD25000")
Range("AD2:AD25000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AB2").Select
ActiveCell.FormulaR1C1 = "=RC[2]"
Range("AB2").Select
Selection.AutoFill Destination:=Range("AB2:AB25000")
Range("AB2:AB25000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AD2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Range("AB2").Select
Range("AB1").Select
ActiveCell.FormulaR1C1 = "Description blocage"
Range("AC1").Select
ActiveCell.FormulaR1C1 = "Description litige"
Range("AB1").Select
Columns("BA:BB").Select
Selection.ClearContents
Range("X1").Select
Sheets("C2C_PNS").Select
Range("CC2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-64]="""",RC[-61],IF(RC[-64]=""P0"",""BA01-BA02"",IF(RC[-64]=""S0"",""BA04"",""BA05"")))"
Range("CC2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-64]="""",RC[-61],IF(RC[-64]=""P0"",""BA01-BA02"",IF(RC[-64]=""S0"",""BA04"",IF(RC[-64]=""B0"",""BA05"",IF(RC[-80]="""","""","""")))))"
Range("CC2").Select
Selection.Copy
Range("CC2:CC25000").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Cut
Range("CC2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Cut
Range("T2").Select
ActiveSheet.Paste
Range("T1").Select
Selection.End(xlToLeft).Select
Range("A2").Select
Sheets("C2C_PNS").Select
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
Columns("AA:AA").Select
Selection.NumberFormat = "#,##0.00"
Columns("Z:Z").Select
Selection.NumberFormat = "#,##0.00"
Columns("N:N").Select
Selection.NumberFormat = "#,##0.00"
Columns("L:L").Select
Selection.NumberFormat = "#,##0.00"
Columns("A:AC").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
ActiveWindow.DisplayGridlines = False
Columns("A:A").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("B:B").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("C:C").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("D:D").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("F:F").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("G:G").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("H:H").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("M:M").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("O:O").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("Q:Q").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("R:R").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("S:S").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("T:T").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("U:U").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("V:V").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("W:W").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("X:X").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:AD").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 48
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 48
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 48
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 48
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 48
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 48
End With
Columns("H:H").Select
Selection.NumberFormat = "@"
Range("AF2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-26],'Compte Gest Magik'!R3C1:R1734C2,2,0)),"""",VLOOKUP(RC[-26],'Compte Gest Magik'!R3C1:R1734C2,2,0))"
Selection.AutoFill Destination:=Range("AF2:AF25000")
Range("AG2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-10]="""",RC[-1],RC[-10])"
Selection.AutoFill Destination:=Range("AG2:AG25000")
Range("AG2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("W2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("AF:AG").Select
Selection.ClearContents
Range("AD2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-26],'Compte Gest Magik'!R3C3:R365C5,3,0)),"""",VLOOKUP(RC[-26],'Compte Gest Magik'!R3C3:R365C5,3,0))"
Range("AD2").Select
Selection.AutoFill Destination:=Range("AD2:AD25000")
Range("AD2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("AD1").Select
ActiveCell.FormulaR1C1 = "Catégorie Client"
Application.Run "PivotUpdate"
Exit Sub
ErrorHandler:
Dim Msg, Style, Title, Response
Msg = " ERROR !" & _
vbCrLf & " " & _
vbCrLf & " THE DATE FORMAT IS NOT VALID OR THE IMPORT FILE DOES NOT EXIST" & _
vbCrLf & " " & _
vbCrLf & " Please restart the import process using the proper format" & _
vbCrLf & ".............................................................................................."
Style = vbOK + vbExclamation + vbDefaultButton3
Title = "WARNING"
Response = MsgBox(Msg, Style, Title)
End Sub
Configuration: Windows XP
Internet Explorer 7.0
Afficher la suite
