How do you export an Excel document to PDF after cell value input?-CodePudding

References:

OS: Windows 10 Enterprise

Excel Pro Plus 2016 - 32bit

FileType - xlsm

Operation Details:

Once the last cell input box prompt is completed, the document should automatically export active sheet to PDF without need to save the document. The exported PDF should have a document name given from the title label cell & pub label cell.

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)


Dim rngC As Range
    Set rngC = Intersect(Target, Me.Range("Publication"))
    
Dim trig As Range
Dim title As String
Dim pub As String

   Set trig = Intersect(Target, Me.Range("OldDoc"))

    
    Select Case True
    

    Case Not rngC Is Nothing

Dim entryDate As String

         entryDate = Format$(Now, "ddmmmm,yyyy")

         On Error GoTo SafeExit
         Application.EnableEvents = False

Dim cellc As Range

         For Each cellc In rngC
             If Not IsEmpty(cellc) Then
                cellc.Offset(1).Value = entryDate
             End If
    Next


    
        Case Not trig Is Nothing
        
    title = Range("Title").Text
    pub = Range("Publication").Text
        
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Quality:=xlQualityStandard, _
                                    IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                                    OpenAfterPublish:=True, Filename:=title & "-" & pub
        
End Select

    

SafeExit:
    Application.EnableEvents = True
    
    


End Sub

Additional Notes:

I have included my entire worksheet_change code to show how I have all the processes laid out.

CodePudding user response：

The Cell with the last input value is defined as follows:

Dim trig As Range

Set trig = Intersect(Target, Me.Range("OldDoc")) 'This will be the variable to trigger the export syntax

For this process I use the Case statement for my code to identify which statement is true and then perform that function. So We start with Select Case True.

To make the case statement true for this process we compare it as a false negative so when an input is made it will activate the export process:

Case Not trig Is Nothing 'This is looking to see for changes in the variable, then will proceed to the process syntax in the statement

To remove the process of saving the document prior to formatting to PDF, the argument OpenAfterPublish:=True for ExportAsFixedFormat. This will automatically open your excel document in adobe Acrobat or preferred PDF software for continued editing.

The final should look like this (when using the case statement format):

Dim trig As Range
Dim title As String
Dim pub As String

   Set trig = Intersect(Target, Me.Range("OldDoc"))

    
    Select Case True
        Case Not trig Is Nothing
        
    title = Range("Title").Text
    pub = Range("Publication").Text
        
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Quality:=xlQualityStandard, _
                                    IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                                    OpenAfterPublish:=True, Filename:=title & "-" & pub
        
End Select

Note:

All cell ranges are specified cell labels, interchange with preferred cells as needed. And please provide feedback as I am still developing with vba and the correct vocabulary.