Results 1 to 3 of 3

Help with save as PDF with Dynamic File Name: ActiveSheet.ExportAsFixedFormat method

Threaded View

  1. #1
    Forum Contributor
    Join Date
    03-30-2016
    Location
    Gillette, WY
    MS-Off Ver
    Office 365
    Posts
    230

    Help with save as PDF with Dynamic File Name: ActiveSheet.ExportAsFixedFormat method

    Hi all,

    I am working on an application to help us track our chemical applications. In the attached .xlsm file, the user would fill out the fields highlighted in blue on the "Field Application Sheet" tab. Then the "Save Form" button will have the primary function of saving the range B8:L42 as a PDF file with a dynamic file name. There are also header and footer fields that will need to be published with the PDF but I understand that if I set the IncludeDocProperties = True then they should be. In the attached example the file name I would want is:

    "ChemApp_JQ Applicator_123456_St. John's Hwy ROW_3/20/2018_12:00:00 PM.pdf"

    I am using the following code:

    Sub SaveClear_Form()
    
        Dim AppName As String
        Dim WrkOrdr As String
        Dim Location As String
        Dim AppDate As Date
        Dim AppTime As Date
        Dim PDFName As String
        
        Set wb = ThisWorkbook
        Set ws = wb.Worksheets("Field Application Sheet")
        
        'Get values from form fields to generate dynamic file name
        AppName = ws.Range("D9").Value
        WrkOrdr = ws.Range("H10").Value
        Location = ws.Range("D13").Value
        AppDate = ws.Range("C35").Value
        AppTime = ws.Range("D35").Value
    
        PDFName = "ChemApp_" & AppName & "_" & WrkOrdr & "_" & Location & "_" & AppDate & "_" & AppTime & ".pdf"
     
        ChDir "U:\ExcelTraining\Excel App Develop\Chemical Application Sheets"
        'Error Occurs Here: Run-Time ERROR '-2147024773 (8007007b)':
            'Automation error
            'The filename, directory name, or volume label syntax is incorrect
        ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "U:\ExcelTraining\Excel App Develop\Chemical Application Sheets\" & PDFName _
            , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=True
        
    End Sub
    I've tried to research the issue but it's a bit over my head. I've been researching this problem for two days and have not been able to translate what I've found into a solution.
    This is the first time I've tried using the ExportAsFixedFormat method. I appreciate the help. Thanks.

    --TFiske
    Attached Files Attached Files
    Last edited by TFiske; 03-21-2018 at 01:10 PM. Reason: Code Correction

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Activesheet Method not working in my Macro
    By ankamshetti.nagaraj in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-20-2017, 06:53 PM
  2. Run-time error '1004': Method 'ExportAsFixedFormat' of object '_Worksheet' failed
    By jlshores in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-09-2017, 02:48 PM
  3. Pages Resizing When Using ActiveSheet.ExportAsFixedFormat
    By PosseJohn in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-16-2016, 04:16 PM
  4. [SOLVED] Save only the Activesheet as a new file
    By Broobi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-20-2014, 08:47 AM
  5. Macro to save an excel file (that is not the activesheet) to PDF / multiple users
    By Mdelgado in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-02-2013, 12:46 AM
  6. .save method for iSeries IFS file
    By dwr0211 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-18-2011, 09:29 AM
  7. Location method with ActiveSheet
    By pierre_colin in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-30-2010, 07:51 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1