Changing path for import file

[Greg]

My macro imports a couple of csv files that are always located in the
c:\users\username\documents\data folder, with username being the user whose
computer the spreadsheet is being used on. Unfortunately for me, whenever I
make a change to the macro, I have to create a separate copy for each
individual so that the file path to the csv file that's being imported is
correct. The portion of the code that refers to the file reads:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\username\Documents\Data\Import.csv" _
, Destination:=Range("A3"))
.Name = "Import"

Is there a way to code this so I don't have to change the path for each user?

Thanks for any help.

 

[Gary Keramidas]

see if this helps:

dim fPath as string
fPath = "c:\users\" & Environ("username") & "Documents\Data\"

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & fPath & "Import.csv" _
, Destination:=Range("A3"))
.Name = "Import"

 

[Gary Keramidas]

sorry, missed a backslash

fpath = "c:\users\" & Environ("username") & "\Documents\Data\"

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & fpath & "Import.csv" _
, Destination:=Range("A3"))
.Name = "Import"

 

[Greg]

That works great. Thanks.

One other complication. There may also be some users running Windows XP. In
this case, the file would be located at

C:\Documents and Settings\username\Documents\Data\Import.csv

Is there a way to code it so it can be used on both Windows XP & Vista
machines?

Thanks!

 

[Gary Keramidas]

maybe this will work, or maybe someone has a vbscript.

Sub test()
If UCase(Split(Environ("homepath"), "\")(1)) = "USERS" Then
MsgBox "vista"
ElseIf UCase(Split(Environ("homepath"), "\")(1)) = "DOCUMENTS AND
SETTINGS" Then
MsgBox "XP"
Else
MsgBox "some other os"
End If
End Sub