AW: Import CSV + Formatierung + Formeln, Wichtig!!!
05.05.2015 14:55:58
fcs
Hallo Tommy,
ich hab jetzt mal versucht dein Makro lauffähig zu machen.
Gruß
Franz
Sub Import_CSV()
'Import_CSV Makro
Dim wks As Worksheet, ZelleEinfuegen As Range
Dim Zeile1 As Long, ZeileL As Long, strFormel As String
Set wks = ActiveSheet
Dim strFilename As String
strFilename = "C:\Users\Thomas\Desktop\Excel Forum\His.csv"
With wks
'Einfügezelle in Spalte C setzen
Set ZelleEinfuegen = .Cells(.Rows.Count, 3).End(xlUp).Offset(1, 0)
Zeile1 = ZelleEinfuegen.Row
With .QueryTables.Add(Connection:="TEXT;" & strFilename, Destination:=ZelleEinfuegen)
.Name = "His" & Format(Now, "YYYYMMDDhhmmss")
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(5, 2, 1, 2, 1, 1, 1, 5, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1) '5 = JMT bei einigen Spalten für korrekte Datums-Konversion
.TextFileDecimalSeparator = "." 'für deutsches Excel/Betriebssystem-Einstellung
.TextFileThousandsSeparator = "," 'für deutsches Excel/Betriebssystem-Einstellung
.Refresh BackgroundQuery:=False
End With
wks.QueryTables(1).Delete 'Querytable nach Import wieder löschen
ZeileL = .Cells(.Rows.Count, 3).End(xlUp).Row
If ZeileL >= Zeile1 Then
'Zeilenformat aus Zeile 14 auf alle neuen Zeilen kopieren
.Rows(14).Copy
With .Range(.Rows(Zeile1), .Rows(ZeileL))
.PasteSpecial Paste:=xlPasteFormats
End With
Application.CutCopyMode = Fale
With .Range(.Cells(Zeile1, 11), .Cells(ZeileL, 11)) 'Spalte K
strFormel = "=IF(OR(ISBLANK(RC[-1]),RC[-1]=""""),"""",WEEKNUM(RC[-1],2))"
.FormulaR1C1 = strFormel
End With
With .Range(.Cells(Zeile1, 13), .Cells(ZeileL, 13)) 'Spalte M
strFormel = "=IF(ISBLANK(RC[-3]),"""",RC[-3]-RC[-10])"
.FormulaR1C1 = strFormel
End With
With .Range(.Cells(Zeile1, 18), .Cells(ZeileL, 18)) 'Spalte R
strFormel = "=RC[-1]+RC[-3]+RC[-4]"
.FormulaR1C1 = strFormel
End With
With .Range(.Cells(Zeile1, 19), .Cells(ZeileL, 19)) 'Spalte S
strFormel = "=(RC[-1]+RC[-5])/RC[-3]"
.FormulaR1C1 = strFormel
End With
With .Range(.Cells(Zeile1, 20), .Cells(ZeileL, 20)) 'Spalte T
strFormel = "=ROUND(RC[-1],0)"
.FormulaR1C1 = strFormel
End With
With .Range(.Cells(Zeile1, 21), .Cells(ZeileL, 21)) 'Spalte U
strFormel = "=SUM(R13C[-2]:RC[-2])"
.FormulaR1C1 = strFormel
End With
With .Range(.Cells(Zeile1, 22), .Cells(ZeileL, 22)) 'Spalte V
strFormel = "=R8C3 + SUM(R13C[-4]:RC[-4])"
.FormulaR1C1 = strFormel
End With
End If
End With
End Sub