none
Excel to PDF macro RRS feed

  • Question

  • First of all I really apologize if I am going to ask something stupid.

    Sub SavePDF()
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="C:\PDF\Quotation_" & _
            ActiveSheet.Range("AY8").Value & ".pdf", _
            OpenAfterPublish:=False
    End Sub

    I have tried above suggested code and it is really working great. But now I have a new requirement and I am pretty much sure that someone can help me out or guide me to find out a good solution. 

    I want to save PDF file in a specific format like "MR_invoice number_client name_month.pdf". Where MR is a constant, Invoice number is an incremental number, client name and month are also constant. All these 4 values are coming from four different cells. You can say "MR_E4_P4+1_P12_J12.pdf".

    I will really appreciate if you can help me out in this. I also want to ask can I generate a PDF file of A.xlsx from a button which is placed in B.xlsx.

    Regards, 

    Vicky

    Tuesday, August 4, 2015 11:44 AM

Answers

  • If the sheet is the active sheet in the active workbook:

    Sub SavePDF()
        Dim wsh As Worksheet
        Set wsh = ActiveSheet
        wsh.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="C:\PDF\MR_" & wsh.Range("E4").Value & "_" & _
                (wsh.Range("P4").Value + 1) & "_" & _
                wsh.Range("P12").Value & "_" & _
                wsh.Range("J12").Value & ".pdf", _
            OpenAfterPublish:=False
    End Sub

    C:\PDF is the folder to save the file in; you can change this of course.

    If the sheet is in another workbook, that workbook must be open when we export to PDF.

    If the workbook has already been opened, you can use code like this:

    Sub SavePDF()
        Dim wbk As Workbook
        Dim wsh As Worksheet
        Set wbk = Workbooks("A.xlsx")
        Set wsh = wbk.Worksheets("MySheet")
        wsh.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="C:\PDF\MR_" & wsh.Range("E4").Value & "_" & _
                (wsh.Range("P4").Value + 1) & "_" & _
                wsh.Range("P12").Value & "_" & _
                wsh.Range("J12").Value & ".pdf", _
            OpenAfterPublish:=False
    End Sub

    Change the name of the workbook and of the worksheet to match your situation.

    If the other workbook is not open when the macro starts, we have to open it:

    Sub SavePDF()
        Dim wbk As Workbook
        Dim wsh As Worksheet
        Set wbk = Workbooks.Open("C:\Excel\A.xlsx")
        Set wsh = wbk.Worksheets("MySheet")
        wsh.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="C:\PDF\MR_" & wsh.Range("E4").Value & "_" & _
                (wsh.Range("P4").Value + 1) & "_" & _
                wsh.Range("P12").Value & "_" & _
                wsh.Range("J12").Value & ".pdf", _
            OpenAfterPublish:=False
    End Sub

    Change the path C:\Excel\A.xlsx and the sheet name as needed.

    Niggling remark: the workbook containing the code must be a .xlsm, .xlsb or .xls workbook; it cannot be a .xlsx workbook.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, August 4, 2015 3:16 PM
  • If your default date format contains a /, e.g. 08/09/15 or 09/08/2015, you cannot use the value of the cell directly because / is not allowed in a file name. You could use a format such as 20150809: change wsh.Range("J12").Value in the code to Format(wsh.Range("J12").Value, "yyyymmdd")

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, August 9, 2015 2:30 PM

