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.

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


"Greg" wrote in message
...
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.

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"


--


Gary


"Greg" wrote in message
...
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.

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" wrote:

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"


--


Gary


"Greg" wrote in message
...
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.

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

--


Gary


"Greg" wrote in message
...
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" wrote:

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"


--


Gary


"Greg" wrote in message
...
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.