Error exporting excel workbooks to PDF

[Billy Hill]

I've tried about half a dozen different methods to export sheets to a single pdf. The closest I've gotten has failed after trying to export with Run Time Error 1004, stating "document not saved. The document may be open, or an error may have been encountered when saving".

Any idea what I can do here? There are two sheets, named Operation List and Tool List. I want them into a single PDF but will settle for two different PDFs. Thanks!

Excel 2010.

Sub SaveCertainWorksheetsAsPDF()
    'Save the active sheet as a .PDF with the filename in cell B1
    'File will be saved in the same directory as this file
    
    Dim sFileName As String, sht As Worksheet
    
    For Each sht In Worksheets
            With sht
                sFileName = .Range("B1").Value 
                .ExportAsFixedFormat Type:=xlTypePDF, _
                    Filename:="C:\" & sFileName & ".pdf", _
                    Quality:=xlQualityStandard, IncludeDocProperties:=False, _
                    IgnorePrintAreas:=False, OpenAfterPublish:=False
            End With
    Next
End Sub

 

[Domenic]

Re: Error exporting excel workbooks to PDF, please help

The following macro will create a single PDF containing the specified worksheets. Change the path and filename accordingly.

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] SaveAsPDF()

    [COLOR=darkblue]Dim[/COLOR] sFileName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    
    sFileName = "MyFilename" [COLOR=green]'change the file name accordingly[/COLOR]
    
    Sheets(Array("Operation List", "Tool List")).Copy
    
    ActiveWorkbook.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:="C:\Users\Domenic\Desktop\" & sFileName & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, OpenAfterPublish:=[COLOR=darkblue]False[/COLOR]
        
    ActiveWorkbook.Close SaveChanges:=False
    
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
    
    MsgBox "Completed...", vbExclamation

[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[Billy Hill]

Re: Error exporting excel workbooks to PDF, please help

Thanks Domenic, I tried the code and it showed the publishing dialog but before it finished it threw up this error:

Run-time error '1004':
 Method 'ExportAsFixedFormat' of object '_Workbook' failed.

Am I missing a reference to a specific .dll or something?

The following macro will create a single PDF containing the specified worksheets. Change the path and filename accordingly.

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] SaveAsPDF()





    [COLOR=darkblue]Dim[/COLOR] sFileName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    
    sFileName = "MyFilename" [COLOR=green]'change the file name accordingly[/COLOR]
    
    Sheets(Array("Operation List", "Tool List")).Copy
    
    ActiveWorkbook.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:="C:\Users\Domenic\Desktop" & sFileName & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, OpenAfterPublish:=[COLOR=darkblue]False[/COLOR]
        
    ActiveWorkbook.Close SaveChanges:=False
    
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
    
    MsgBox "Completed...", vbExclamation

[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[Domenic]

Re: Error exporting excel workbooks to PDF, please help

Did you change the path to a valid one? In my example, I had left out the back slash before the file name. It should have been...

Filename:="C:\Users\Domenic\Desktop\" & sFileName & ".pdf", _

 

[Billy Hill]

Re: Error exporting excel workbooks to PDF, please help

Did you change the path to a valid one? In my example, I had left out the back slash before the file name. It should have been...

Filename:="C:\Users\Domenic\Desktop\" & sFileName & ".pdf", _

Yes, I changed the path to a valid one:

Filename:="C:\" & sFileName & ".pdf",

Any other ideas? Only thing I can think of is a reference for Adobe but I've tried all the ones I could find locally.

 

[Domenic]

Re: Error exporting excel workbooks to PDF, please help

That's probably because you don't have permissions to save in the root directory. Try saving it in your Documents folder.

 

[Billy Hill]

Re: Error exporting excel workbooks to PDF, please help

Well isn't that curious?!?! LOL.

That was it, thanks much. That explains another issue I was having some time back.

 

[excellearnerz]

Hello Sirs,

Maybe you can help me. I am getting the same exportaspdf failed error.

Here is the code I am using. I have a command button in my data entry form, which when clicked will save a pdf file for sheet 4.

Private Sub CommandButton1_Click()

    Application.ScreenUpdating = False
    ChDir "C:\Users\Engr. Ferrer\Desktop\Destiny\"
    Sheet4.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Format(Now(), "DD-MMM-YYYY hh:mm:ss AMPM") _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Application.ScreenUpdating = True
    
End Sub