CODEWITHSUNDEEP
vbaexcel
Lucho
Lucho

Reputation: 23

Excel To PDF issue

So I have this simple little code to convert an Excel sheet to PDF on a command button on my Excel sheet:

Sub Save_Excel_As_PDF()

    ActiveSheet.ExportAsFixedFormat _
                  Type:=xlTypePDF

End Sub

The problem is that I have to manually go through the steps first ( Save as, then PDF, etc.) in order for the button to work after I go through the manual steps first.

I wanted to save this anywhere and just click the button to create the PDF without first going through all the initial manual steps. Can this code be modified to do that?

Upvotes: 2

Views: 626

Answers (3)

ASH
ASH

Reputation: 20302

This script below will convert all Excel files to PDF files.

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:\Users\yourpath_here\"

    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:\Users\your_path_here\" & 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

Upvotes: 0

A.S.H
A.S.H

Reputation: 29332

Without specifying the FileName parameter, the PDF will be saved in your Documents folder. After you do a manual Save Asin some folder, next time it will be created in the same folder.

You dont need this at all, you can create the file in the same folder as your workbook, with the same name as the worksheet's name, by specifying the FileName parameter:

 ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
     FileName:=ThisWorkbook.Path & "\" & ActiveSheet.name

You can specify another name or another folder than ThisWorkbook.Path.

Upvotes: 4

Shyam Sundar Shankar
Shyam Sundar Shankar

Reputation: 96

Guess this works for me:

Sub Macro1()

ChDir "C:\Users\Shyamsundar.Shankar\Desktop"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\Shyamsundar.Shankar\Desktop\Sheet1.pdf", Quality:=xlQualityStandard

End Sub

Upvotes: 1

Related Questions