Variabler Pfad im Query

Forumthread: Variabler Pfad im Query

19.01.2021 12:31:33

V.Mendez

Hallo zusammen,
ich benötige Hilfe bei der VBA basierten Imports einer CSV Datei.
Leider streikt der Import immer mit dem Hinweis, dass ein absoluter Pfad angegeben werden muss.
Ich bin am Ende meines Lateins und würde daher eure Hilfe erbitten:
Folgenden Code habe ich erstellt:


Sub Import()
Dim Importdatei As String
Dim path As String
Dim file As Office.FileDialog
Dim objQr
For Each objQr In ThisWorkbook.Queries
objQr.Delete
Next
Set file = Application.FileDialog(msoFileDialogFilePicker)
With file
.Title = "Dateiauswahl"
.Filters.Clear
.Filters.Add "csv", "*.csv"
.Filters.Add "All Files", "*.*"
If .Show = True Then
path = .SelectedItems(1)
Else
Exit Sub
End If
End With
Sheets("Report").Select
Cells.Select
Selection.Delete
ActiveWorkbook.Queries.Add Name:="Report", Formula:= _
"let" & Chr(13) & "" & Chr(10) & "    Quelle = Csv.Document(File.Contents(""path""),[ _
Delimiter="","", Columns=32, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" &  _
Chr(10) & "    #""Höher gestufte Header"" = Table.PromoteHeaders(Quelle, [PromoteAllScalars= _
true])," & Chr(13) & "" & Chr(10) & "    #""Geänderter Typ"" = Table.TransformColumnTypes(#""Hö" & _
"her gestufte Header"",{{"""", type text}, {""Serial Number"", Int64.Type}, {""Owner  _
Company Name"", type text}, {""Agreement Number"", Int64.Type}, {""Status"", type text}, {"" _
Agreement Type"", type text}, {""Package"", type text}, {""Service Level"", type text}, {"" _
Services"", type text}, {""Start Date"", type date}, {""End Date"", type date}, {""Installed At Site " & _
"Name"", type text}, {""Group"", type text}, {""Product Number"", type text}, {""System  _
_
_
Name"", type text}, {""Os"", type text}, {""# Of Shelves"", Int64.Type}, {""# Of Disks"", Int64. _
_
Type}, {""Nvram"", Int64.Type}, {""Nvmem"", Int64.Type}, {""Motherboard Memory"", Int64.Type}, { _
""Autosupport Status"", type text}, {""Installed At Address"", type text}, {""City"", typ" & _
"e text}, {""State"", Int64.Type}, {""Postal Code"", Int64.Type}, {""Country"", type  _
text}, {""Contact First Name"", type text}, {""Contact Last Name"", type text}, {""Contact  _
Email"", type text}, {""Agreement Company"", type text}, {""Reseller Company                     _
""," & _
" type text}})," & Chr(13) & "" & Chr(10) & "    #""Entfernte Spalten"" = Table. _
RemoveColumns(#""Geänderter Typ"",{"""", ""Agreement Type"", ""Package"", ""Services"", "" _
Installed At Site Name"", ""Group"", ""System Name"", ""Os"", ""Nvram"", ""Nvmem"", "" _
Motherboard Memory"", ""Autosupport Status"", ""State"", ""Contact First Name"", ""Contact Last Name"", ""Contact Email"", ""Reseller Company" & _
"                                                                                        _
_
_
"", ""Service Level"", ""Status""})," & Chr(13) & "" & Chr(10) & "    _
_
#""Neu angeordnete Spalten"" = Table.ReorderColumns(#""Entfernte Spalten"",{""Owner Company  _
Name"", ""Agreement Number"", ""Serial Number"", ""Start Date"", ""End Date"", ""Product Number"", ""# Of Shelves"", ""#" & _
" Of Disks"", ""Installed At Address"", ""City"", ""Postal Code"", ""Country"", "" _
Agreement Company""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Neu   _
_
angeordnete Spalten"""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Report; _
Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Report]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Report"
.Refresh BackgroundQuery:=False
End With
End Sub

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Variabler Pfad im Query

19.01.2021 14:14:32

V.Mendez
AW: Variabler Pfad im Query

19.01.2021 14:49:00

ralf_b

AW: Variabler Pfad im Query

19.01.2021 14:14:32

V.Mendez

Wie bekomme ich es hin, dass der User den Dateipfad selbst auswählen kann?

AW: Variabler Pfad im Query

19.01.2021 14:49:00

ralf_b

entweder du verkettest den Text richtig mit der Path variable

...File.Contents(""" & path & """),[Delimiter="",""....

oder du erzeugt eine parameterabfrage, die z.b. auf eine bestimmte Zelle zugreift.
gruß
rb

Infobox / Tutorial

Variabler Pfad im Query für den CSV-Import

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um einen variablen Pfad für den Import einer CSV-Datei in Excel mit VBA zu verwenden, kannst du die folgende Schritt-für-Schritt-Anleitung befolgen:

Öffne den VBA-Editor: Drücke ALT + F11, um den VBA-Editor zu öffnen.
Füge ein neues Modul hinzu: Klicke mit der rechten Maustaste auf „VBAProject (Deine Arbeitsmappe)“ und wähle „Einfügen“ > „Modul“.

Füge den folgenden Code ein:

Sub Import()
    Dim Importdatei As String
    Dim path As String
    Dim file As Office.FileDialog
    Dim objQr As Object
    For Each objQr In ThisWorkbook.Queries
        objQr.Delete
    Next
    Set file = Application.FileDialog(msoFileDialogFilePicker)
    With file
        .Title = "Dateiauswahl"
        .Filters.Clear
        .Filters.Add "csv", "*.csv"
        .Filters.Add "All Files", "*.*"
        If .Show = True Then
            path = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With
    Sheets("Report").Select
    Cells.Select
    Selection.Delete
    ActiveWorkbook.Queries.Add Name:="Report", Formula:= _
    "let" & Chr(13) & "" & Chr(10) & "    Quelle = Csv.Document(File.Contents(""" & path & """), [Delimiter="","", Columns=32, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Höher gestufte Header"" = Table.PromoteHeaders(Quelle, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Geänderter Typ"" = Table.TransformColumnTypes(#""Höher gestufte Header"", {{""Column1"", type text}, ... })" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Geänderter Typ"""

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Report; Extended Properties=""""" _
    , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Report]")
        .Refresh BackgroundQuery:=False
    End With
End Sub

Schließe den VBA-Editor und führe das Makro aus.

Häufige Fehler und Lösungen

Fehler: „Ein absoluter Pfad muss angegeben werden“:
- Stelle sicher, dass du den path korrekt mit den doppelten Anführungszeichen in die File.Contents Funktion einfügst:
```
File.Contents(""" & path & """)
```
Fehler: Abfrage kann nicht erstellt werden:
- Überprüfe, ob deine Datenquelle korrekt konfiguriert ist und ob die „Report“-Tabelle existiert.

Alternative Methoden

Wenn du keinen VBA-Code verwenden möchtest, kannst du auch Power Query verwenden, um Daten aus einer CSV-Datei zu importieren. Gehe zu „Daten“ > „Daten abrufen“ > „Aus Datei“ > „Aus CSV“, um den Datei-Dialog zu öffnen und den Pfad manuell auszuwählen.

Praktische Beispiele

Hier ist ein einfaches Beispiel, wie du mit activeworkbook.queries.add eine CSV-Datei importieren kannst:

ActiveWorkbook.Queries.Add Name:="ImportierteCSV", Formula:= _
"let Quelle = Csv.Document(File.Contents(""" & path & """), [Delimiter="","", Columns=3, Encoding=65001]) in Quelle"

Hierbei wird die CSV-Datei als Query in der Arbeitsmappe hinzugefügt, und du kannst die Daten anschließend in Excel nutzen.

Tipps für Profis

Verwende Parameterabfragen: Anstatt den Dateipfad direkt in den Code einzufügen, kannst du eine Zelle verwenden, um den Pfad dynamisch zu speichern. Das macht die Abfrage flexibler.
Optimierung der Abfragen: Wenn du die Struktur deiner Daten häufig änderst, überlege dir, die activeworkbook.queries zu optimieren, um die Leistung zu verbessern.

FAQ: Häufige Fragen

1. Wie kann ich den VBA-Code anpassen, um mehrere CSV-Dateien zu importieren? Du kannst eine Schleife verwenden, um durch alle ausgewählten Dateien zu iterieren und jede Datei einzeln zu importieren.

2. Was ist der Unterschied zwischen activeworkbook.queries.add und vba queries.add? Es gibt keinen funktionalen Unterschied; beide Methoden fügen eine neue Abfrage hinzu. Der Unterschied liegt lediglich in der Syntax.

3. Kann ich den Importprozess automatisieren? Ja, du kannst das Makro so anpassen, dass es automatisch beim Öffnen der Arbeitsmappe ausgeführt wird, indem du es in die Workbook_Open-Prozedur einfügst.

Beliebteste Forumthreads (12 Monate)