VBA SAVE AS PDF TO CURRENT DIRECTORY FOLDER WITH CURRENT FOLDER NAME.PDF

[hamiveic]

Hi Everyone,

This is my first post.

I'm trying to export the worksheet as PDF.

e.g
The current excel file is saved in path is C:\document\file\ABC
the current folder name is ABC.
B6 = 3

Below code is able to get be to save the PDF in the current folder C:\document\file\ABC as 3.pdf. But it will not save the document as ABC3.pdf.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.path & "\" & Range("B6").Value & ".pdf", Quality:=xlQualityStandard, _
  IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True

This will able to get me to save the PDF as ABC3.pdf. But it will save the PDF to the prior folder C:\document\file. Not the sub-folder ABC.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.path & Range("B6").Value & ".pdf", Quality:=xlQualityStandard, _
  IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True

What I want to do is to save the PDF in the folder C:\document\file\ABC as ABC3.pdf.

I do not want to set the location permanently to C:\document\file\ABC. Since this is a temple and will be copied and pasted around folders.

Any suggestions? Thank you very much.

 

[John_w]

Try this:

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & Mid(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\")) & Range("B6").Value & ".pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True

 

[hamiveic]

Try this:

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & Mid(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\")) & Range("B6").Value & ".pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True

It worked! Thank you so much!
Would you take a min to help explain a bit about this part?

Mid(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\"))

 

[John_w]

InStrRev looks backwards to find the position of the last "\" in the Path string. Mid then extracts the Path from that position to the end.

 

[hamiveic]

Thank you very much!