Import a txt file from a folder in Users

[assurdo]

Hello.

I have to import automatically, when I open an excel file, a file txt named myfile.txt. This file is inside the path C:\Users\myname\AppData\Roaming\myfolder.

It is easy if the myname was the same in all computers, instead I have to be able to open this file on a computer with different myname.

I have seen that I can use the ENVIRON("APPDATA") function to get the following address "C:\Users\myname\AppData\Roaming" but I cannot make it work in excel.

Thank you.

This is the code:

Private Sub Workbook_Open()

Dim asd As String
asd = Environ("APPDATA" + "\myfolder\myfile.txt"

Sheets("Import").Select
With Selection.QueryTable
.Connection = _
"TEXT;asd"
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

End Sub

 

[assurdo]

I changed a bit the code, but the problem remains.

Private Sub Workbook_Open()

Dim asd As String
asd = Environ("APPDATA") + "\myfolder\myfile.txt"

Sheets("Import").Select
With Selection.QueryTable
.Connection = _
"TEXT;" & asd
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

End Sub