宏以将Excel Doc导出为所有用户的PDF

本文介绍了宏以将Excel Doc导出为所有用户的PDF的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我需要创建一个宏，将Excel文档作为PDF文件保存到任何用户的桌面(即，多个人将使用此文档/宏).

I need to create a macro that will save an excel document as a PDF file to any user's desktop (i.e. multiple people will be using this document/macro).

这是我到目前为止拥有的VBA代码:

Here is VBA code I have so far:

Sub CreatePDF()
'
' CreatePDF Macro
'

'
    ChDir "C:\Users\Public\Desktop"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\Public\Desktop\QuickView Update Dec_2017.pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
End Sub

我认为问题在于保存到的目录(在此代码在文件路径中显示"Public"的情况下，我已将其更改为最初填充的用户名).

I think the issue is with the directory it's saving to (in the instances where this code says "Public" in the file path, I had changed that from my username which was initially populated).

有人知道一种指定将文档另存为PDF到任何用户桌面的通用路径的方法吗?

Does anyone know a way to specify a generic path to save this document as a PDF to any users' desktop?

推荐答案

使用.specialfolders("Desktop")保存到桌面.设置为字符串变量并添加路径分隔符

Use .specialfolders("Desktop") to save to the desktop. Set to a string variable and add the path separator

示例

Option Explicit
Sub CreatePDF()
    Dim FilePath As String

    FilePath = CreateObject("WScript.Shell").specialfolders("Desktop")

    Debug.Print FilePath

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                    FileName:=FilePath & "\" & "QuickView Update Dec_2017.pdf", _
                    Quality:=xlQualityStandard, _
                    IncludeDocProperties:=True, _
                    IgnorePrintAreas:=False, _
                    OpenAfterPublish:=True

End Sub

AllUsersDesktop

AllUsersDesktop

AllUsersStartMenu

AllUsersPrograms

AllUsersStartup

台式机

收藏夹

字体

MyDocuments

NetHood

PrintHood

程序