Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
use a macro to "Save As" to a generic desktop
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
use a macro to "Save As" to a generic desktop
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
use a macro to "Save As" to a generic desktop
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting "Save As" adds "Copy of" to file name- MS Excel 2007 | Excel Discussion (Misc queries) | |||
"CELL("FILENAME") NOT UPDATE AFTER "SAVE AS" ACTION | Excel Discussion (Misc queries) | |||
"Save" and "Save As" options greyed out - "Save as Webpage" option | Excel Discussion (Misc queries) | |||
save and restore "Workbook Menu Bar" & "Cell" menus | Excel Programming | |||
Is there a "generic" Error Handling method | Excel Programming |