Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Run-time error ‘-2147024773 (8007007b)’: Doucument not saved

0

Run-time error '-2147024773 (8007007b)': Doucument not saved.

Private Sub cmdSavePDS_Click()

Dim ws1 As Worksheet

Dim docName As String

Dim Path As String

   ThisWorkbook.Save

   Set ws1 = Worksheets("Invoice")

   docName = Path & VBA.Format(ws1.Range("I7"), "YYYY-MM-DD") _

          & " " & ws1.Range("A13") & " " & ws1.Range("G13") & ".pdf"

   Application.DisplayAlerts = False

   Path = "C:\Users\carlo\OneDrive\Anita\Anita Invoices\"

   docName = Path & VBA.Format(ws1.Range("I7"), "YYYY-MM-DD") _

          & " " & ws1.Range("A13") & " " & ws1.Range("G13") & ".pdf"

      MsgBox "The invoice has been SAVED in .pdf"

  Where it is written in red, it is highlighted in yellow.

   ws1.ExportAsFixedFormat _

         Type:=xlTypePDF, _

         Filename:=docName, _

         Quality:=xlQualityStandard, _

         IncludeDocProperties:=True, _

         IgnorePrintAreas:=False, _

         OpenAfterPublish:=True

End Sub

Answer
Discuss

Discussion

Hi there, please edit your question and put code tags around the relevant code. It is very hard to read your question. Note: no colors or images will transfer to a question, even if it looks like you can paste them in.
don (rep: 1979) Aug 6, '22 at 11:58 am
Add to Discussion

Answers

0

Zeca

As Don said, we can't see coloured text in questions and you should put code within tags created by the CODE button (as I do below).

I can get your  error to occur by using the modified code below and putting any of the characters not allowed in path/ filenames (< > : " / \ | ? *) in A13 or G13 (or in I7, any except /) - this creates an illegal path and causes the error. (I used "<" in cell G13).

Please check if that is the problem in your file (and if not, read more in the Microsoft guidance here Naming Files, Paths, and Namespaces)

I got your code to work by making the changes below (in bold) so it saves the pdf to the same folder as the Excel file:

Private Sub cmdSavePDS_Click()

Dim ws1 As Worksheet

Dim docName As String

Dim Path As String

   ThisWorkbook.Save

   Set ws1 = Worksheets("Invoice")
    ' commented out (since repeated below and Path is not yet defined so "")
    'docName = Path & VBA.Format(ws1.Range("I7"), "YYYY-MM-DD") _
          & " " & ws1.Range("A13") & " " & ws1.Range("G13") & ".pdf"

   Application.DisplayAlerts = False
    'Picked local path, same as this file
    Path = ThisWorkbook.Path & "\"
   'Path = "C:\Users\carlo\OneDrive\Anita\Anita Invoices\"

   docName = Path & VBA.Format(ws1.Range("I7"), "YYYY-MM-DD") _
          & " " & ws1.Range("A13") & " " & ws1.Range("G13") & ".pdf"

      MsgBox "The invoice has been SAVED in .pdf"

   ws1.ExportAsFixedFormat _
         Type:=xlTypePDF, _
         Filename:=docName, _
         Quality:=xlQualityStandard, _
         IncludeDocProperties:=True, _
         IgnorePrintAreas:=False, _
         OpenAfterPublish:=True
    ' optional addition (for good practice)
    Application.DisplayAlerts = True

End Sub

Suggest you use SelectAll to copy that and paste it into your file and see if it works. If so, the problem may be with your path string (commented out above).

Hope this fixes your problem. If so, please remember to mark this Answer as Selected.

Discuss

Discussion

Did that help,  Zeca?  (You didn't comment or mark the Answer as Selected) 
John_Ru (rep: 4022) Aug 23, '22 at 2:57 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login