VBA to export to any desktop

[Neilster]

Hi Guys,

I have this code below which works fine and sends an Excel spread sheet to the users desktop.

DoCmd.SetWarnings False

DoCmd.OpenQuery "QryBristolj"

DoCmd.SetWarnings True
Dim strFullyQualifiedFilename As String

strFullyQualifiedFilename = "C:\Users\User\Desktop\End Of Month.xlsx"

DoCmd.TransferSpreadsheet acExport, , "QryBristolj", strFullyQualifiedFilename

MsgBox "File has been exported to your desktop" & strFullyQualifiedFilename

End Sub

However I want to be able to use this code to work for any client and therefore need a universal bit of VBA in the below line of code to work on any PC without having to ask the client for their path file.

strFullyQualifiedFilename = "C:\Users\User\Desktop\End Of Month.xlsx"

Hope that makes sense and can anyone help.

Cheers

 

[spikepl]

You'll need an environmental variable. Google VBA Environ function

 

[Neilster]

Nice one! I've sorted it using the 'VBA Environ function'

Thanks for your help. (-:

 

[spikepl]

For the benefit of the thousands that are going to read this post in the future: what was the specific solution?

 

[Pslice]

It would look like "C:\Users\" & Environ("USERNAME") & "\Desktop\End Of Month.xlsx"

 

[Neilster]

No.

=Environ("Public") & "\Desktop\End Of Month.xlsx"

 

[Pslice]

No.

=Environ("Public") & "\Desktop\End Of Month.xlsx"

Do you have a "Desktop" folder in your shared Public folder?
Does that code actually work?