Hallo zusammen,

ich habe mir mit Excel ein Makro aufgezeichnet, dass TXT Dateien importieren soll. Ganz am Anfang hat das auch einmal geklappt. Mittlerweile aber leider nicht mehr.  Dieses Makro habe ich anschließend so angepasst, dass es in einer Schleife alle Dateien des Ordners durchläuft und jede auf ein neues Tabellenblatt importiert.

Auf den Blättern sehe ich aber jeweils nur "ExterneDaten_1: Empfange Daten ...". Ich kann mir einfach nicht erklären, warum der Import nicht klappt.

Die Trennzeichen in den TXTs sind Semikolons, es befinden sich aber auch Kommas im Text (die sollten ja aber keinen Einfluss auf das Ganze haben).

Bitte schaut doch mal kurz auf den Code, ob ihr einen Grund erkennen könnt, warum es fehlschlägt. Ich bin am verzweifeln.
 

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
    
' Eventuelle leere Spalten löschen
    
    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

Vielen Dank für jede Unterstützung

Gruß

Tobi