Set USERNAME as variable and use in file Address

[bconner]

I would like to take the USERNAME of the person logged in and pass it to the address of the transferspreadsheet function, below is an example:

Private Sub Command3_Click()
Dim User As String
User = Environ$("USERNAME")
 

On Error GoTo SendError
 
'Tricare Reports
DoCmd.TransferSpreadsheet acExport, , "90JJ AR Sum By Rej", "C:\Documents and Settings\[B][SIZE=3]user[/SIZE][/B]\Desktop\DeepDiveReports\Grp 11 DeepDive Reports.xls", True

however the above code doesn't work, any ideas??


Thanks
Brian

 

[rainman89]

DoCmd.TransferSpreadsheet acExport, , "90JJ AR Sum By Rej", "C:\Documents and Settings\" & user& "\Desktop\DeepDiveReports\Grp 11 DeepDive Reports.xls", True

 

[Sevn]

Is there an error? If so, what is the error?

The word "User" may be a reserved word in Access. Try using envUser or something more unique. I've had a similar issue with reserved words before.

I'm not a professional by any means, but figured I try to help.

 

[Sevn]

I stand corrected. Didn't even see that.

 

[bconner]

I get the following error when I try it:
3044 'C:\Documents and Settings\user\Desktop\DeepDiveReports\Grp 11 DeepDive Reports.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

below is the code I am using:

Private Sub Command3_Click()
Dim User As String
User = Environ$("USERNAME")
 

On Error GoTo SendError
 
'Tricare Reports
DoCmd.TransferSpreadsheet acExport, , "90JJ AR Sum By Rej", "C:\Documents and Settings\" & "user" & "\Desktop\DeepDiveReports\Grp 11 DeepDive Reports.xls", True

 

[bconner]

sorry I was using quotes around the variable....
It worked like a Charm! Thank you both for your help, I appreciate it.