Excel: Save To Desktop As Default 2

[MattGreer]

I have some code at the end of which it prompts the user to save the file. Right now the default location is 'My Documents' but I want to change it to 'Desktop'

Here's the current line of code:

Application.Dialogs.Item(xlDialogSaveAs).Show arg1:="\desktop\sheetname", arg2:=xlCSV

We use XP Pro and work in a domain environment, so the Desktop is buried in the 'Documents and Settings' folder of the current user that is logged on the workstation. This is actually something that I've been wondering for a long time, how do you access the Desktop of the current user via VBA?

Thanks!

Matt

 

[Nareik]

You may be able to trackdown the current user paths using one of the environment variables.

USe something like this to discover the correct variable

Sub PrintLocalSettings()
Dim I As Integer
I = 1
Debug.Print "Local Settings in force are:"
While Environ(I) > ""
Debug.Print I & Chr(9) & Environ(I)
I = I + 1
Wend
End Sub

then use

Environ(Environment Name) to retrieve it eg Environ("USERPROFILE")

Kieran

 

[TonyJollans]

Hi MattGreer,

You might like to look at thread705-805911

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[MattGreer]

I gave you both stars as you more or less gave a similar answer that worked, namely, using the Environment variable.

Works like a champ now! Thanks!

Thanks!!


Matt