#1
|
|||
|
|||
Add file name automatically in the vba code ...
How can I add the file name automatically in the Excel VBA code?
ActiveSheet.Select ActiveSheet.PageSetup.PrintArea = "$A$1:$AE$40" ActiveSheet.PageSetup.Orientation = xlPortrait ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="\\D:\MDH_Data\Delivery Notes\Print_DeliveryNote_001.pdf", Quality:=xlQualityStandard, IgnorePrintAreas:=False Note: Print_DeliveryNote_001.pdf. This file name should be taken automatically from DeliveryNote.xlsx Cell "D4" and put in the below code line. ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="\\D:\MDH_Data\Delivery Notes\Print_DeliveryNote_001.pdf", Quality:=xlQualityStandard, IgnorePrintAreas:=False |
#2
|
||||
|
||||
Is DeliveryNote.xlsx the same workbook as the activeworkbook? If so then much depends on what is in cell D4. If it contains Print_DeliveryNote_001.pdf then sName is the content of D4. If D4 contains only a number then you need the optional lines.
Code:
Const sPath As String = "D:\MDH_Data\Delivery Notes\" Dim sName As String With ActiveSheet sName = .Range("D4") If IsNumeric(sName) Then 'Optional sName = "Print_DeliveryNote_" & Format(sName, "000") & ".pdf" 'Optional End If 'Optional .PageSetup.PrintArea = "$A$1:$AE$40" PageSetup.Orientation = xlPortrait .ExportAsFixedFormat Type:=xlTypePDF, _ FileName:=sPath & sName, _ Quality:=xlQualityStandard, _ IgnorePrintAreas:=False End With
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
Thanks for your kind help. It works quite nice. Just one more request. How can I open that .pdf file automatically once it saved? The filename format is "MDH-DN-2".
|
#4
|
||||
|
||||
Add the option to 'OpenAfterPublish' i.e.
Code:
.ExportAsFixedFormat Type:=xlTypePDF, _ FileName:=sPath & sName, _ Quality:=xlQualityStandard, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=True
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Code to change highlight on content control automatically | gsbpxw | Word VBA | 2 | 06-11-2018 03:05 AM |
vb code for updating file automatically | klpw | Excel Programming | 1 | 01-14-2016 08:05 PM |
Auto Save Every Page(s) as a separate file, and name each new file automatically by the first line? | commissarmo | Word VBA | 3 | 03-14-2015 12:53 AM |
How to convert a text file to an Excel file with the data format automatically? | mradmin | Excel | 6 | 10-16-2013 10:34 AM |
How to copy automatically data from Excel file to Word file? | fuchsd | Word | 6 | 10-25-2011 05:52 AM |