PDA

View Full Version : loading a txt file into excel win works fine. Loading txt on a mac doesn't work.



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

mancubus
01-31-2018, 05:03 AM
i'm not a mac user but did some googling:

https://warwick.ac.uk/fac/sci/systemsbiology/staff/dyer/software/excelvbafileopen/

for username equivalent on the Mac:
https://stackoverflow.com/questions/17847080/vba-get-username-on-osx-or-mac-alternative-to-environusername

scroll down the page:

Environ("USER") and Environ("LOGNAME") return the same thing on the Mac as the MacScript answer

and try recording a macro on the Mac while importing the txt file.
incorporate the recorded macro with the code provided in the above links.