Import data . txt from desktop no matter which user

Maxxian

New member
Joined
Feb 15, 2014
Messages
2
Reaction score
0
Points
0
Hi guys, I am looking for a macro that imports a text file located on a desktop for any user. I mean i don't want to change the file link in VBA for any user. Macro should retrieve the Desktop address automatically. File stored in desktop has always the same name. For xp / vista and win 7. Thanks guys.

Inviato dal mio GT-N7100 utilizzando Tapatalk
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,199
Reaction score
16
Points
38
Excel Version(s)
365
try something along these lines, tweaking for destination etc. of course:
Code:
Sub blah()
xxx = CreateObject("WScript.Shell").specialfolders("Desktop") & Application.PathSeparator & "YourConstantFilename.txt"
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & xxx, Destination:=Range("C10"))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub
 

Maxxian

New member
Joined
Feb 15, 2014
Messages
2
Reaction score
0
Points
0
Thanks a lot. It has worked perfectly. :)

Inviato dal mio GT-N7100 utilizzando Tapatalk
 
Top