VBA: How to save Excel Workbook to Desktop regardless of user?
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)?
我认为这是获取桌面路径的最可靠的方式，它不总是与用户名相同。
MsgBox CreateObject("WScript.Shell").specialfolders("Desktop")
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:UsersPublicDesktop" will make it available to different usernames.
Public Sub SaveToDesktop()
ThisWorkbook.SaveAs Filename:="C:UsersPublicDesktop" & ThisWorkbook.Name & "_copy", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub
I'm not sure whether this would be a requirement, but may help!
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 <> 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" & LoginName & "Desktop"
Debug.Print LoginName
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:Users" & LoginName & "DesktopMyFileName.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
End Sub