none
VBA code - .xls to .pdf into another folder RRS feed

  • Question

  • Hans,

    I'd like to ask you some help about a VBA code.

    You have published the below code earlier, but I need to modify it as the .pdf file name be the same as the .xlsx file name, but the sheet name and the date is not necessary.

    Sub Export2PDF()
        Dim strFileName As String
        ' Change path and filename as desired
        strFileName = "C:\MyFolder\MySubfolder\" & ActiveSheet.Name & "_" & _
            Format(Date, "yyyymmdd") & ".pdf"
        ' Export active sheet to PDF
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=strFileName, OpenAfterPublish:=False
    End Sub


    Could you please help me?

    Thanks in advance

    Andras

    Wednesday, August 5, 2015 11:51 AM

Answers

  • Sub Export2PDF()
        Dim strFileName As String
        ' Change path and filename as desired
        strFileName = "C:\MyFolder\MySubfolder\" & ActiveWorkbook.Name
    
        'remove the excel extension
        strFileName = Trim(Replace(strFileName, ".xlsm", ""))
    
    
        ' Export active sheet to PDF
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=strFileName, OpenAfterPublish:=False
    End Sub
    

    • Marked as answer by Andygrass Wednesday, August 5, 2015 1:55 PM
    Wednesday, August 5, 2015 1:45 PM

All replies

  • Sub Export2PDF()
        Dim strFileName As String
        ' Change path and filename as desired
        strFileName = "C:\MyFolder\MySubfolder\" & Activeworkbook.Name
        ' Export active sheet to PDF
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=strFileName, OpenAfterPublish:=False
    End Sub

    • Proposed as answer by André Santo Wednesday, August 5, 2015 1:05 PM
    Wednesday, August 5, 2015 1:05 PM
  • Hi André,

    thanks a lot for your help,  only one small modification would be necessary: the pdf file name now contains the full file name of the Excel file, so it looks like this: "Originalfilename.xlsm.pdf". The ."xlsm" is not necesary in the pdf file. How can I remove it?

    Thanks in advance

    Andras

    Wednesday, August 5, 2015 1:23 PM
  • Sub Export2PDF()
        Dim strFileName As String
        ' Change path and filename as desired
        strFileName = "C:\MyFolder\MySubfolder\" & ActiveWorkbook.Name
    
        'remove the excel extension
        strFileName = Trim(Replace(strFileName, ".xlsm", ""))
    
    
        ' Export active sheet to PDF
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=strFileName, OpenAfterPublish:=False
    End Sub
    

    • Marked as answer by Andygrass Wednesday, August 5, 2015 1:55 PM
    Wednesday, August 5, 2015 1:45 PM
  • Hi André,

    it works perfectly.

    Thank you very much, you helped me a lot.

    Best regards

    Andras

    Wednesday, August 5, 2015 1:56 PM