宏以将Excel Doc导出为所有用户的PDF [英] Macro to export Excel Doc as PDF for all Users

问题描述

我需要创建一个宏，将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

AllUsersStartMenu

AllUsersPrograms

AllUsersPrograms

AllUsersStartup

AllUsersStartup

台式机

收藏夹

字体

MyDocuments

MyDocuments

NetHood

PrintHood

PrintHood

程序

最近

发送至

开始菜单

启动

模板

_{如果请求的文件夹(strFolderName)不可用，则SpecialFolders属性返回一个空字符串.例如，Windows 95没有AllUsersDesktop文件夹，并且如果strFolderName是AllUsersDesktop，则返回空字符串.}

这是另一个示例 https://stackoverflow.com/a/31694603/4539709

这篇关于宏以将Excel Doc导出为所有用户的PDF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！