use a macro to "Save As" to a generic desktop

[kyle]

I have a program that uses the SaveAs feature. However, the program is going
to be used my many users and I wanted to automate the process and have the
sheet save to the desktop. My problem is that the user id which is apart of
the address changes for each person. Is there a way to just save the file to
the desktop and not worry about the rest of the address? Here is my code....

ans = MsgBox("Save file as " & sFilename)
If ans = vbOK Then
ActiveWorkbook.SaveAs "C:\Documents and Settings\????????\Desktop" &
sFilename '
End If


Thanks,
Kyle

 

[Steve Yandl]

Kyle,

Here is one method to find the path to the logged on user's desktop.

______________________

Const Desktop = &H10&
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(Desktop)
strDeskPath = objFolder.Self.Path
MsgBox strDeskPath

_______________________

Steve

 

[Tom Ogilvy]

The desktop is one of the special folders:

http://support.microsoft.com/default.aspx?scid=kb;en-us;252652
HOWTO: Use the SHGetFolderPath Function from Visual Basic

http://msdn.microsoft.com/library/d...llcc/platform/shell/reference/enums/csidl.asp


or you can use the windows scripting host:


Create a link to the Windows Scripting Host Runtime libary
C:\WINDOWS\SYSTEM\WSHOM.OCX


Name is: IWshRuntimeLibrary


In Tools=>References in the VBE it will be listed as: Windows Scripting Host
Object Model or something similar.


You can use this to get your desktop location:


Sub Tester15()


Dim wsh As New IWshShell_Class
Dim DesktopPath As String
DesktopPath = wsh.SpecialFolders.Item("Desktop")
msgbox DeskTopPath
end Sub


or without setting the reference


Sub Tester16()
Dim wsh As Object
Dim DesktopPath As String
set wsh = CreateObject("wscript.shell")
DesktopPath = wsh.SpecialFolders.Item("Desktop")
msgbox DeskTopPath
end Sub