vba 保存 Excel 文件并使用不同的工作表将其导出为 pdf
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11334438/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Save an Excel file and export it to pdf with a different sheet
提问by user1502325
I have never written VBA code, but I checked on internet for some information.
我从未编写过 VBA 代码，但我在互联网上查看了一些信息。
My wish is the following: I have an Excel file with 3 sheets. On one of them, I'd like to add one button which can:
我的愿望如下：我有一个包含 3 张纸的 Excel 文件。在其中之一上，我想添加一个按钮，它可以：
- Save the totality of my Excel file following this naming convention:
[name of a cells of a page]_AP_[date of today].xls. - Save one of the sheets in a .pdf file.
- Print 2 of the 3 sheets while adjusting the contents.
- 按照以下命名约定保存我的 Excel 文件的全部内容：
[name of a cells of a page]_AP_[date of today].xls. - 将其中一张工作表保存在 .pdf 文件中。
- 在调整内容的同时打印 3 张中的 2 张。
I already started something, but I'm really bad at programming:
我已经开始了一些事情，但我真的很不擅长编程：
Public Sub Savefile_Click() 'copie sauvegarde classeur
' save my file following a name
Dim nom As String
Dim chemin As String
Dim wSheet As Worksheet
chemin = "C:\Users\aaa\Desktop"
nom = [Q13].Value & "_" & Day(Date) & "-" & Month(Date) & "-" & Year(Date) _
& ".xlsm"
With ActiveWorkbook
.SaveAs Filename:=chemin & nom
.Close
rep = MsgBox("Fichier excell sauvegardé")
End With
' ... and print my active sheet (where the button will stay)
For Each wSheet In ActiveWorkbook.Worksheets
If wSheet.Visible Then wSheet.PrintOut
Next
'Save my page 'offre' in pdf on my desktop and print it
Worksheets("OFFRE A ENVOYER").Range("A1:i47").ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=[Q13].Value & "_Offre de prix", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
End Sub
After that there will be another option and details, but this is really the base.
之后会有另一个选项和细节，但这确实是基础。
回答by Trace
1) Save as Excel
1）另存为Excel
Dim nom As String
nom = ThisWorkbook.Sheets(1).Range("Q13").Value & "AP" & Format(Date, "ddmmyyyy") & ".xls"
thisworkbook.saveas sPath & nom 'Define path first, don't forget the \ at the end.
Even better would be to create a named range from range "Q13" and use:
更好的是从范围“Q13”创建一个命名范围并使用：
nom = thisworkbook.names("Something").referstorange.value
To make the link dynamic in case you insert a column or row which shifts all your ranges.
使链接动态化，以防您插入移动所有范围的列或行。
2) Save workbook as PDF
2) 将工作簿另存为 PDF
ThisWorkbook.ExportAsFixedFormat xlTypePDF, sPath & sFile 'Define here .pdf
3) "print 2 of the 3sheets with adjusting the contenant of a "
3）“打印3张中的2张，调整a的内容”
I'm not sure if I get this one...
我不确定我是否得到了这个...
Print command is given by:
打印命令由以下给出：
Set oSheet= thisworkbook.sheets(2)
with oSheet.PageSetup
.PrintArea = "$A1:$Q"
...
'Any other properties: http://www.java2s.com/Code/VBA-Excel-Access-Word/Excel/AllpropertiesofPageSetup.htm
'任何其他属性：http: //www.java2s.com/Code/VBA-Excel-Access-Word/Excel/AllpropertiesofPageSetup.htm
end with
oSheet.printout
Which ever way you want to program this in order to retrieve the sheets that you need to print. You can loop through the sheets with a counter and put if statements to add conditions.
您想以哪种方式对其进行编程以检索您需要打印的工作表。您可以使用计数器遍历工作表并放置 if 语句以添加条件。
dim oSheet as Excel.worksheet
dim iCnt as integer
For each oSheet in thisworkbook.sheets
iCnt = iCnt + 1
'Include conditions here
If ... then 'Whatever condition
set oSheet = thisworkbook.sheets(iCnt)
'Print
end if
next oSheet
回答by m k soni
thank you ...i was searching this. this worked very well.
谢谢...我正在寻找这个。这工作得很好。
Option Explicit Sub SvMe() 'Save filename as value of A1 plus the current date
Option Explicit Sub SvMe() '将文件名保存为 A1 的值加上当前日期
Dim newFile As String, fName As String
' Don't use "/" in date, invalid syntax
fName = Range("A1").Value
newFile = fName & " " & Format$(Date, "mm-dd-yyyy")
' Change directory to suit
ChDir _
"C:\Users\user\Desktop" 'YOU MUST Change USER NAME to suit
ThisWorkbook.ExportAsFixedFormat xlTypePDF, Filename:=newFile
End Sub
结束子
this 1. saves my file in pdf format and 2. does not prompt me for attending save as dialog box 3. saves file using cell value in A1 and date stamp
这 1. 以 pdf 格式保存我的文件 2. 不提示我参加另存为对话框 3. 使用 A1 中的单元格值和日期戳保存文件
