Sub Import_CSV()
Const CSVPFAD = "C:\Users\Privat\Desktop\Test Daten\temp"
Dim wbTarget As Workbook, wbSource As Workbook, ws As Worksheet, ts As Worksheet
Dim Spalte As Integer
Dim SpalteEnd As Integer
Set fso = CreateObject("Scripting.Filesystemobject")
Set wbTarget = ActiveWorkbook
Application.DisplayAlerts = False
Dim Filename As String
For Each f In fso.GetFolder(CSVPFAD).Files
If LCase(Right(f.Name, 3)) = "txt" Then
Filename = Left(f.Name, Len(f.Name) - 4)
ActiveWorkbook.Queries.Add Name:=Filename, Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Quelle = Csv.Document(File.Contents(f.FullName),[Delimiter="";"", Columns=99, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Typ ändern"" = Table.TransformColumnTypes(Quelle,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type tex" & _
"t}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type text}, {""Column11"", type text}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type text}, {""Column17"", type text}, {""Column18"", typ" & _
"e text}, {""Column19"", type text}, {""Column20"", type text}, {""Column21"", type text}, {""Column22"", type text}, {""Column23"", type text}, {""Column24"", type text}, {""Column25"", type text}, {""Column26"", type text}, {""Column27"", type text}, {""Column28"", type text}, {""Column29"", type text}, {""Column30"", type text}, {""Column31"", type text}, {""Colum" & _
"n32"", type text}, {""Column33"", type text}, {""Column34"", type text}, {""Column35"", type text}, {""Column36"", type text}, {""Column37"", type text}, {""Column38"", type text}, {""Column39"", type text}, {""Column40"", type text}, {""Column41"", type text}, {""Column42"", type text}, {""Column43"", type text}, {""Column44"", type text}, {""Column45"", type text}" & _
", {""Column46"", type text}, {""Column47"", type text}, {""Column48"", type text}, {""Column49"", type text}, {""Column50"", type text}, {""Column51"", type text}, {""Column52"", type text}, {""Column53"", type text}, {""Column54"", type text}, {""Column55"", type text}, {""Column56"", type text}, {""Column57"", type text}, {""Column58"", type text}, {""Column59"", " & _
"type text}, {""Column60"", type text}, {""Column61"", type text}, {""Column62"", type text}, {""Column63"", type text}, {""Column64"", type text}, {""Column65"", type text}, {""Column66"", type text}, {""Column67"", type text}, {""Column68"", type text}, {""Column69"", type text}, {""Column70"", type text}, {""Column71"", type text}, {""Column72"", type text}, {""Co" & _
"lumn73"", type text}, {""Column74"", type text}, {""Column75"", type text}, {""Column76"", type text}, {""Column77"", type text}, {""Column78"", type text}, {""Column79"", type text}, {""Column80"", type text}, {""Column81"", type text}, {""Column82"", type text}, {""Column83"", type text}, {""Column84"", type text}, {""Column85"", type text}, {""Column86"", type te" & _
"xt}, {""Column87"", type text}, {""Column88"", type text}, {""Column89"", type text}, {""Column90"", type text}, {""Column91"", type text}, {""Column92"", type text}, {""Column93"", type text}, {""Column94"", type text}, {""Column95"", type text}, {""Column96"", type text}, {""Column97"", type text}, {""Column98"", type text}, {""Column99"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #" & _
"""Typ ändern"""
On Error Resume Next
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Filename;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Filename]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = Filename
.Refresh BackgroundQuery:=False
End With
ActiveSheet.ListObjects(Filename).ShowHeaders = False
ActiveSheet.ListObjects(Filename).ShowTableStyleRowStripes = False
Rows("1:1").Select
Selection.Delete Shift:=xlUp
With Sheet1
SpalteEnd = .UsedRange.Columns.Count
For Spalte = SpalteEnd To 1 Step -1
If .Cells(1, Spalte).Value = "" Then
.Columns(Spalte).Delete
End If
Next Spalte
End With
End If
Next
Application.DisplayAlerts = True
Set fso = Nothing
End Sub