saving as PDF

[rickblunt]

Hello, Below is a snippet of code that I am using in a routine to save a worksheet to a back-up drive, and it all works fine. On my last audit the auditor "suggested" that I should be saving these files in a format that cannot be altered, such as PDF. Does anyone have a suggestion on how I can tweak this code to save it as a pdf instead of xls? There doesn't seem to be a value that does this in "FileFormat"
Thanks for any input

Sheets("Fall Protection Report").Copy                
        Set wb = ActiveWorkbook
       With wb
            Application.DisplayAlerts = False
            .SaveAs _
            "\\MESSDV002.na.infineon.com\Facilities\DeptData\EH&S\Compliance and EHS\Facilities Inspections\Fall Protection\Fall Protection Inspection Report  " _
            & Format(Now, "yy_mmdd") & ".xlsx", FileFormat:=51
            .Close True
        Application.DisplayAlerts = True
        
     End With

 

[Domenic]

To export your worksheet to PDF, you can use the ExportAsFixedFormat method of the Worksheet object . . .

    Sheets("Fall Protection Report").ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:="\\MESSDV002.na.infineon.com\Facilities\DeptData\EH&S\Compliance and EHS\Facilities Inspections\Fall Protection\Fall Protection Inspection Report  " _
            & Format(Now, "yy_mmdd") & ".xlsx"

This method contains other parameters that can be set. Have a look at the following link for more information . . .

Worksheet.ExportAsFixedFormat method (Excel)

Office VBA reference topic

docs.microsoft.com

Hope this helps!

 

[rickblunt]

Thanks Domenic for the help, the file does get saved to the folder as a pdf file, but it cannot be opened - it appears to be corrupted. Not sure if maybe there is some items in my worksheet (like userforms or macros) that is confusing the pdf reader? But when I save the file directly from excel as a PDF it works fine. It must be somewhere later in that particular sub that is creating the issue, as the sub does not completely finish. But you did answer my question so I really appreciate the solution, thanks for the help.

 

[Joe4]

I think the issue is that you are exporting to PDF, but the file extension is still "xlsx". That could be causing the corruption message (when file format and extension do not match up).

Try changing it to:

  Sheets("Fall Protection Report").ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:="\\MESSDV002.na.infineon.com\Facilities\DeptData\EH&S\Compliance and EHS\Facilities Inspections\Fall Protection\Fall Protection Inspection Report  " _
            & Format(Now, "yy_mmdd") & ".pdf"

 

[rickblunt]

I think the issue is that you are exporting to PDF, but the file extension is still "xlsx". That could be causing the corruption message (when file format and extension do not match up).

Try changing it to:

  Sheets("Fall Protection Report").ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:="\\MESSDV002.na.infineon.com\Facilities\DeptData\EH&S\Compliance and EHS\Facilities Inspections\Fall Protection\Fall Protection Inspection Report  " _
            & Format(Now, "yy_mmdd") & ".pdf"

Yes, Joe4 you are correct there, I got an error when I first ran it, but when I changed it to pdf it worked better. Or at least it dropped a pdf file in the folder, but that is about where the code stopped running... I'm going through my entire code now to figure out what is hanging up. Thanks for the input

 

[Joe4]

Yes, Joe4 you are correct there, I got an error when I first ran it, but when I changed it to pdf it worked better. Or at least it dropped a pdf file in the folder, but that is about where the code stopped running... I'm going through my entire code now to figure out what is hanging up. Thanks for the input

You are welcome.

Yep, sounds like you have an unrelated issue going on (at least, unrelated to the code you posted).
You may to try stepping through your code to find out where it is hanging.

 

[rickblunt]

Thanks for the help - I don't see an issue with why its hanging up where it is, should I create a new thread or just continue it here?

 

[Joe4]

Thanks for the help - I don't see an issue with why its hanging up where it is, should I create a new thread or just continue it here?

Since it is a different question/matter, you should start a new thread.

 

[rickblunt]

That's what I thought I want to try my hardest to figure it out before I ask for help though, thanks again for the input...