odds
01-31-2018, 01:55 AM
Hy everyone.
Im new here and not an expert of vba excel.
I have a working excel-file which grabs a .txt and put it into a table.
This works fine on a pc but not on a mac. Must have something to do with the structure of the folders, i guess.
Can somebody tell me how to rebuild code that it works for both? Mac and PC?
Here is the code:
Sub Import()
' Import Makro EVENTS
' Variabeln definieren
' wie heisst das txt - file ?
Filepfad = VBA.Environ("userprofile") & "\Downloads\"
ImportFile = Filepfad & "event_statistik.txt"
'Filepfad und File Existenz checken checken
If Dir(ImportFile) = "" Then
Meldung = "Das Importfile" & vbCrLf & " '" & ImportFile & "'" & vbCrLf & " konnte nicht gefunden werden"
MsgBox Meldung, 16, "ACHTUNG fehlendes Importfile Events"
Exit Sub
End If
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & ImportFile, Destination _
:=Range("$A$1"))
.Name = "Daten Events"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 65001
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
' Range("A1").Select
End Sub
"""""" On the Mac, the path where the downloaded file should be: Macintosh HD:Users:<user name>:Downloads
It should be possible to use it with every "user name"
Can somebody help me?
I'm searching since days on the internet for a working solution.
Please help.
greetings and thx
odds
Im new here and not an expert of vba excel.
I have a working excel-file which grabs a .txt and put it into a table.
This works fine on a pc but not on a mac. Must have something to do with the structure of the folders, i guess.
Can somebody tell me how to rebuild code that it works for both? Mac and PC?
Here is the code:
Sub Import()
' Import Makro EVENTS
' Variabeln definieren
' wie heisst das txt - file ?
Filepfad = VBA.Environ("userprofile") & "\Downloads\"
ImportFile = Filepfad & "event_statistik.txt"
'Filepfad und File Existenz checken checken
If Dir(ImportFile) = "" Then
Meldung = "Das Importfile" & vbCrLf & " '" & ImportFile & "'" & vbCrLf & " konnte nicht gefunden werden"
MsgBox Meldung, 16, "ACHTUNG fehlendes Importfile Events"
Exit Sub
End If
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & ImportFile, Destination _
:=Range("$A$1"))
.Name = "Daten Events"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 65001
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
' Range("A1").Select
End Sub
"""""" On the Mac, the path where the downloaded file should be: Macintosh HD:Users:<user name>:Downloads
It should be possible to use it with every "user name"
Can somebody help me?
I'm searching since days on the internet for a working solution.
Please help.
greetings and thx
odds