AW: Umwandlung textfiles in Excel
09.06.2008 13:51:00
Klaus
Hallo Jessica,
der Makrorekorder ist dein Freund!
Ich hab deine Anforderung mal aufgezeichnet:
Sub Makro1()
' Makro1 Makro
' Makro am 09.06.2008 von K.MvdT aufgezeichnet
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\k.mvdt\Desktop\Txtest.txt", Origin:=437 _
, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
Columns("A:E").Select
Columns("A:E").EntireColumn.AutoFit
Range("A1:E1").Select
Selection.AutoFilter
Range("B2").Select
ActiveWindow.FreezePanes = True
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.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)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
Range("A1:N21").Select
Selection.Sort Key1:=Range("F2"), Order1:=xlAscending, Key2:=Range("D2") _
, Order2:=xlAscending, Key3:=Range("N2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
End Sub
Der Rekorder Code ist eine ganz gute Basis, um etwas zu optimieren:
Sub Makro2()
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\k.mvdt\Desktop\Txtest.txt", Origin:=437 _
, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
Columns("A:N").EntireColumn.AutoFit
Range("A1:N1").AutoFilter
'*** ohne select?
Range("B2").Select
ActiveWindow.FreezePanes = True
ActiveSheet.PageSetup.PrintTitleRows = "$1:$1" 'Wiederholungszeilen
Range("A1:N21").Sort Key1:=Range("F2"), Order1:=xlAscending, Key2:=Range("D2") _
, Order2:=xlAscending, Key3:=Range("N2"), Order3:=xlAscending
End Sub
Du musst nur noch die "Open" Prozedur variabler gestalten - das kann ich nicht, da ich deine Pfade und Dateinamen nicht kenne.
Grüße,
Klaus M.vdT.