PDA

View Full Version : [SOLVED] I want to create a Macro That will save



Erays
03-14-2005, 09:23 PM
I want to create a macro that will save an activeworksheet to a specific folder on the desktop, you see I am user LC67b, and I want it to work with other users, they could be any user such as lc59b Example below
C:\Documents and Settings\LC67b\Desktop\ExcelWorkbooks\

MWE
03-14-2005, 10:09 PM
I want to create a macro that will save an activeworksheet to a specific folder on the desktop, you see I am user LC67b, and I want it to work with other users, they could be any user such as lc59b Example below
C:\Documents and Settings\LC67b\Desktop\ExcelWorkbooks\
I think you mean activeworkbook. You can save the activeworkbook anywhere you like using the Workbook().Close procedure. If LC67b is your logon name, you can fetch that by calling Application.UserName. So, we could construct a general proc that will save the active workbook to the correct location. It might be something like:


Sub SaveSpecial()
Dim NewFileName As String
NewFileName = "C:\Documents and Settings\" + Application.UserName + _
"\Desktop\ExcelWorkbooks\" + ActiveWorkbook.Name
ActiveWorkbook.Close SaveChanges:=True, Filename:=NewFileName
End Sub

Jacob Hilderbrand
03-14-2005, 10:15 PM
Additionally you should check out this Kb Entry (http://www.vbaexpress.com/kb/getarticle.php?kb_id=216) for a method of getting the Desktop address.