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

 

[Macropod]

You would get better results if you actually supplied the path. Try:

Sub PDF_Save()
Dim i As Long, StrPath As String, StrFlNm As String
StrPath = ActiveWorkbook.Path & "\"
For i = 1 To 3
  With Sheets("Report" & i)
    StrFlNm = "Report" & 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

 

[hyunee]

Thank you!!

The problem is the tab name(worksheet name) are not actually Report 1,2 and 3. i just changed it to 1,2,3 for you to understand easy. The worksheet names are product names like Cola, Pepsi, and Fanta so your coding below wont work.... sorry can you please please give me different codes?

 

[Macropod]

It would have been helpful had you said they weren't the real worksheet names instead of having me waste time providing a streamlined solution that doesn't apply... As it is, the only part of my coding that won't work is the loop You will instead need to replace the references to:
"Report" & I
with whatever the real worksheet names are and have a sequence of such With - End With constructs - or you could adapt the code I posted, thus:

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 = Sheets(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]

Now cross-posted at: VBA Saving PDF files in the same folder where the excel workbook is saved without even the courtesy of acknowledging the advice already given.
Please read our policy on Cross-Posting in rule #13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[hyunee]

Thank you! and sorry for not telling you the sheet name were not real name before.

I have changed the codes as below and it still not working. It comes up as Complie error. Sorry i am new to VBA and only started learning recently. If you could help one more time please...

also i am not sure why the pdf is not being saved automatically in the same folder where the excel book is saved when i dont have any specific path in the coding..



Sub PDF_Save()
Dim i As Long, StrPath As String, StrFlNm As String
StrPath = ActiveWorkbook.Path & ""
For i = 1 To 3
With Sheets("Cola", "Pepsi", "Fanta")
StrFlNm = "Cola" & 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]

Is there a reason you didn't use the code I posted???

 

[hyunee]

I have, but it didnt work either. it keeps saying " Object doesn't support this property or method.

Is there anyway i could send you the excel workings with the codes i have? i literally copied and pasted your codes and not sure why it's not working..

 

[Macropod]

Typo. Try:

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 = 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

 

[hyunee]

It's now working! Thank you so much! i really appreciate your help!!
Cant wait to get better at VBA!