"Run-time error '1004'. Document not saved." when exporting to PDF with VBA
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: "Run-time error '1004'. Document not saved." when exporting to PDF with VBA

  1. #1
    New Member
    Join Date
    Mar 2010
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default "Run-time error '1004'. Document not saved." when exporting to PDF with VBA

     
    I have a macro that runs from a key combo to save the active worksheet to PDF.

    It seems that this macro runs correctly and performs the requested operation (export to PDF), however, it throws up a run-time error, which prevents the macro from completing.

    Code:
    Sub ...
    ...
    ...
    ..
    With ActiveSheet
    .ExportAsFixedFormat _
            Type:=xlTypePDF, _
            FileName:="C:\...correct path triple-checked!...\" & Cells(11, 10).Value & "_" & Cells(15, 8).Value & ".pdf", _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            OpenAfterPublish:=False
    '
    End With
    '
    Sheets("Sheet1").Delete
    '
    End Sub
    VBA repeatedly tells me (highlights during Debug) the offending code starts at .ExportAsFixedFormat and ends after OpenAfterPublish:=False, yet I cannot find anything wrong with the coding, despite having corrected the coding and reverted to my original format numerous times. The only thing I can assume is that the underscore in the filename is causing the issue, yet all resources I have checked specify that underscore is a valid character...

    What is even more confusing is that the macro does produce my final PDF (with the file name in the format CellRef_CellRef); it just fails to delete sheet 1 after doing so... If I then, after deleting Sheet1 manually, run the macro again, the macro seems to then run perfectly (overwriting the previously exported file) without throwing up any errors whatsoever.

    Any help would be greatly appreciated, as I'm certain I've grown a few more grey hairs since this morning!

  2. #2
    New Member
    Join Date
    Mar 2010
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "Run-time error '1004'. Document not saved." when exporting to PDF with VBA

    I should mention that I have attempted:

    Application.ScreenUpdating = False

    and another Application.(something about Alerts, cannot remember what)

    to try and bypass the redundant run-time error alert, however this does not seem to affect run-time error alerts...

  3. #3
    Board Regular BiocideJ's Avatar
    Join Date
    Jan 2012
    Location
    Florida, USA
    Posts
    1,664
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "Run-time error '1004'. Document not saved." when exporting to PDF with VBA

    The code looks good and perhaps someone else can figure out what is going on to remove the error altogether, however, if all you want to do is supress errors just put this line of code in the first line after the Sub

    On Error Resume Next

    Downside of this is that if the code experiences a legitimate error you wouldn't know until you went to find the PDF and it wasn't there.

  4. #4
    Board Regular
    Join Date
    Nov 2009
    Posts
    613
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "Run-time error '1004'. Document not saved." when exporting to PDF with VBA

    When I removed
    Code:
    Sheets("Sheet1").Delete
    I didn't get an error (without suppressing the error)

    -- g
    Home: Windows XP sp3
    Home: Excel 2010, Professional
    Work: Excel 2007, Professional

  5. #5
    New Member
    Join Date
    Mar 2010
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "Run-time error '1004'. Document not saved." when exporting to PDF with VBA

    however, if all you want to do is supress errors just put this line of code in the first line after the Sub

    On Error Resume Next

    Downside of this is that if the code experiences a legitimate error you wouldn't know until you went to find the PDF and it wasn't there.
    Thanks, this successfully suppressed the error, which is the main thing at the moment (-as I say, the macro performs flawlessly, so I'm not quite sure what's going on...

    When I removed

    Code:
    Sheets("Sheet1").Delete
    I didn't get an error (without suppressing the error)

    -- g
    That's interesting... Sheet1 is actually created at the beginning of the macro using "Sheets.Add Type:=". The macro then selects other sheets and copies values to Sheet1 before exporting it. I can't see why it would have an issue recognising Sheet1 in order to delete it, if Sheets("Sheet1").Select did not return an error earlier... I'm completely mystified... Perhaps I need to run the sheet deletion in a second macro (though that would defeat the object of a macro, would it not!)?

  6. #6
    Board Regular BiocideJ's Avatar
    Join Date
    Jan 2012
    Location
    Florida, USA
    Posts
    1,664
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "Run-time error '1004'. Document not saved." when exporting to PDF with VBA

    I think I actually figured out what is causing this.
    If there are no cells with data in the ActiveSheet then there is nothing to 'print' into the PDF. I suspect the file that is being created is just a blank PDF page.

    You should be able to avoid the error altogether with the following.

    Code:
    If ActiveSheet.UsedRange.Cells.Count > 1 Then
    With ActiveSheet
        .ExportasFixedFormat...
    ...
    End If
     
    Sheets("Sheet1").Delete
    This code will NOT create a blank PDF. If you absolutely want to create the file (even if it is blank) than the way you have it now is correct.
    Last edited by BiocideJ; Mar 7th, 2012 at 02:28 PM.

  7. #7
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "Run-time error '1004'. Document not saved." when exporting to PDF with VBA

    Maybe the unqualified Cells property is the cause of the error. Notice the dots before Cells below:

    Code:
    FileName:="C:\...correct path triple-checked!...\" & .Cells(11, 10).Value & "_" & .Cells(15, 8).Value & ".pdf", _
    Microsoft MVP - Excel

  8. #8
    Board Regular BiocideJ's Avatar
    Join Date
    Jan 2012
    Location
    Florida, USA
    Posts
    1,664
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "Run-time error '1004'. Document not saved." when exporting to PDF with VBA

    Quote Originally Posted by Andrew Poulsom View Post
    Maybe the unqualified Cells property is the cause of the error. Notice the dots before Cells below:

    Code:
    FileName:="C:\...correct path triple-checked!...\" & .Cells(11, 10).Value & "_" & .Cells(15, 8).Value & ".pdf", _
    That is an interesting point, although I thought if you didn't qualify Cells that ActiveSheet was implied. Worth a try though if you are still having problems.

  9. #9
    New Member
    Join Date
    Mar 2010
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "Run-time error '1004'. Document not saved." when exporting to PDF with VBA

    If there are no cells with data in the ActiveSheet then there is nothing to 'print' into the PDF. I suspect the file that is being created is just a blank PDF page.
    Sheet1 is an invoice created from a template which references various cells in other sheets in the workbook, so it actually contains numerous items of data.


    Quote Originally Posted by Andrew Poulsom View Post
    Maybe the unqualified Cells property is the cause of the error. Notice the dots before Cells below:

    Code:
    FileName:="C:\...correct path triple-checked!...\" & .Cells(11, 10).Value & "_" & .Cells(15, 8).Value & ".pdf", _
    Hmmm ill have to try this solution later. I'd presumed the With ActiveSheet command would have implied that the cells were on the sheet...

    Thanks to all for the continued help! Its much appreciated!

  10. #10
    New Member
    Join Date
    Mar 2010
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "Run-time error '1004'. Document not saved." when exporting to PDF with VBA

      
    Quote Originally Posted by Andrew Poulsom View Post
    Maybe the unqualified Cells property is the cause of the error. Notice the dots before Cells below:

    Code:
    FileName:="C:\...correct path triple-checked!...\" & .Cells(11, 10).Value & "_" & .Cells(15, 8).Value & ".pdf", _
    @Andrew - Thanks a thousand times. I removed the command to suppress errors and qualified the cells as instructed and it now appears to be working flawlessly. Again, many, many thanks

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com