Add file name automatically in the vba code ...

LearnerExcel · #1 12-28-2019, 02:37 PM

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

gmayor · #2 12-29-2019, 03:48 AM

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

LearnerExcel · #3 12-29-2019, 10:13 AM

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".

gmayor · #4 12-30-2019, 06:11 AM

Add the option to 'OpenAfterPublish' i.e.

Code:

.ExportAsFixedFormat Type:=xlTypePDF, _
                             FileName:=sPath & sName, _
                             Quality:=xlQualityStandard, _
                             IgnorePrintAreas:=False, _
                             OpenAfterPublish:=True

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

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