josiba
Goto Top

CSV-Datei mit einem VBA Makro in Excel einlesen und leicht anpassen

Hallo zusammen,

ich benutze folgenden Code von Colinardo:
Sub ImportiereCSVDateien()
    Dim ws As Worksheet, header As Boolean, startRange As Range, curRange As Range, counter As Integer
    Const CSVPFAD = "E:\Datenbank"  
    Set fso = CreateObject("Scripting.Filesystemobject")  
    Set ws = Worksheets(1)
    ws.Range("A:ZZ").Clear  
    Set startRange = ws.Range("A1")  
    Set curRange = startRange
    Application.DisplayAlerts = False
    counter = 1
    For Each f In fso.GetFolder(CSVPFAD).Files
        If LCase(Right(f.Name, 3)) = "csv" Then  
            Dim importHeader As Boolean
            If counter = 1 Then
                header = True
            Else
                header = False
            End If
            importCSV f.Path, ";", curRange, header  
            Set curRange = curRange.End(xlDown).Offset(1, 0)
            counter = counter + 1
        End If
        
    Next
    ws.ListObjects.Add xlSrcRange, ws.Range(startRange, curRange.Offset(-1, curRange.Offset(-1, 0).End(xlToRight).Column - 1))
    Application.DisplayAlerts = True
    Set fso = Nothing
End Sub

