VBA print PDF and save n new folder

[jhm0054]

Hi,

I'm using a VBA to turn a sheet into a PDF and save it into the existing workbook location. The code I'm using below works however, I now need the PDFs to be saved in their own folder of the same name as the PDF. Where do I add this into my code

Sub save_excel_as_PDF()
With ActiveSheet.PageSetup
.Orientation = xlPortrait
.PrintArea = "$A$1:$F$33"
.Zoom = False
.FitToPagesTall = False
.FitToPagesWide = 1

End With
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=ActiveWorkbook.Path & "\" & ActiveSheet.Range("B19").Value, _
Quality:=xlQualityStandard, _
IgnorePrintAreas:=False, _
From:=1, _
To:=2, _
OpenAfterPublish:=True _

End Sub

 

[yky]

Hi,

I'm using a VBA to turn a sheet into a PDF and save it into the existing workbook location. The code I'm using below works however, I now need the PDFs to be saved in their own folder of the same name as the PDF. Where do I add this into my code

Sub save_excel_as_PDF()
With ActiveSheet.PageSetup
.Orientation = xlPortrait
.PrintArea = "$A$1:$F$33"
.Zoom = False
.FitToPagesTall = False
.FitToPagesWide = 1

End With
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=ActiveWorkbook.Path & "\" & ActiveSheet.Range("B19").Value, _
Quality:=xlQualityStandard, _
IgnorePrintAreas:=False, _
From:=1, _
To:=2, _
OpenAfterPublish:=True _

End Sub

The ActiveWorkbook.Path tells Excel to save the file in the folder where the active workbook resides. If you the file to be saved in a different folder, change that to the path of the desired destination folder, something like "D:\myfolder\mypdf"

 

[jhm0054]

The ActiveWorkbook.Path tells Excel to save the file in the folder where the active workbook resides. If you the file to be saved in a different folder, change that to the path of the desired destination folder, something like "D:\myfolder\mypdf"

Hi yky,

I need the pdf to be saved to a specific file, however, upon saving as a PDF I would like to automatically create a new folder with the PDF within the Active workbook folder. i.e Active workbook folder>PDF1 folder>PDF1

 

[yky]

Try this:

Sub test_save()

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=ActiveWorkbook.Path & "\" & ActiveSheet.Range("B19").Value, _
Quality:=xlQualityStandard, _
IgnorePrintAreas:=False, _
From:=1, _
To:=2, _
OpenAfterPublish:=True

'test to see if the target directory already exists
'if not, create one
If Len(Dir(ActiveWorkbook.Path & "\" & Left(ActiveSheet.Range("B19").Value, _
Len(ActiveSheet.Range("B19").Value) - 4), vbDirectory)) = 0 Then
   MkDir ActiveWorkbook.Path & "\" & Left(ActiveSheet.Range("B19"), Len(ActiveSheet.Range("B19")) - 4)
End If

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=ActiveWorkbook.Path & "\" & Left(ActiveSheet.Range("B19"), Len(ActiveSheet.Range("B19")) - 4) & "\" & _
ActiveSheet.Range("B19").Value, _
Quality:=xlQualityStandard, _
IgnorePrintAreas:=False, _
From:=1, _
To:=2, _
OpenAfterPublish:=True

End Sub