PDA

View Full Version : File saving macro


rsdjimbie
May 4, 2009, 12:47 AM
How do I change the following code so all pc's on network can run macro and have it saved on their desktops?


ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\User1\Desktop\FAWU", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False

JBeaucaire
May 4, 2009, 06:37 AM
Put this two lines of code at the TOP of your macro, one to declare a variable for the Desktop filepath and one to solve for that variable.

Dim DTAddress As String
DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator

Further down in the macro you can now use DTAddress in place of the path, just add the filename:

ActiveWorkbook.SaveAs DTAddress & "FAWU.xls"

These are the system folders that can be used with "SpecialFolders":
'Options For special folders

AllUsersDesktop
AllUsersStartMenu
AllUsersPrograms
AllUsersStartup
Desktop
Favorites
Fonts
MyDocuments
NetHood
PrintHood
Programs
Recent
SendTo
StartMenu
Startup
Templates

rsdjimbie
May 4, 2009, 07:20 AM
Thank you, works very well.
JB you helped me so much, you are an insperation, so I have orderd myself VBA for Excel for dummies as a start.

JBeaucaire
May 4, 2009, 09:07 AM
Glad to be of aid. I've never used that myself, but the internet is a wicked fast research tool... found that for you with just a little digging and testing.

I learned something useful myself in the process and now have SpecialFolders() as a permanent technique in my arsenal... thanks to you!

Cheers.