VBA creating pdf files and saving them in the same folder where the workbook is saved

[hyunee]

Hello everyone

I am trying to create individual pdf file for three active sheets (Report1, Report2 and Report3) and want to save them in the same folder as where the current excel workbook is saved. i have codes below and the pdf files are created okay but they are being saved in different folder. i cant figure it out why and how to fix it. can you please help?
I have saved the workbook and closed and re-opened it and the pdf files are still saved in the wrong place....
Thank you in advance!

*here are some background which might help you to help me...
i normally create the following month workbook by changing file name of previous month excel workbook and created a new folder for the month i am working and saved them. i want the pdf files to be saved in the THAT folder i created not previous folder!


Codes that i have at the moment are...

Sheets("Report1").Select
Dpath = "Report1" & Format(Range("h7"), "mmmm yyyy")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
Dpath, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True

Sheets("report2").Select
Dpath = "Report2" & Format(Range("h7"), "mmmm yyyy")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
Dpath, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True

Sheets("report3").Select
Dpath = "Report3 " & Format(Range("h7"), "mmmm yyyy")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
Dpath, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True

 

[hyunee]

Hi Paul

Thanks for your help previously. Now i have a new problem as i changed your codes by adding "Yearly Product Report for NSW" in StrFlNm and it's not working. i want the PDF files to be saved as " Yearly Product Report for NSW Cola" ," Yearly Product Report for NSW Pepsi" and "Yearly Product Report for NSW Fanta".

Could you please help?
Thank you!


Sub PDF_Save()
Dim i As Long, StrPath As String, StrFlNm As String, ArrNames
ArrNames = Array("Cola", "Pepsi", "Fanta")
StrPath = ActiveWorkbook.Path & ""
For i = 0 To UBound(ArrNames)
With Sheets(ArrNames(i))
StrFlNm = "Yearly Product Report for NSW" & ArrNames(i) & Format(.Range("H7"), " mmmm yyyy")
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=StrPath & StrFlNm, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
End With
Next
End Sub

 

[Macropod]

You need to insert \ between "" on this line:
StrPath = ActiveWorkbook.Path & ""
other than that, I'd be inclined to replace:
"Yearly Product Report for NSW"
with:
"Yearly Product Report for NSW "
to provide a space before the product names.

 

[hyunee]

Happy New Year Paul

I tried your suggestion both and it's still not working.

I did a bit of google research and it might be due to too long Path? are there a limit on characters for "Path" or "Filename"?

Since this is for work and the folder where those reports are to be saved has a ridiculous long folder address.

Could you please help?

As always, i appreciate your help.

Thanks

 

[Macropod]

The code works just fine for me with the suggested modifications. And yes, filenames & paths can become too long - the limit is set by your file system. If it works without adding "Yearly Product Report for NSW", you might try shorter versions of that (e.g. "2017 NSW Product Report ").