VBA (also non vba) Help Required - Saving To PDF

[The Great SrH]

Hi all,

I have a "macro-enabled" excel document i've created. I'm looking to add the following code to save part as the document as PDF.

Private Sub CommandButton4_Click()
Application.ScreenUpdating = False
Worksheets("request").Unprotect ("bacon")
    Sheets("request").Range("A1:I45").ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=Range("S8").Value _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True
            
Worksheets("request").Protect ("bacon")
Application.ScreenUpdating = True

End Sub

I want the document to save in the same location as the Excel file. An alternative code I'd like if anyone has, is for the user to have the "Save As" box appear so they can name the file themselves.

However, I keep getting a runtime error stating:
"Run-Time Error '1004' Document not saved. The document may be open or an error may have been encountered."

When i try to export the document as PDF (non VBA), I also get a similar error.

Any suggestions on how to fix this?
Thanks

 

[Yongle]

I want the document to save in the same location as the Excel file

Try

Private Sub CommandButton4_Click()
 Application.ScreenUpdating = False
   Dim fName As String
   With Worksheets("request")
      .Unprotect ("bacon")
       fName = ActiveWorkbook.Path & "\" & Range("S8").Value
      .Range("A1:I45").ExportAsFixedFormat Type:=xlTypePDF, Filename:=fName
      .Protect ("bacon")
   End With
 Application.ScreenUpdating = True
End Sub

 

[Yongle]

An alternative code I'd like if anyone has, is for the user to have the "Save As" box appear so they can name the file themselves

Do you want the user to be able to
"name the pdf" ?
OR
select the "save to" folder ?
OR
both ?

 

[The Great SrH]

Do you want the user to be able to
"name the pdf" ?
OR
select the "save to" folder ?
OR
both ?

Both possible!

To be honest, this code will be redundant if I can't fix the PDF issue?

 

[Yongle]

did you try post#2 solution?

 

[The Great SrH]

did you try post#2 solution?

Yep. It just wont save anything to PDF...even if I tried the "File" menu way

 

[Yongle]

The code works for me - the value in S8 is likely to be the problem
You did not qualify the range - so it is taking the value from the active sheet - is that the correct sheet?

Add this line

MsgBox fName

below

fName = ActiveWorkbook.Path & "\" & Range("S8").Value

Run the code again and look at the message box
- does it only contain the path without a name for the file?
- or it has some illegal characters (for file saving)?

 

[The Great SrH]

The code works for me - the value in S8 is likely to be the problem
You did not qualify the range - so it is taking the value from the active sheet - is that the correct sheet?

Add this line

MsgBox fName

below

fName = ActiveWorkbook.Path & "\" & Range("S8").Value

Run the code again and look at the message box
- does it only contain the path without a name for the file?
- or it has some illegal characters (for file saving)?

No it doesn't seem to be anything to do with the file name. The form doesn't save in PDF when I go to File > Export > Create PDF. I get the same error.

The file name is "form v3"

 

[Fluff]

Do you already have a file of that name in the folder?
If so can you delete it?

 

[Yongle]

A simple test which eliminates anything that may be in your current file .....

Please close EXCEL completely and then reopen it
Create a new workbook and insert this code in a standard module and simply run it

Sub TestExportToPDF()
    Dim fpath As String: fpath = Application.DefaultFilePath
    Dim fname As String: fname = "YongleTest"
    ActiveSheet.Range("A1:I45").ExportAsFixedFormat Type:=xlTypePDF, Filename:=fpath & "\" & fname
    MsgBox "saved to: " & fpath & vbCr & "name: " & fname
End Sub

Do you still get the same error or not?