Makro: Datumsformat einer csv-Datei
27.02.2009 11:43:56
Ralf
Hallo,
die beigefügte Datei ist bei mir eine csv-Datei. Ich mußte sie als xls-Datei hochladen, da csv in diesem Forum nicht hochgeladen werden kann.
Unten aufgeführtes Makro macht bei dem Settlement date aus dem 4.3. den 3.4.
Sub FIDHaekRent()
ChDir "\\Dkdensx09\e074749$\RK\Transaction"
Workbooks.Open Filename:="\\Dkdensx09\e074749$\RK\Transaction\KAGFXRP.CSV"
ChDir "\\DKDENSX09\E074749$\RK"
ActiveWorkbook.SaveAs Filename:="\\DKDENSX09\E074749$\RK\formatieren.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
'leere Spalten ausblenden
Application.ScreenUpdating = False
For i = 1 To 36
Columns(i).Hidden = Application.CountA(Columns(i)) = 0
Next
Rows("1:9").Select
With Selection.Font
.Name = "Times New Roman"
.FontStyle = "Standard"
.Size = 1
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Rows("10:10").Select
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Rows("11:200").Select
ActiveWindow.LargeScroll Down:=-4
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Selection.RowHeight = 25
Cells.Select
Cells.EntireColumn.AutoFit
Rows("1:9").Select
With Selection.Font
.Name = "Times New Roman"
.FontStyle = "Standard"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Columns("A:AI").Select
ActiveSheet.PageSetup.PrintArea = "$A:$AI"
ActiveWindow.SmallScroll ToRight:=0
Range("E13").Select
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A:$AI"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.196850393700787)
.RightMargin = Application.InchesToPoints(0.196850393700787)
.TopMargin = Application.InchesToPoints(0.984251969)
.BottomMargin = Application.InchesToPoints(0.984251969)
.HeaderMargin = Application.InchesToPoints(0.511811023622047)
.FooterMargin = Application.InchesToPoints(0.511811023622047)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
i = 1
Do Until Cells(10, i).Value = ""
x = 0
x = x + InStr(1, Cells(10, i).Value, "Rel. transref.")
x = x + InStr(1, Cells(10, i).Value, "Rel.transref")
x = x + InStr(1, Cells(10, i).Value, "CANC transref.")
x = x + InStr(1, Cells(10, i).Value, "Comm.")
x = x + InStr(1, Cells(10, i).Value, "Fees")
x = x + InStr(1, Cells(10, i).Value, "Tax")
x = x + InStr(1, Cells(10, i).Value, "Others")
x = x + InStr(1, Cells(10, i).Value, "Interest")
x = x + InStr(1, Cells(10, i).Value, "Interest days")
x = x + InStr(1, Cells(10, i).Value, "Trade Time")
x = x + InStr(1, Cells(10, i).Value, "Trade time")
x = x + InStr(1, Cells(10, i).Value, "Trade date&time")
x = x + InStr(1, Cells(10, i).Value, "Place of trade")
x = x + InStr(1, Cells(10, i).Value, "Broker ID type")
x = x + InStr(1, Cells(10, i).Value, "Broker ID")
x = x + InStr(1, Cells(10, i).Value, "Clearing Broker")
x = x + InStr(1, Cells(10, i).Value, "Counterparty ID type")
x = x + InStr(1, Cells(10, i).Value, "Counterparty ID")
x = x + InStr(1, Cells(10, i).Value, "Tic Size")
x = x + InStr(1, Cells(10, i).Value, "Tic size")
x = x + InStr(1, Cells(10, i).Value, "Tic Value")
x = x + InStr(1, Cells(10, i).Value, "Tic value")
x = x + InStr(1, Cells(10, i).Value, "FX -Rate")
x = x + InStr(1, Cells(10, i).Value, "Portfolio ID Custodian")
x = x + InStr(1, Cells(10, i).Value, "Margin")
x = x + InStr(1, Cells(10, i).Value, "Net price")
x = x + InStr(1, Cells(10, i).Value, "Limit")
x = x + InStr(1, Cells(10, i).Value, "Valid Date")
x = x + InStr(1, Cells(10, i).Value, "Total Units")
x = x + InStr(1, Cells(10, i).Value, "Unit Price")
x = x + InStr(1, Cells(10, i).Value, "Execute Broker ID(BIC)")
x = x + InStr(1, Cells(10, i).Value, "CODE")
x = x + InStr(1, Cells(10, i).Value, "Principal")
x = x + InStr(1, Cells(10, i).Value, "Transaction")
x = x + InStr(1, Cells(10, i).Value, "Unit Price")
x = x + InStr(1, Cells(10, i).Value, "NO.")
x = x + InStr(1, Cells(10, i).Value, "Broker geglättet")
x = x + InStr(1, Cells(10, i).Value, "Handelstag")
x = x + InStr(1, Cells(10, i).Value, "Valuta")
x = x + InStr(1, Cells(10, i).Value, "SWIFT BIC")
x = x + InStr(1, Cells(10, i).Value, "Sec.A/C")
x = x + InStr(1, Cells(10, i).Value, "Clearer Acc (where required)")
x = x + InStr(1, Cells(10, i).Value, "Country of settlement")
x = x + InStr(1, Cells(10, i).Value, "Covered/Uncovered")
x = x + InStr(1, Cells(10, i).Value, "FX-Rate")
x = x + InStr(1, Cells(10, i).Value, "Clearer Acc")
x = x + InStr(1, Cells(10, i).Value, "Country")
x = x + InStr(1, Cells(10, i).Value, "Status")
x = x + InStr(1, Cells(10, i).Value, "SettlementType")
x = x + InStr(1, Cells(10, i).Value, "OrigfaceAmt")
x = x + InStr(1, Cells(10, i).Value, "Sedol")
x = x + InStr(1, Cells(10, i).Value, "MIC")
x = x + InStr(1, Cells(10, i).Value, "PriceType")
x = x + InStr(1, Cells(10, i).Value, "Factor")
x = x + InStr(1, Cells(10, i).Value, "GrossAmt")
x = x + InStr(1, Cells(10, i).Value, "Commission")
If x > 0 Then Columns(i).Hidden = True
i = i + 1
Loop
Range("a10").Select
Do Until ActiveCell.Value = ""
If InStr(1, ActiveCell.Value, "Interest rate") Then
Selection.EntireColumn.Hidden = False
End If
ActiveCell.Offset(0, 1).Select
Loop
'leere Spalten ausblenden
Application.ScreenUpdating = True
ActiveWorkbook.Save
End Sub