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



"kyle" <[email protected]> wrote in message
news:[email protected]...
>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

[Tom Ogilvy]

The desktop is one of the special folders:

http://support.microsoft.com/default...b;en-us;252652
HOWTO: Use the SHGetFolderPath Function from Visual Basic

http://msdn.microsoft.com/library/de...nums/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


--
Regards,
Tom Ogilvy




"kyle" wrote:

> 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