CODEWITHSUNDEEP
vbaexcel
Santhosh
Santhosh

Reputation: 23

Print Macro - issues

I am using the below mentioned code to print a sheet. My task is complete, however I get the error message stating "Run-time error '-2147024773 (8007007b)': Document not saved."

Also, in the below code, can I add a text to the file name (other than cell A1 text?). I would like the file name to be name (which is on cell A1) and add a text "- Workpaper" in the end.

Can some one help?

Sub PrintFile()
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="C:\Foldername\" & Range("A1").Text, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
  End With
End Sub

Upvotes: 1

Views: 97

Answers (3)

Rafał B.
Rafał B.

Reputation: 404

First of all type e.g. "test" into A1 cell - probably there is something wrong with the filename. You can also use Excels's data validation or some VBA code to sanitizate the filename. You can also add some check if directory exists to make sure it isn't a problem.

Sub PrintFile()

' check if folder exists
    If Dir("C:\Foldername\", vbDirectory) = "" Then
      MkDir "C:\Foldername\"
    End If

' check if name in A1 is not blank
    If IsEmpty(Range("A1")) Then
      MsgBox "Fill A1 Cell with name of the pdf file first"
    Else
      With ActiveSheet
        .ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="C:\Foldername\" & Range("A1").Value & "- Workpaper", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
        End With
    End If

End Sub

So there is a problem with your path or/and filename. Maybe it is a Mac and C:\ is not proper address?

Upvotes: 1

Frank Ball
Frank Ball

Reputation: 1126

Try changing "Range("A1").Text" to "Range("A1").Value" Text vs Value

Also, you should be checking for a valid filename prior to using the value.

Function ValidateFileName(ByVal name As String) As Boolean
    ' Check for nothing in filename.
    If name Is Nothing Then
        ValidateFileName = False
    End If

    ' Determines if there are bad characters.
    For Each badChar As Char In System.IO.Path.GetInvalidPathChars
        If InStr(name, badChar) > 0 Then
            ValidateFileName = False
        End If
    Next

    ' If Name passes all above tests Return True.
    ValidateFileName = True
End Function

Upvotes: 1

Imran Malek
Imran Malek

Reputation: 1719

The reason for this error might be insufficient privileges or any invalid characters in the name of the file.

Could you try to save it in a different drive than C and see if that works?

Replace this line of your code with below line to add -Workpaper at the end of your file name.

Assuming that you're trying to save this in D drive in a temp folder.

Filename:="D:\temp\" & Range("A1").Text & "-Workpaper", _

Upvotes: 0

Related Questions