excel vba save worksheet as pdf.

[BRB1983]

I'm having issues with this code not saving my file. I get an error 1004. it worked before. does any one see any issues with this code?

Option Explicit
Sub SaveAsPDF()

 Dim strDir As String
   strDir = CreateObject("WScript.Shell").specialfolders("Desktop") & "\Daily Reports\"
     
    If Dir(strDir, vbDirectory) = "" Then
        MkDir strDir
    Else
        'MsgBox "Directory exists."
    End If
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
Dim fName As String
With ActiveSheet
    fName = .Range("C7").Value & " " & .Range("F46").Value
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            strDir & fName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
MsgBox "SAVE COMPLETE"
Range("C7") = ""
Range("C9:C45") = ""
End Sub

 

[DanteAmor]

What the error message says and on which line the macro stops.

 

[BRB1983]

message: document not saved. the document may be open, or an error may have been encountered when saving.

Code debug:
.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
strDir & fName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

 

[DanteAmor]

I'm going to ask the obvious, do you have the pdf open?

 

[Logit]

.
Interesting issue ... your macro functions as designed here.

 

[BRB1983]

no pdf open just the excel

 

[DanteAmor]

Perform the following test to save the file in the same path where you have the Excel file with the macro.

Sub SaveAsPDF_2()
  Dim strDir As String, fName As String
  strDir = ThisWorkbook.Path & "\"
  With ActiveSheet
    '.PrintOut
    fName = .Range("C7").Value & " " & .Range("F46").Value
    .ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=strDir & fName, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
  MsgBox "SAVE COMPLETE"
  Range("C7") = ""
  Range("C9:C45") = ""
End Sub

 

[BRB1983]

i get the same error 1004

 

[BRB1983]

i think i know the proble.. cell C7 is date format... can not save a file with "/". ill give it a try with out date.

 

[DanteAmor]

What version of Excel do you have?
You can save a file as pdf from the Excel Save As Pdf.