Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-28-2019, 02:37 PM
LearnerExcel LearnerExcel is offline Add file name automatically in the vba code ... Windows 7 32bit Add file name automatically in the vba code ... Office 2013
Advanced Beginner
Add file name automatically in the vba code ...
 
Join Date: Nov 2016
Posts: 81
LearnerExcel will become famous soon enoughLearnerExcel will become famous soon enough
Default 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
Reply With Quote
  #2  
Old 12-29-2019, 03:48 AM
gmayor's Avatar
gmayor gmayor is offline Add file name automatically in the vba code ... Windows 10 Add file name automatically in the vba code ... Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
Reply With Quote
  #3  
Old 12-29-2019, 10:13 AM
LearnerExcel LearnerExcel is offline Add file name automatically in the vba code ... Windows 7 32bit Add file name automatically in the vba code ... Office 2013
Advanced Beginner
Add file name automatically in the vba code ...
 
Join Date: Nov 2016
Posts: 81
LearnerExcel will become famous soon enoughLearnerExcel will become famous soon enough
Default

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".
Reply With Quote
  #4  
Old 12-30-2019, 06:11 AM
gmayor's Avatar
gmayor gmayor is offline Add file name automatically in the vba code ... Windows 10 Add file name automatically in the vba code ... Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Add file name automatically in the vba code ... Code to change highlight on content control automatically gsbpxw Word VBA 2 06-11-2018 03:05 AM
Add file name automatically in the vba code ... vb code for updating file automatically klpw Excel Programming 1 01-14-2016 08:05 PM
Add file name automatically in the vba code ... 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
Add file name automatically in the vba code ... How to convert a text file to an Excel file with the data format automatically? mradmin Excel 6 10-16-2013 10:34 AM
Add file name automatically in the vba code ... How to copy automatically data from Excel file to Word file? fuchsd Word 6 10-25-2011 05:52 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:08 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft