Error 1004 Document Not Saved .ExportAsFixedFormat xlTypePDF

Hi All,

I have been working on this project for a long time and it is finally nearly done. I have a chunk of code that is intended to grab a range of cells from a form and export that range as a PDF file with a dynamic file name. This was working fine on my computer but when I tried to run the project on my coworker's computer (for whom the project is intended), I got a 1004 Error. The error box says that the PDF may be open, but there were no pdf's open and since the file name is dynamic I don't understand what it's talking about.

Both my coworker and myself are running Office 2016. Their computer does have Adobe Reader installed and I can manually export the range as a PDF just fine but the code does not work. Looking forward to any thoughts. My code is below:

        Application.ScreenUpdating = False

        Dim AppName As String
        Dim WrkOrdr As String
        Dim Location As String
        Dim AppDate As Date
        Dim AppTime As Date
        Dim PDFName As String
    
        'Get values from form fields to generate dynamic file name
        AppName = ws.Range("H9").value
        WrkOrdr = ws.Range("L10").value
        Location = ws.Range("H13").value
        AppDate = ws.Range("G35").value
        AppTime = ws.Range("H35").value

        'Generate file name string
        PDFName = "ChemApp_" & AppName & "_" & WrkOrdr & "_" & Location & "_" & Format(AppDate + AppTime, "m_d_yyyy_h_mm AM/PM") & ".pdf"
 
        ChDir "H:\PK\Spraying and Fertilizing\Chemical Application Records"
        
        'Error 1004 occurs here with the arrow on the last line
        ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "H:\PK\Spraying and Fertilizing\Chemical Application Records\" & PDFName _
            , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=True
        
        Application.ScreenUpdating = True

Thank you

Try it maybe in this way:

    Dim AppName As String, WrkOrdr As String, Location As String, PDFName As String, curdrv As String, curpth As String
    Dim AppDate As Date, AppTime As Date
    Dim ws As Worksheet '! if it has not been declared before
    
    Const strPath As String = "H:\PK\Spraying and Fertilizing\Chemical Application Records\"
    
    Application.ScreenUpdating = False
    
    '"ws" should be declared before, by "Set", is it declared ?
    'if not, then e.g.:
    Set ws = ThisWorkbook.ActiveSheet
    
    'Get values from form fields to generate dynamic file name
    AppName = Trim(ws.Range("H9").Value)
    WrkOrdr = Trim(ws.Range("L10").Value)
    Location = Trim(ws.Range("H13").Value)
    AppDate = Trim(ws.Range("G35").Value)
    AppTime = Trim(ws.Range("H35").Value)
    
    'Generate file name string
    PDFName = "ChemApp_" & AppName & "_" & WrkOrdr & "_" & Location & "_" & Format(AppDate & " " & AppTime, "m_d_yyyy_h_mm AM/PM") & ".pdf"
    
    'Possible but not needed - Part A
    'curdrv = Left(CurDir, 1)
    'curpth = ThisWorkbook.Path
    'ChDrive Left(strPath, 1)
    'ChDir strPath
    
    ws.ExportAsFixedFormat Type:=xlTypePDF, _
                            Filename:=strPath & PDFName, _
                            Quality:=xlQualityStandard, _
                            IncludeDocProperties:=True, _
                            IgnorePrintAreas:=False, _
                            OpenAfterPublish:=True
    
    'Possible but not needed - Part B
    'ChDrive curdrv
    'ChDir curpth
    
    Set ws = Nothing
    
    Application.ScreenUpdating = True

Ps.:
1) use "&" to concatenate strings, not "+"
2) The 'ChDir' statement changes the default directory but not the default drive

porucha vevrku,

I apologize for not responding sooner. I haven't been able to work on this until now. I tried your code both with and without the "Possible but not needed" sections. Both version of your code produce the following error at the ws.ExportAsFixedFormat command line:

"Run-Time error '-2147024773(8007007b)': Document not saved."

Yes, ws. is declared earlier in the procedure. I should have posted my entire code, though. My full code, with your input, is below.

Sub SaveClear_Form()

    Dim wb As Workbook
    Dim ws As Worksheet
    
        Set wb = ThisWorkbook
        Set ws = wb.Worksheets("Field Application Sheet")
        
    'Dim ReqCell As Range
    'Dim ReqRange As Range
        'Set ReqRange = ws.Range("$H$9,$H$10,$H$11,$L$10,$H$13,$M$14,$N$14,$O$14,$H$15,$H$17,$G$23,$H$23,$I$23,$J$23,$K$23,$L$23,$M$23,$N$18,$N$23,$O$23,$G$35,$H$35,$I$35,$L$35,$M$35,$N$35,$O$35,$L$36,$M$36,$N$36,$O$36")
        
        'For Each ReqCell In ReqRange
            'If ReqCell = "" Then
                'MsgBox "Form incomplete!  Ensure all fields shown in BOLD font are entered and at least the first row of the Chemicals applied table."
                'Exit Sub
            'End If
        'Next

    Dim msg As String, Ans As Variant
    
    msg = "Please confirm! This action will Record the Chemical Application and reset the form.  This action CANNOT be undone.  Review the information before clicking 'Yes'.  Clicking 'Yes' is confirmation that all information entered into this form is true and accurate.  To Abort and edit the form, click 'No'"
        
    Ans = MsgBox(msg, vbYesNo)
    
    Select Case Ans
        Case vbYes
        
        '=======================================================
        'Original Code by TFiske
        '=======================================================
        'Dim AppName As String
        'Dim WrkOrdr As String
        'Dim Location As String
        'Dim AppDate As Date
        'Dim AppTime As Date
        'Dim PDFName As String
    
        'Get values from form fields to generate dynamic file name
        'AppName = ws.Range("H9").value
        'WrkOrdr = ws.Range("L10").value
        'Location = ws.Range("H13").value
        'AppDate = ws.Range("G35").value
        'AppTime = ws.Range("H35").value

        'Generate file name string
        'PDFName = "ChemApp_" & AppName & "_" & WrkOrdr & "_" & Location & "_" & Format(AppDate + AppTime, "m_d_yyyy_h_mm AM/PM") & ".pdf"
 
        'ChDir "H:\PK\Spraying and Fertilizing\Chemical Application Records"
        
        'Error 1004 occurs here with the arrow on the last line
        'ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "H:\PK\Spraying and Fertilizing\Chemical Application Records\" & PDFName _
            , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=True
        '===========================================================
        
        '===========================================================
        'New code by Excel Help Forum: porucha vevrku
        '===========================================================
        Dim AppName As String, WrkOrdr As String, Location As String, PDFName As String, curdrv As String, curpth As String
        Dim AppDate As Date, AppTime As Date
    
        Const strPath As String = "H:\PK\Spraying and Fertilizing\Chemical Application Records\"
    
        Application.ScreenUpdating = False
        
        'Get values from form cells to generate dynamic file name
        AppName = trim(ws.Range("H9").value)
        WrkOrdr = trim(ws.Range("L10").value)
        Location = trim(ws.Range("H13").value)
        AppDate = trim(ws.Range("G35").value)
        AppTime = trim(ws.Range("H35").value)
    
        'Generate file name string
        PDFName = "ChemApp_" & AppName & "_" & WrkOrdr & "_" & Location & "_" & Format(AppDate & " " & AppTime, "m_d_yyyy_h_mm AM/PM") & ".pdf"
    
        'Possible but not needed - Part A
        curdrv = Left(CurDir, 1)
        curpth = ThisWorkbook.Path
        ChDrive Left(strPath, 1)
        ChDir strPath
        
        'Error Occurs Here
        ws.ExportAsFixedFormat Type:=xlTypePDF, _
                                Filename:=strPath & PDFName, _
                                Quality:=xlQualityStandard, _
                                IncludeDocProperties:=True, _
                                IgnorePrintAreas:=False, _
                                OpenAfterPublish:=True
    
        'Possible but not needed - Part B
        ChDrive curdrv
        ChDir curpth
    
        Set ws = Nothing
        
        
        Application.ScreenUpdating = True
        '=================================================================
        
        Call PopulateChemRecordTable
        
        Dim ClearRange As Range
            Set ClearRange = ws.Range("$H$9:$K$11,$L$10,$M$10:$O$11,$H$13,$H$15,$L$18,$M$14,$N$14,$O$14,$M$15,$H$17:$I$20,$J$18,$N$18,$G$23:$O$32,$G$35:$I$36,$L$35:$O$36,$K$38").SpecialCells(xlCellTypeConstants)
            ClearRange.ClearContents
            
        wb.Save
                
        Case vbNo
            GoTo Quit:
    End Select
    
Quit:
    
End Sub

Thank you for your help. I appreciate it.