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" <(E-Mail Removed)> wrote in message
news:376EE507-7B2B-49DB-B41F-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:376EE507-7B2B-49DB-B41F-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:376EE507-7B2B-49DB-B41F-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:9BF5099C-99F0-4FD6-9783-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:376EE507-7B2B-49DB-B41F-(E-Mail Removed)...
>> > 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.
>>
>>
>>