csv einlesen mit "absoluten Pfad" macht Probleme
07.08.2025 12:28:14
Klaus Putterlik
hoffentlich kann mir wieder jemand helfen.
ich lese eine bestimmte csv-Datei ein um die Werte dann weiter zu bearbeiten. Wenn ich den absoluten Pfad direkt eingebe, funktioniert auch alles. Ich brauche aber eine Variable (Wert ist identisch mit absolutem Pfad), dann meckert xls, dass der Pfad nicht absolut ist. Wie kann das umgangen werden?
um diese Zeile geht es im Makro:
ActiveWorkbook.Queries.Add Name:="top_cover_results", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents("" & csv & ""),[Delimiter="";"", Columns=27, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumn" & _
"Types(#""Promoted Headers"",{{""Ergebnis-ID"", Int64.Type}, {""Kurve verfügbar"", type text}, {""Sitzung"", type datetime}, {""Testparameter ID"", Int64.Type}, {""Name"", type text}, {""Testtyp"", type text}, {""Werkzeugtyp"", type text}, {""Verbundene Geräte"", type text}, {""Datum/Zeit"", type datetime}, {""Status"", type text}, {""Einheit"", type text}, {""Drehmo" & _
"ment"", Int64.Type}, {""Nominal Drehmoment"", Int64.Type}, {""Winkelschwellwert"", Int64.Type}, {""Min Drehmoment"", Int64.Type}, {""Max Drehmoment"", Int64.Type}, {""Winkel"", Int64.Type}, {""Spitzenwert Drehmoment"", Int64.Type}, {""Winkel bei Spitzenwert Drehmoment"", Int64.Type}, {""Drehmoment max. Winkel"", Int64.Type}, {""Max. Winkel"", Int64.Type}, {""Nominal" & _
" Winkel"", Int64.Type}, {""Min. Winkel"", Int64.Type}, {""Max. Winkel_1"", Int64.Type}, {""VIN"", type text}, {""Seq. Ergebnis"", Int64.Type}, {""Hinweis Kurve"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
wenn ich bei "" & csv & "" den Pfad ("G:\...") direkt angebe gibt es kein Problem, mit der Variablen "csv" dann aber doch bei .Refresh BackgroundQuery:=False (kein absoluter Pfad???)
Public Sub top_cover()
Dim i As Long
Dim DUT1 As String
Dim search_dut As Range
Dim search_partname As Range
Dim screw_ident As String
Dim path As String
Dim csv As String
Dim search_1 As String
Dim Q
Dim objNetzwerk As Object
For Each Q In ActiveWorkbook.Queries
If Q.Name = "top_results" Then Q.Delete
Next
DUT1 = "DUT " & DUT
search_1 = Sheets("identification_table").Range("B2").Value
Sheets("identification_table").Range("B3").Value = "top_cover"
Worksheets("identification_table").Calculate
path = Sheets("identification_table").Range("B5").Value
path = Replace(path, "\\xxx\xxx\xxx\", "")
csv = "G:\yyy\" & path
Call CreateDrive
ActiveWorkbook.Queries.Add Name:="top_results", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents("" & csv & ""),[Delimiter="";"", Columns=27, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumn" & _
"Types(#""Promoted Headers"",{{""Ergebnis-ID"", Int64.Type}, {""Kurve verfügbar"", type text}, {""Sitzung"", type datetime}, {""Testparameter ID"", Int64.Type}, {""Name"", type text}, {""Testtyp"", type text}, {""Werkzeugtyp"", type text}, {""Verbundene Geräte"", type text}, {""Datum/Zeit"", type datetime}, {""Status"", type text}, {""Einheit"", type text}, {""Drehmo" & _
"ment"", Int64.Type}, {""Nominal Drehmoment"", Int64.Type}, {""Winkelschwellwert"", Int64.Type}, {""Min Drehmoment"", Int64.Type}, {""Max Drehmoment"", Int64.Type}, {""Winkel"", Int64.Type}, {""Spitzenwert Drehmoment"", Int64.Type}, {""Winkel bei Spitzenwert Drehmoment"", Int64.Type}, {""Drehmoment max. Winkel"", Int64.Type}, {""Max. Winkel"", Int64.Type}, {""Nominal" & _
" Winkel"", Int64.Type}, {""Min. Winkel"", Int64.Type}, {""Max. Winkel_1"", Int64.Type}, {""VIN"", type text}, {""Seq. Ergebnis"", Int64.Type}, {""Hinweis Kurve"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=top_results;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [top_results]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "top_results"
.Refresh BackgroundQuery:=False
End With
ActiveWorkbook.Queries("top_results").Delete
ActiveSheet.Name = "top_results"
For i = 1 To Cells(Rows.Count, 12).End(xlUp).Row
If IsNumeric(Range("L" & i)) Then
Range("L" & i) = Range("L" & i) / 100
End If
Next
'....
Sheets("identification_table").Select
Range("B2").Select
Sheets("top_results").Delete
End SubVielen Dank für eure Hilfe
Gruß Klaus
Anzeige