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
Bookmarks