All replies

  • If the sheet is the active sheet in the active workbook:

    Sub SavePDF()
        Dim wsh As Worksheet
        Set wsh = ActiveSheet
        wsh.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="C:\PDF\MR_" & wsh.Range("E4").Value & "_" & _
                (wsh.Range("P4").Value + 1) & "_" & _
                wsh.Range("P12").Value & "_" & _
                wsh.Range("J12").Value & ".pdf", _
            OpenAfterPublish:=False
    End Sub

    C:\PDF is the folder to save the file in; you can change this of course.

    If the sheet is in another workbook, that workbook must be open when we export to PDF.

    If the workbook has already been opened, you can use code like this:

    Sub SavePDF()
        Dim wbk As Workbook
        Dim wsh As Worksheet
        Set wbk = Workbooks("A.xlsx")
        Set wsh = wbk.Worksheets("MySheet")
        wsh.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="C:\PDF\MR_" & wsh.Range("E4").Value & "_" & _
                (wsh.Range("P4").Value + 1) & "_" & _
                wsh.Range("P12").Value & "_" & _
                wsh.Range("J12").Value & ".pdf", _
            OpenAfterPublish:=False
    End Sub

    Change the name of the workbook and of the worksheet to match your situation.

    If the other workbook is not open when the macro starts, we have to open it:

    Sub SavePDF()
        Dim wbk As Workbook
        Dim wsh As Worksheet
        Set wbk = Workbooks.Open("C:\Excel\A.xlsx")
        Set wsh = wbk.Worksheets("MySheet")
        wsh.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="C:\PDF\MR_" & wsh.Range("E4").Value & "_" & _
                (wsh.Range("P4").Value + 1) & "_" & _
                wsh.Range("P12").Value & "_" & _
                wsh.Range("J12").Value & ".pdf", _
            OpenAfterPublish:=False
    End Sub

    Change the path C:\Excel\A.xlsx and the sheet name as needed.

    Niggling remark: the workbook containing the code must be a .xlsm, .xlsb or .xls workbook; it cannot be a .xlsx workbook.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, August 4, 2015 3:16 PM
  • Please try this...

    Sub Convert_Excel_To_PDF()
        Dim MyPath As String, FilesInPath As String
        Dim MyFiles() As String, Fnum As Long
        Dim mybook As Workbook
        Dim CalcMode As Long
        Dim sh As Worksheet
        Dim ErrorYes As Boolean
        Dim LPosition As Integer
     
        'Fill in the path\folder where the Excel files are
        MyPath = "c:\Documents and Settings\shuerya\Desktop\ExcelFiles\"
     
        FilesInPath = Dir(MyPath & "*.xl*")
        If FilesInPath = "" Then
            MsgBox "No files found"
            Exit Sub
        End If
     
        Fnum = 0
        Do While FilesInPath <> ""
            Fnum = Fnum + 1
            ReDim Preserve MyFiles(1 To Fnum)
            MyFiles(Fnum) = FilesInPath
            FilesInPath = Dir()
        Loop
     
        With Application
            CalcMode = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
            .EnableEvents = False
        End With
     
        If Fnum > 0 Then
            For Fnum = LBound(MyFiles) To UBound(MyFiles)
                Set mybook = Nothing
                On Error Resume Next
                Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
                On Error GoTo 0
     
                If Not mybook Is Nothing Then
     
                        
                        LPosition = InStr(1, mybook.Name, ".") - 1
                        mybookname = Left(mybook.Name, LPosition)
                        mybook.Activate
                        'All PDF Files get saved in the directory below:
                        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                            "C:\Documents and Settings\shuerya\Desktop\PDFFiles\" & mybookname & ".pdf", _
                            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                            :=False, OpenAfterPublish:=False
                            
                End If
                
                mybook.Close SaveChanges:=False
     
            Next Fnum
        End If
     
        If ErrorYes = True Then
            MsgBox "There are problems in one or more files, possible problem:" _
                 & vbNewLine & "protected workbook/sheet or a sheet/range that not exist"
        End If
     
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .Calculation = CalcMode
        End With
    End Sub
    


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, August 6, 2015 3:29 AM
  • Hi Hans,

    Let me tell you ... you are a life saver for me .... The code is working excellent... But as a tech greedy now I want to improve this solution in more effective way .... and again you are my last hope :)

    I want to add 2 things in this code ... I tried it but as usual I am not lucky enough to implement these changes ...

    1. Above code is only converting first sheet of my excel file in PDF. Can I convert all the sheets in one PDF Currently I have 2 different sheets in my A.xlsx.

    2. wsh.Range("P4").Value + 1) & "_" & _ .... this code is also working fine but it increments only 1 value. For Example I have a value 21200 in my P4 cell. Above code is generating file with the name of MR_customername_21201_month.pdf. Every time it is saving with the same file name. It will be great if I can generate a file next time with an increment value like MR_customername_21202_month.pdf and so on.

    Regards, 

    Vicky

    Thursday, August 6, 2015 9:19 AM
  • @ryguy72 Thank You. 



    • Edited by VickyHanif Thursday, August 6, 2015 10:00 AM
    Thursday, August 6, 2015 9:20 AM
  • 1) To export all worksheets, change

        wsh.ExportAsFixedFormat ...

    to

        wbk.ExportAsFixedFormat ...

    2) Add a line

        wsh.Range("P4").Value = wsh.Range("P4").Value + 1

    just above End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, August 6, 2015 2:07 PM
  • Hi Hans,

    Thanks for everything ... really appreciated ...... one last thing and I hope I am not bothering you. When I am trying to get date from a Cell in below code it is giving me error. Date is in Cell J12. I think its because of format?

    wsh.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="C:\PDF\MR_" & wsh.Range("E4").Value & "_" & _
                (wsh.Range("P4").Value + 1) & "_" & _
                wsh.Range("P12").Value & "_" & _
                wsh.Range("J12").Value & ".pdf", _

    I am trying to get date in "J12" by a formula =Today(). How can I resolve this issue because date is an important part of file name.

    Regards, 

    Sunday, August 9, 2015 2:26 PM
  • If your default date format contains a /, e.g. 08/09/15 or 09/08/2015, you cannot use the value of the cell directly because / is not allowed in a file name. You could use a format such as 20150809: change wsh.Range("J12").Value in the code to Format(wsh.Range("J12").Value, "yyyymmdd")

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, August 9, 2015 2:30 PM
  • Hello Hans - I've been searching many of these threads and was hoping you could assist me. I would like to create a macro that saves the active sheet to a pdf named in this structure: Cell 1_Cell 2.pdf and places it into the same folder the excel spreadsheet is saved in. Any thoughts? Thanks!

    Thursday, April 6, 2017 6:56 PM
  • For example:

    Sub SaveAsPDF()
        If Range("C1").Value = "" Or Range("C2").Value = "" Then
            MsgBox "Please make sure that C1 and C2 are filled in!", vbExclamation
            Exit Sub
        End If
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & _
            Application.PathSeparator & Range("C1").Value & "_" & Range("C2").Value & ".pdf"
    End Sub

    (I used C1 and C2 as cells, you can easily change that)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Thursday, April 6, 2017 7:02 PM
  • Hans - thank you! 

    I'm having some trouble and was hoping you could help again. Two things:

    1) I've created this worksheet as a template and that entire sheet will then be copy/pasted into another workbook and saved. When I do this and press my 'save to pdf' button, the sheet tries to pull and run the macro from the template workbook and then subsequently saves the pdf in the file where the template workbook resides. Is there a way to remedy this?

    2) I was experimenting with other macros/code for other things - placing the file name into a cell for example. I have deleted the modules, but the code is still running. How do I get this permanently deleted?


    Tuesday, April 11, 2017 1:23 PM
  • I figured out my answer to #2 - apparently even having a space left in the code window will cause it to run! 

    Thank you in advance for your help!!

    Tuesday, April 11, 2017 1:31 PM
  • Does it help if you change ThisWorkbook to ActiveWorkbook?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, April 11, 2017 4:38 PM
  • Thank you, Hans. That saves the file in the correct location, but unfortunately it still runs the macro from the template file. It seems when you copy/paste a worksheet, the VBA module doesn't get copy/pasted with it (it's not listed in the VBA window) and when the macro runs it calls it from the original sheet. I wonder if there's a why to break that connection and copy/paste the macro?
    Tuesday, April 11, 2017 7:07 PM
  • If you copy an entire workbook, macro(s) will be copied with it. But if you copy a worksheet, only code in the worksheet's module will be copied with it; ordinary macros won't travel with the worksheet.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, April 11, 2017 7:10 PM
  • Thank you, Hans! I really appreciate your help. I have one final question that I would love some help on if you have time. While trying to figure out how to display the filename in a cell, I came across the following macro and implemented it without thinking through the consequences: 
    Private Sub Workbook_Open()
    
        Range("d3") = ThisWorkbook.Name
    
    End Sub

    Now, every time I open a workbook this code is populated and its driving me bonkers. How do I delete it for good?

    I'm using Excel 2010 in case that makes a difference - thanks!

    Tuesday, April 11, 2017 7:47 PM
  • Press Alt+F11 to activate the Visual Basic Editor.

    Double-click ThisWorkbook under Microsoft Excel Objects in the Project Explorer pane on the left hand side.

    Delete the code from the ThisWorkbook module.

    Switch back to Excel.

    Save the workbook.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, April 11, 2017 8:02 PM