?
Solved

Run-time error 1004. Document not saved. The document may be open, or an error may have been encountered when saving

Posted on 2012-09-11
4
Medium Priority
?
4,765 Views
Last Modified: 2015-04-03
Under the section of the macro 'Saving file as PDF to the specified folder path FolderName, the macro stops on ActiveWorkbook.ExportAsFixedFormat Filename:=FullFileName, Type:=xlTypePDF and throws the error: “Run-time error 1004. Document not saved. the document may be open, or an error may have been encountered when saving”.  And  so when you hit debug, it highlights the part of code in the area I explain above. (ActiveWorkbook.ExportAsFixedFormat Filename:=FullFileName, Type:=xlTypePDF and throws the error). What this should do is take the information input in the spreadsheet and then export it over to a PDF file, but for whatever reason, this stopped working and now the PDF is blank. The file is still created, but it is blank.  

Any ideas of what the problem might be? Here's a sample of the code.


Sub SendDays()
'
'SendDays Macro
'Formats and sends summary for Day Shift
'
Application.ScreenUpdating = False
'
    Dim Today, FolderName, DateDay, NameofFile, FullFileName As String
    Dim DummyFolderName, NameofDummyFile, FullDummyFileName As String
    Dim OL As Object, EmailItem As Object, Doc As Workbook
   
       
'Saving file as PDF to the specified folder path FolderName
  FolderName = "O:\PTX_MESH\Shift Summaries\2012\May\"
  NameofFile = "MeSH Shift Summary"
  DateDay = Format(Now, "mmmm dd yyyy")
  FullFileName = FolderName + NameofFile + "" + DateDay
  ActiveWorkbook.ExportAsFixedFormat Filename:=FullFileName, Type:=xlTypePDF
 
 
'Save "Dummy" file as PDF to be emaile
DummyFolderName = "O:\PTX_MESH\Shift Summaries\"
NameofDummyFile = "MeSH Day Shift Summary"
FullDummyFileName = DummyFolderName + NameofDummyFile
ActiveWorkbook.ExportAsFixedFormat Filename:=FullDummyFileName, Type:=xlTypePDF

'Print file to MeSH Printer

    'Dim sCurrentPrinter As String
    'Const MyPrinter As String = "\\CPME-PRT01.am.me.net\PTX-MFD-7 on Ne07:"
    'sCurrentPrinter = ActivePrinter
    'ActivePrinter = MyPrinter
    'ActiveSheet.PrintOut
    'ActivePrinter = sCurrentPrinter

           
'Unhide Sheets
    Sheets("Summary").Select
    Range("E3:G3").Select
    Selection.ClearContents
    Range("Q3:R3").Select
    Selection.ClearContents
    Range("AD6:AE6").Select
    Selection.ClearContents
    Range("AD8:AE8").Select
    Selection.ClearContents
    Range("AD9:AE9").Select
    Selection.ClearContents
    Range("AD12:AE12").Select
    Selection.ClearContents
    Range("B14").Select
    Selection.ClearContents
    Range("G23:G25").Select
    Selection.ClearContents
    Range("J23:J25").Select
    Selection.ClearContents
    Range("U23:U24").Select
    Selection.ClearContents
    Range("X23:X24").Select
    Selection.ClearContents
    Range("B27").Select
    Selection.ClearContents
    Range("C34").Select
    Selection.ClearContents
    Range("C37").Select
    Selection.ClearContents
    Range("C40").Select
    Selection.ClearContents
    Range("C43").Select
    Selection.ClearContents
    Range("I49").Select
    Selection.ClearContents
    Range("I50").Select
    Selection.ClearContents
    Range("T49").Select
    Selection.ClearContents
    Range("T50").Select
    Selection.ClearContents
    ActiveWorkbook.Save
   
   
   
'Setting up to email the "Dummy" PDF file just created
    Set OL = CreateObject("Outlook.Application")
    Set EmailItem = OL.CreateItem(olMailItem)
    Set Doc = ActiveWorkbook
    Doc.Save
    Application.ScreenUpdating = False
   
   
'Emailing PDF with the specified subject line and recipient names
    With EmailItem
        .Subject = "MeSH Day Shift Summary:" + Format(Now, "mmmm dd yyyy")
        .To = "wellsaw@me.com; ingraba@me.com; huffilw@me.com; dickedg@me.com;" _
        & "lewishr@me.com; gibsorr@me.com; greenrd@me.com; mesnerk@me.com;" _
        & "browndg@me.com; adamehm@me.com; rubala@me.com; jonesbi@me.com;" _
        & "footetl@me.com; crittkn@me.com; huntedl@me.com; speedjd@me.com;" _
        & "martijd@me.com; irvinjl@me.com; hooksja@me.com; griffmr@me.com;" _
        & "deschma@me.com; falcor@me.com; hardyah@me.com; greenhd@me.com;" _
        & "owensld@me.com; roblesa@me.com; smitjos@me.com; tiffanl@me.com;" _
        & "johnmi@me.com; huffgl@me.com; alewidl@me.com; howeljv@me.com;" _
        & "huffitw@me.com; fallwwe@me.com; mclaikk@me.com; mckindg@me.com;" _
        & "moranpw@me.com; bevintj@me.com; plunkmo@me.com; barreew@me.com;" _
        & "reneajw@me.com; adamspd@me.com; holcodr@me.com; milletp@me.com;" _
        & "mcnabmc@me.com"
         
'Location of the "Dummy" file created to send as the attachment

    .Attachments.Add _
      "O:\PTX_MESH\Shift Summaries\MeSH Day Shift Summary.pdf"
      .Display
   
End With

Application.ScreenUpdating = True

Set Doc = Nothing
Set OL = Nothing
Set EmailItem = Nothing

'Delete the "Dummy" file after its been emailed
    Kill "O:\PTX_MESH\Shift Summaries\MeSH Day Shift Summary.pdf"
   
   
   
End Sub
0
Comment
Question by:CPChem1
  • 2
4 Comments
 
LVL 18

Accepted Solution

by:
Steven Harris earned 2000 total points
ID: 38474686
Generally speaking, the FileName:= format should come after the Type:= format.  The standard layout for this method is:

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF FileName:=“NamedFile.pdf” Quality:=xlQualityStandard DisplayFileAfterPublish:=True

And where the bold formats are optional but may help in diagnosing your issue.

Please note that this will throw an error if a PDF add-in for Excel is not installed.

You can find more optional formats for the ExportAsFixedFormat Method on the Microsoft website.
0
 

Author Comment

by:CPChem1
ID: 38474927
Than you so much!
0
 

Author Closing Comment

by:CPChem1
ID: 38474934
Thanks!
0
 
LVL 13

Expert Comment

by:Chris Raisin
ID: 40704562
The order should not matter
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question