Function importCSV(strPath, delim, ByVal targetRange As Range, ByVal importHeader As Boolean)
    Dim intStart As Integer
    Set fso = CreateObject("Scripting.FileSystemObject")  
    Set regex = CreateObject("vbscript.regexp")  
    patNumber = "^([\d\.,\+\-]+)$"  
    arrLines = Split(fso.OpenTextFile(strPath, 1).ReadAll(), vbNewLine, -1, vbTextCompare)
    Set rngCurrent = targetRange
    If importHeader Then
        intStart = 0
    Else
        intStart = 1
    End If
    For i = intStart To UBound(arrLines)
        If arrLines(i) <> "" Then  
            cols = Split(arrLines(i), delim, -1, vbTextCompare)
            For c = 0 To UBound(cols)
                rngCurrent.Offset(0, c).ClearFormats
                wert = Trim(Replace(cols(c), """", "", 1, -1, vbTextCompare))  
                ' check for Numberformat  
                regex.Pattern = patNumber
                Set matches = regex.Execute(wert)
                If matches.Count > 0 Then
                    wert = Replace(matches(0).Submatches(0), ",", ".", 1, -1, vbTextCompare)  
                End If
                ' set value in cell  
                rngCurrent.Offset(0, c).Value = wert
            Next
            Set rngCurrent = rngCurrent.Offset(1, 0)
        End If
    Next
    Set fso = Nothing
    Set regex = Nothing
End Function


Die Importierung an sich Funktioniert.

Ich habe hier nur 4 Anliegen.

1) Die Datenbank wird im mom auf "Worksheets(1)" eingelesen, ist es möglich das es immer in den "Tabellenblatt : Datenbank)" eingelesen wird, egal ob es das 2,3,4 Tabellenblatt ist.

2) Datenbank Tabellenname wird als "Tabelle3" eingetragen, ist es möglich diese zu Ändern in "Master_DB".

3) Noch eine Frage, beim Importieren, werden nur die Spalten A:L als Datenbank eingelesen, die restlichen M:AG werden nicht mehr als Datenbank sonder als normale Tabelle.

4) In den Spalten "D:E" sind Datum und Uhrzeit als "UNIX Timestamp" vorhanden, diese sollen beim Importieren in den Format (DD.MM.JJJJ HH:MM) umgewandelt werden.

Kannst du mir hier helfen?
Was muss im Code geändert werden?

Danke

Gruß

JoSiBa

Content-Key: 315371

Url: https://administrator.de/contentid/315371

Ausgedruckt am: 19.03.2024 um 02:03 Uhr

Mitglied: colinardo
Lösung colinardo 16.09.2016 um 10:41:57 Uhr
Goto Top
Hallo JoSiBa,
hier die Änderung für die erste Sub, die untere Funktion bleibt gleich.
Sub ImportiereCSVDateien()
    Dim ws As Worksheet, header As Boolean, startRange As Range, curRange As Range, counter As Integer, lo As ListObject, x As Long
    Const CSVPFAD = "E:\Datenbank"  
    Set fso = CreateObject("Scripting.Filesystemobject")  
    Set ws = Worksheets("Datenbank")  
    ws.UsedRange.Clear
    Set startRange = ws.Range("A1")  
    Set curRange = startRange
    Application.DisplayAlerts = False
    counter = 1
    For Each f In fso.GetFolder(CSVPFAD).Files
        If LCase(Right(f.Name, 3)) = "csv" Then  
            Dim importHeader As Boolean
            If counter = 1 Then
                header = True
            Else
                header = False
            End If
            importCSV f.Path, ";", curRange, header  
            Set curRange = curRange.End(xlDown).Offset(1, 0)
            counter = counter + 1
        End If
        
    Next
    'Bereich in ListObject umwandeln  
    Set lo = ws.ListObjects.Add(xlSrcRange, ws.Range(startRange, ws.Cells(curRange.Offset(-1, 0).Row, Columns.Count).End(xlToLeft)))
    'Tabelle benennen  
    lo.Name = "Master_DB"  
    
    ' Unix timestamp der Zellen D:E umwandeln  
    For Each cell In lo.DataBodyRange.Columns(4).Resize(, 2).Cells
        On Error Resume Next
        x = CLng(cell.Value)
        If Err.Number = 0 Then
            cell.Value = DateAdd("s", x, CDate("01/01/1970"))  
        End If
    Next
    'Datumsformat der Spalten D:E setzen  
    lo.DataBodyRange.Columns(4).Resize(, 2).NumberFormat = "dd/mm/yyyy hh:mm;@"  
    Application.DisplayAlerts = True
    Set fso = Nothing
End Sub
Grüße Uwe
Mitglied: JoSiBa
JoSiBa 18.09.2016 um 20:21:45 Uhr
Goto Top
Funktioniert Super

Danke
Mitglied: JoSiBa
JoSiBa 19.09.2016 um 11:36:44 Uhr
Goto Top
Hallo colinardo,

ich habe jetzt ein neues Problem.

durch den Code ws.UsedRange.Clear werden meine Berechnungen die ich im anderen Datenblatt habe unbrauchbar gemacht.

Bsp:
vor der Aktualisierung: =ZÄHLENWENNS(Master_DB[ee2];$D4;Master_DB[ee];E$3)
nach der Aktualisierung: =ZÄHLENWENNS(#BEZUG!;$D4;#BEZUG!;E$3)

Wenn ich den Code deaktiviere per ' ws.UsedRange.Clear bekomme ich zum Schluss eine Fehlermeldung 400

Kannst du mir hier noch mal Helfen?

Danke
JoSiBa
Mitglied: Biber
Lösung Biber 19.09.2016 um 20:37:47 Uhr
Goto Top
Moin JoSiBa,

das ist selbst verschuldetes Leiden durch ein Refernezieren auf eine Tabelle, von der du doch vorher weisst, dass sie weggebratzt wird.

Was da passiert ist folgendes:

  • du hast dein Workbook einmal fertig, es existiert eine Tabelle (ein ListObject) namens "Master_DB"
  • unter anderem kannst du diesen Namen sehen, wenn du oben links in der Zell-Anzeige mal die definierten Namen anzeigst
  • ebenso sichtbar ist der Name "Master_DB" wenn du den "Namensmanager" aufrufst
  • auf "Master_DB" verweisen die ZÄHLENWENNS (und funktionieren)
  • jetzt - mach es einmal manuell, damit du verstehst, was Excel jetzt macht - löscht du den Inhalt des Blattes, auf dem "Master_DB" ist.
  • daraufhin verschwindet in der Oben-Links-Anzeige der Name "Master_DB", gibts ja nicht mehr. Bei den ZÄHLENWENNS steht "#BEZUG"
  • aber: wirf einen Blick in den Namensmanager: da existiert der Name "Master_DB" noch (als Name, und mit Wert "#BEZUG")
  • in dieser Situation rennt das Makro los und legt die Tabelle "Master_DB" neu an. Geht, eine Tabelle dieses Namens gibt es ja nicht
  • und es wird der ListObject.Name auch auf "Master_DB" gesetzt, nur: das ist gar nicht der Oben-Links-Name (der im Excel-Sheet benutzt wird)
  • das ist nämlich der ListObject.Displayname.
  • hier merkt Excel, dass es als .Displayname den "Master_DB" schon gibt und legt deshalb den Displaynamen "Master_DB_01" (oder beim zweiten Versuch "_02", beim dritten Lauf "..03") an
  • im Namensmanager siehst du auch nach wie vor die Namen "Master_DB" (mit "#BEZUG") und den neuen gültigen Namen "Master_DB_01", den aber kein Schwein und kein ZÄHLENWENNS kennen kann

Also: als mögliche Strategien fallen mit nur ein: sorg dafür, dass KEIN Verweis auf den Namen "Master_DB" existiert
  • durch Referenzieren auf Tabellenblatt "Datenbank" und absolute Zellangaben. Geht auch Anno 2016 noch.
  • oder durch Kapseln mit INDIREKT ( =ZÄHLENWENNS(Indirekt("Master_DB[ee2]");$D4;INDIREKT("Master_DB[ee]");E$3)

(oder eher nicht, eher als Plan C)
  • eventuell könnte auch der Makro mit ActiveWorkbook.Names("Master_DB").Delete diesen Namen löschen, aber ich denke, das funktioniert nicht, wenn noch die ZÄHLENWENNS-Verweise darauf zeigen

Grüße
Biber
Mitglied: colinardo
Lösung colinardo 20.09.2016 aktualisiert um 00:26:47 Uhr
Goto Top
Jepp, Danke Biber, damit hatte ich ihn im Hintergrund auch schon via PM versorgt, da er es dort angefragt hatte face-smile

Grüße Uwe