How to save Excel Workbook to Desktop regardless of user

Question

I have an Excel Workbook that on Form Button click I need to save a copy of the workbook to the user s Desktop   Originally everything was going to be on a shared Network folder  but now I have about 6 different users that when they click the button  I need to save the workbook to their individual Desktops   Is their a way  coding-wise  to save to the machine Desktop without having to specify individual users  which would require me to maintain 6 different Workbook files

User · Answer

You ve mentioned that they each have their own machines  but if they need to log onto a co-workers machine  and then use the file  saving it through  C  Users Public Desktop   will make it available to different usernames   Public Sub SaveToDesktop       ThisWorkbook SaveAs Filename   C  Users Public Desktop    amp  ThisWorkbook Name  amp    copy          FileFormat  xlOpenXMLWorkbookMacroEnabled End Sub   I m not sure whether this would be a requirement  but may help

User · Answer

Not sure if this is still relevant  but I use this way  Public bEnableEvents As Boolean Public bclickok As Boolean Public booRestoreErrorChecking As Boolean    put this at the top of the module   Private Declare Function apiGetComputerName Lib  kernel32  Alias    GetComputerNameA   ByVal lpBuffer As String  nSize As Long  As Long Private Declare Function apiGetUserName Lib  advapi32 dll  Alias    GetUserNameA   ByVal lpBuffer As String  nSize As Long  As Long  Function GetUserID   As String   Returns the network login name On Error Resume Next Dim lngLen As Long  lngX As Long Dim strUserName As String strUserName   String  254  0  lngLen   255 lngX   apiGetUserName strUserName  lngLen  If lngX  lt  gt  0 Then     GetUserID   Left  strUserName  lngLen - 1  Else     GetUserID      End If Exit Function End Function   This next bit I save file as PDF  but can change to suit  Public Sub SaveToDesktop   Dim LoginName As String LoginName   UCase GetUserID   ChDir  C  Users    amp  LoginName  amp    Desktop   Debug Print LoginName ActiveSheet ExportAsFixedFormat Type  xlTypePDF  Filename          C  Users    amp  LoginName  amp    Desktop MyFileName pdf   Quality  xlQualityStandard        IncludeDocProperties  True  IgnorePrintAreas  False  OpenAfterPublish         True End Sub

User · Answer

I think this is the most reliable way to get the desktop path which isn t always the same as the username   MsgBox CreateObject  WScript Shell   specialfolders  Desktop

[excel] How to save Excel Workbook to Desktop regardless of user?

Examples related to excel

Examples related to vba