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

stuck when save file as pdf in folder has created

0

Hello

I'm stuck  when  try  save  file  as  pdf to  current  month but  it  gives error 

can't  save  document  or  document  is open  in this  line 

Rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Sub ExportSheetToPDF()
'
    Const openPDFwhenDone = True

    'Set constant basePath to the file save path
    Const basePath = "C:\Users\PC WORLD\Desktop\TextFolder\"

    'Declare variable pdfPath which for the complete path & filename
    Dim pdfPath As String
    Dim lr As Long
   Dim WS As Worksheet
   Set WS = Sheets("ST")


     lr = WS.Cells(Rows.Count, 1).End(xlUp).Row
     Set Rng = WS.Range("a1:F" & lr)

    'get the name of the "year" folder
    pdfPath = basePath & Year(Date)

    'if the "year" folder doesn't exist then create it
    If Dir(pdfPath, vbDirectory) = "" Then MkDir pdfPath

    'get the name of the "month" folder
    pdfPath = pdfPath & "\" & Format(Month(Date), "00")

    'if the "month" folder doesn't exist then create it
    If Dir(pdfPath, vbDirectory) = "" Then MkDir pdfPath

    'get the complete pdf filename
   If Right(PDFfile, 1) <> "\" Then PDFfile = PDFfile & "\"
   PDFfile = PDFfile & WS.Range("F6")


    'export active worksheet as PDF to pdfPath
    Rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    'make sure the pdf file was created
    If Dir(pdfPath) = "" Then
        'file not found
        MsgBox "Something went wrong. (PDF wasn't created.)"
    Else
        'Success!  Show success message (unless PDF was set to auto-open)
        If Not openPDFwhenDone Then MsgBox "File Saved As:" & vbLf & pdfPath
    End If

End Sub

the  code  should  save  in  folder 06  within folder 2023    and  the  name  file  is  based on cell F6 and  should save  the  range  A1: F,  not  the  whole  sheet

thanks 

Answer
Discuss

Answers

0
Selected Answer

 Hi Mussa

You didn't specify a full path/ filename for the export so Excel will have saved the file in the current folder (wherever that is!) and you set but didn't use your Constant openPDFwhenDone.

You just need to make the changes in bold below and your procedure should work:

    'export active worksheet as PDF to pdfPath
    Rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath & PDFfile, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=openPDFwhenDone
    'make sure the pdf file was created
    If Dir(pdfPath & PDFfile) = "" Then
        'file not found
        MsgBox "Something went wrong. (PDF wasn't created.)"
    Else
        'Success!  Show success message (unless PDF was set to auto-open) 
        If Not openPDFwhenDone Then MsgBox "File Saved As:" & vbLf & pdfPath & PDFfile 
    End If

Hope this helps.

Discuss

Discussion

Hi John,
unfortunately , the  same  error still continues showing  in  the  same  line .
Mussa (rep: 48) Jun 6, '23 at 4:29 am
Odd- it was fine for me. Did you verify that F6 is something like "Month.pdf"? If not, you'd need to concatenate the extension when creating the variable PDFfile
John_Ru (rep: 6142) Jun 6, '23 at 6:40 am
sorry  John !
this  is  my  bad !

the  reason was ":"  existed in F6 after two  letters
and  also  if  don't  write .PDF  will  show message MsgBox "Something went wrong. (PDF wasn't created.)"   but  I  can  fix  it  by  add .pdf  in  line F5  like  this 
[CODE   PDFfile = PDFfile & WS.Range("F6") & ".PDF"
[/CODE]
and  this  works 
thank you  so  much .
Mussa (rep: 48) Jun 6, '23 at 6:58 am
Sure but you didn't attach a file so I couldn't see the ":" in F6. Thanks for selecting my Answer, Mussa. 
John_Ru (rep: 6142) Jun 6, '23 at 8:11 am
Add to Discussion


Answer the Question

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